September 22, 2008 at 12:27 pm
I am trying to restore database and I am getting error"cannot restore database is currently in use".
I check the databse statuse by sp_who2 command, the status is in sleeping mode.Do I need to make database in single user mode while restoring if so then do I have to stop all the application before making database in single user mode. Please let me know.
Abhisek
September 22, 2008 at 2:05 pm
The DB doesn't have to be in single user mode, but there must be no connections using that database, sleeping or otherwise.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2008 at 4:40 am
Yeah you'll need to get all those connections to close. That includes the window you are running the sp_who2 in.
September 24, 2008 at 6:05 am
I run the following script when I need to close out connections.
alter database [db_name] set SINGLE_USER with rollback immediate
go
alter database [db_name] set MULTI_USER with rollback immediate
go
_____________________________________________________________________
- Nate
September 25, 2008 at 6:20 am
RP_DBA given you perfect solution for your problem.
Rajesh Kasturi
September 26, 2008 at 2:02 pm
Go to Current Activity in the Management Folder.
Kill the process that is using this DB that you want to restore. Single User, will not help the cause. The error says that the DB base should not be in use, doesnt mean it has to be in single user. If you see your login against the DB, no matter what state it is in KILL it and you are all set to restre...HOPE THIS HELPS!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 26, 2008 at 2:44 pm
While KILL works isn't it easier to alter the db to SINGLE_USER WITH ROLLBACK IMMEDIATE than having to potentially hunt down and kill multiple processes?
[Quote]Setting Database Options (http://msdn.microsoft.com/en-us/library/aa933082(SQL.80).aspx)
SINGLE_USER | RESTRICTED_USER | MULTI_USER
SINGLE_USER allows one user at a time to connect to the database. All other user connections are broken.
WITH
The termination clause of the ALTER DATABASE statement specifies how to terminate incomplete transactions when the database is to be transitioned from one state to another. Transactions are terminated by breaking their connections to the database. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely, until the transactions commit or roll back on their own.
ROLLBACK AFTER integer [b]SECONDS[/b]
ROLLBACK AFTER integer SECONDS waits for the specified number of seconds and then breaks unqualified connections. Incomplete transactions are rolled back. When the transition is to SINGLE_USER mode, unqualified connections are all connections except the one issuing the ALTER DATABASE statement. When the transition is to RESTRICTED_USER mode, unqualified connections are connections for users who are not members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles.
ROLLBACK IMMEDIATE
ROLLBACK IMMEDIATE breaks unqualified connections immediately. All incomplete transactions are rolled back. Unqualified connections are the same as those described for ROLLBACK AFTER integer SECONDS.
[/Quote]
_____________________________________________________________________
- Nate
September 26, 2008 at 3:12 pm
Well if there are many a processes that are active on the DB then your process works fine. I wouldn't be searching for a number of transactions and kill them...thanks!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply