Restore DB Failed

SQL server 2005
Try to restore database,
Can not restore it because Do not have exclusive access.
Have .DLL constantly connect to the database.
How could I restore database without stop SQL service
Thanks in advance
Jeff
What database you are trying to restore. If it is a user database probably
the database name which you are trying to restore is already in the same
server and used by some users.
If you want to overwrite the database then use the below command to set the
database to single user mode and then try restoring.
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Thanks
Hari
"jeff" wrote:

> SQL server 2005
> Try to restore database,
> Can not restore it because Do not have exclusive access.
> Have .DLL constantly connect to the database.
> How could I restore database without stop SQL service
> Thanks in advance
> Jeff
>
|||On Feb 21, 12:40 pm, Hari Prasad
<HariPra...@.discussions.microsoft.com> wrote:
> What database you are trying to restore. If it is a user database probably
> the database name which you are trying to restore is already in the same
> server and used by some users.
> If you want to overwrite the database then use the below command to set the
> database to single user mode and then try restoring.
> ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> Thanks
> Hari
>
>
It is a user database, and the name is already being used. Trying to
restore by overwrite the existing one.
But if set to single_user mode, will that affect later connection? How
to find out if it is already in single mode?
Thanks
|||be sure your own connection is not using the db. do 'use master' to switch
db context for your own connection before initiating the restore.
-oj
"jeff" <jeffzhang44@.gmail.com> wrote in message
news:1172080049.968263.170350@.q2g2000cwa.googlegro ups.com...
> On Feb 21, 12:40 pm, Hari Prasad
> <HariPra...@.discussions.microsoft.com> wrote:
> It is a user database, and the name is already being used. Trying to
> restore by overwrite the existing one.
> But if set to single_user mode, will that affect later connection? How
> to find out if it is already in single mode?
> Thanks
>
|||On Feb 21, 3:23 pm, "oj" <nospam_oj...@.home.com> wrote:
> be sure your own connection is not using the db. do 'use master' to switch
> db context for your own connection before initiating the restore.
> --
> -oj
>
What does this mean? Could you be more specific? Thanks
|||"jeff" <jeffzhang44@.gmail.com> wrote in message
news:1172091728.199686.230500@.s48g2000cws.googlegr oups.com...
> On Feb 21, 3:23 pm, "oj" <nospam_oj...@.home.com> wrote:
> What does this mean? Could you be more specific? Thanks
>
Common mistake (I'm sure all the regulars have done it at least once) is to
be currently in the database you want to restore.
So you are effectively the user blocking your restore.
do a USE MASTER before the restore statement to eliminate this. (and make
sure enterprise manager isn't using the database in question.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Restore DB Failed

SQL server 2005
Try to restore database,
Can not restore it because Do not have exclusive access.
Have .DLL constantly connect to the database.
How could I restore database without stop SQL service
Thanks in advance
JeffWhat database you are trying to restore. If it is a user database probably
the database name which you are trying to restore is already in the same
server and used by some users.
If you want to overwrite the database then use the below command to set the
database to single user mode and then try restoring.
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Thanks
Hari
"jeff" wrote:

> SQL server 2005
> Try to restore database,
> Can not restore it because Do not have exclusive access.
> Have .DLL constantly connect to the database.
> How could I restore database without stop SQL service
> Thanks in advance
> Jeff
>|||On Feb 21, 12:40 pm, Hari Prasad
<HariPra...@.discussions.microsoft.com> wrote:
> What database you are trying to restore. If it is a user database probably
> the database name which you are trying to restore is already in the same
> server and used by some users.
> If you want to overwrite the database then use the below command to set th
e
> database to single user mode and then try restoring.
> ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> Thanks
> Hari
>
>
It is a user database, and the name is already being used. Trying to
restore by overwrite the existing one.
But if set to single_user mode, will that affect later connection? How
to find out if it is already in single mode?
Thanks|||Hello,
select databaseproperty('Master','IsSingleUser'
)
If it return 1 then database is in single user mode. Change the DBname to
yours.
Thanks
Hari
"jeff" wrote:

> On Feb 21, 12:40 pm, Hari Prasad
> <HariPra...@.discussions.microsoft.com> wrote:
> It is a user database, and the name is already being used. Trying to
> restore by overwrite the existing one.
> But if set to single_user mode, will that affect later connection? How
> to find out if it is already in single mode?
> Thanks
>|||be sure your own connection is not using the db. do 'use master' to switch
db context for your own connection before initiating the restore.
-oj
"jeff" <jeffzhang44@.gmail.com> wrote in message
news:1172080049.968263.170350@.q2g2000cwa.googlegroups.com...
> On Feb 21, 12:40 pm, Hari Prasad
> <HariPra...@.discussions.microsoft.com> wrote:
> It is a user database, and the name is already being used. Trying to
> restore by overwrite the existing one.
> But if set to single_user mode, will that affect later connection? How
> to find out if it is already in single mode?
> Thanks
>|||On Feb 21, 3:23 pm, "oj" <nospam_oj...@.home.com> wrote:
> be sure your own connection is not using the db. do 'use master' to switch
> db context for your own connection before initiating the restore.
> --
> -oj
>
What does this mean? Could you be more specific? Thanks|||"jeff" <jeffzhang44@.gmail.com> wrote in message
news:1172091728.199686.230500@.s48g2000cws.googlegroups.com...
> On Feb 21, 3:23 pm, "oj" <nospam_oj...@.home.com> wrote:
> What does this mean? Could you be more specific? Thanks
>
Common mistake (I'm sure all the regulars have done it at least once) is to
be currently in the database you want to restore.
So you are effectively the user blocking your restore.
do a USE MASTER before the restore statement to eliminate this. (and make
sure enterprise manager isn't using the database in question.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Restore DB Failed

SQL server 2005
Try to restore database,
Can not restore it because Do not have exclusive access.
Have .DLL constantly connect to the database.
How could I restore database without stop SQL service
Thanks in advance
JeffWhat database you are trying to restore. If it is a user database probably
the database name which you are trying to restore is already in the same
server and used by some users.
If you want to overwrite the database then use the below command to set the
database to single user mode and then try restoring.
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Thanks
Hari
"jeff" wrote:
> SQL server 2005
> Try to restore database,
> Can not restore it because Do not have exclusive access.
> Have .DLL constantly connect to the database.
> How could I restore database without stop SQL service
> Thanks in advance
> Jeff
>|||On Feb 21, 12:40 pm, Hari Prasad
<HariPra...@.discussions.microsoft.com> wrote:
> What database you are trying to restore. If it is a user database probably
> the database name which you are trying to restore is already in the same
> server and used by some users.
> If you want to overwrite the database then use the below command to set the
> database to single user mode and then try restoring.
> ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> Thanks
> Hari
>
>
It is a user database, and the name is already being used. Trying to
restore by overwrite the existing one.
But if set to single_user mode, will that affect later connection? How
to find out if it is already in single mode?
Thanks|||Hello,
select databaseproperty('Master','IsSingleUser')
If it return 1 then database is in single user mode. Change the DBname to
yours.
Thanks
Hari
"jeff" wrote:
> On Feb 21, 12:40 pm, Hari Prasad
> <HariPra...@.discussions.microsoft.com> wrote:
> > What database you are trying to restore. If it is a user database probably
> > the database name which you are trying to restore is already in the same
> > server and used by some users.
> >
> > If you want to overwrite the database then use the below command to set the
> > database to single user mode and then try restoring.
> >
> > ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> >
> > Thanks
> > Hari
> >
> >
> >
> It is a user database, and the name is already being used. Trying to
> restore by overwrite the existing one.
> But if set to single_user mode, will that affect later connection? How
> to find out if it is already in single mode?
> Thanks
>|||be sure your own connection is not using the db. do 'use master' to switch
db context for your own connection before initiating the restore.
--
-oj
"jeff" <jeffzhang44@.gmail.com> wrote in message
news:1172080049.968263.170350@.q2g2000cwa.googlegroups.com...
> On Feb 21, 12:40 pm, Hari Prasad
> <HariPra...@.discussions.microsoft.com> wrote:
>> What database you are trying to restore. If it is a user database
>> probably
>> the database name which you are trying to restore is already in the same
>> server and used by some users.
>> If you want to overwrite the database then use the below command to set
>> the
>> database to single user mode and then try restoring.
>> ALTER DATABASE <DBNAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> Thanks
>> Hari
>>
> It is a user database, and the name is already being used. Trying to
> restore by overwrite the existing one.
> But if set to single_user mode, will that affect later connection? How
> to find out if it is already in single mode?
> Thanks
>|||On Feb 21, 3:23 pm, "oj" <nospam_oj...@.home.com> wrote:
> be sure your own connection is not using the db. do 'use master' to switch
> db context for your own connection before initiating the restore.
> --
> -oj
>
What does this mean? Could you be more specific? Thanks|||"jeff" <jeffzhang44@.gmail.com> wrote in message
news:1172091728.199686.230500@.s48g2000cws.googlegroups.com...
> On Feb 21, 3:23 pm, "oj" <nospam_oj...@.home.com> wrote:
>> be sure your own connection is not using the db. do 'use master' to
>> switch
>> db context for your own connection before initiating the restore.
>> --
>> -oj
> What does this mean? Could you be more specific? Thanks
>
Common mistake (I'm sure all the regulars have done it at least once) is to
be currently in the database you want to restore.
So you are effectively the user blocking your restore.
do a USE MASTER before the restore statement to eliminate this. (and make
sure enterprise manager isn't using the database in question.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Restore database fails because database is in use.

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

restore

hello all
How do I get exclusive rights to the db. I tried to set it to offline but
the db refused to let me while users are connected.
Please advise
Regards
AndreAndre,
Check out the WITH ROLLBACK IMMEDIATE and ROLLBACK AFTER arguments of the
ALTER DATABASE statement.
HTH
Jerry
"Andre Gibson" <AndreGibson@.discussions.microsoft.com> wrote in message
news:708D1B6B-73C6-4D69-8405-5BD35DCC20F5@.microsoft.com...
> hello all
> How do I get exclusive rights to the db. I tried to set it to offline but
> the db refused to let me while users are connected.
> Please advise
> Regards
> Andre

restore

hello all
How do I get exclusive rights to the db. I tried to set it to offline but
the db refused to let me while users are connected.
Please advise
Regards
AndreAndre,
Check out the WITH ROLLBACK IMMEDIATE and ROLLBACK AFTER arguments of the
ALTER DATABASE statement.
HTH
Jerry
"Andre Gibson" <AndreGibson@.discussions.microsoft.com> wrote in message
news:708D1B6B-73C6-4D69-8405-5BD35DCC20F5@.microsoft.com...
> hello all
> How do I get exclusive rights to the db. I tried to set it to offline but
> the db refused to let me while users are connected.
> Please advise
> Regards
> Andre