SEO

I'm a newbie so please be gentle.

In attempting to run a restore I get the following error message:
"Exclusive access could not be obtained because the database is in
use." However, it doesn't appear that there are any connections to the
database. At the suggestion of another thread, I ran sp_who2 and there
are no connections to the database in question. I've been running the
same restore for months and all has been well, until yesterday....

I wrote an .asp page (below) to test connectivity to this database.
The vb code ran successfully and soon thereafter, my nightly restore
failed. Task manager does no show that the asp page is active.

Lastly, I did add a user ("jbtest") as well as change the option to use
either Windows authentication or SQL Server authentication in
Enterprise Manager.

Any ideas would be greatly appreciated.

Thanks.

<%@ Language=VBScript %>

<%
companyName = "agemni"

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = "DRIVER=SQL
Server;SERVER=SHEELA-NA-GIG;UID=jbtest;PWD=test1;APP=Microsoft
Development Environment;WSID=SHEELA-NA-GIG;DATABASE=" & companyName &
";Trusted_Connection=No"

cnn.ConnectionTimeout = 300
cnn.CommandTimeout = 300
cnn.Open

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Preferences"
RS.Open strSQL, cnn

Response.Write "connection established with the <b>" & RS("Company
Name") & "</b> database. They are a <b>" & RS("street") & "</b>
customer. "

RS.Close
Set RS = Nothing
%>

<jbmccluskey@satcountry.com> wrote in message
news:1112117455.317127.10740@g14g2000cwa.googlegro ups.com...
> I'm a newbie so please be gentle.
>
> In attempting to run a restore I get the following error message:
> "Exclusive access could not be obtained because the database is in
> use." However, it doesn't appear that there are any connections to the
> database. At the suggestion of another thread, I ran sp_who2 and there
> are no connections to the database in question. I've been running the
> same restore for months and all has been well, until yesterday....
>
> I wrote an .asp page (below) to test connectivity to this database.
> The vb code ran successfully and soon thereafter, my nightly restore
> failed. Task manager does no show that the asp page is active.
>
> Lastly, I did add a user ("jbtest") as well as change the option to use
> either Windows authentication or SQL Server authentication in
> Enterprise Manager.
>
> Any ideas would be greatly appreciated.
>
> Thanks.
>

<snip>

Probably the most reliable solution is to use ALTER DATABASE to get rid of
any open connections, wherever they're from:

alter database MyDB set restricted_user with rollback immediate

See Books Online for the other state options (single_user, offline etc.) -
one of them might suit your needs better.

You don't mention how you're running the restore, but if it's via a
scheduled job, you should also check that the selected database for the
restore step is set to master, not to the database you're restoring,
otherwise the job can block itself.

Simon

Thanks for the reply Simon. The problem was that I was trying to run
the RESTORE in the database itself as opposed to running it under
MASTER.

JB