February 3, 2007 at 2:22 pm
I am trying to use Microsoft SQL Server Management Studio to restore over an existing by highlighting the data base that I want to restore and right clicking and choosing task and then restore.
I then go to the Options page and check the Overwrite existing data base option.
I then click OK.
I keep getting an error message saying "TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'RBDS01'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
I guess I must be missing something but I just don't know what.
I would appreciate if someone could help me on this.
Thanks.
Rick Bellefond
February 4, 2007 at 5:45 am
This is because someone is connected to the database. If anyone is accessing the database you will be prevented
from restoring it.
If you look in activity monitor you will see what connections are active againast the database and you can then
either phone some people and ask them to log out of whichever application is using the database or kill their
connection if you wish.
hth
David
February 5, 2007 at 1:23 am
I completely agree with what David has suggested.
For restoring a database by overwriting the existing database, you need exclusive locking until the restore process is completed. So run a sp_who active on your database on which your are trying restore and kill the process by using kill spid syntax or just disconnect your network and restore the database. If your database is actively operational by some applications, it does not make any sense to disconnect it without intimating the users. So make the right decision based on your requirements before trying to restore the database.
Thanks
Prasad Bhogadi
www.inforaise.com
February 5, 2007 at 9:49 am
Prasad,
First of all thanks for responding.
If I am going to be doing this when I know no one else is on the system what is the easiest way to get exclusive locking on the database that I want restored? I would appreciate if you could use the exact syntax since I am relatively new to SQL 2005.
Thank you.
Rick Bellefond
February 5, 2007 at 10:27 am
There are a few ways to do this.. you could open up SSMS(managment studio) and right click on the DB in question and go to properties.. you can then select options and change the DB to single user mode (which will kill all other connections) and then do your restore.
Or you can open up a query window and run sp_who2... this will give you a list of the processes running on the server.. note the SPID(s) which are using the DB in question and then run "kill SPID*" *enter spid number.. then restore
Or you can open up Activity Monitor and look for those processes acting on the DB in question and right click on them and select "kill process"..then restore
Hope that helps!
February 5, 2007 at 11:03 am
Thes fastest way to get everyone out of the database before restoring over it is to execute this command
alter database MyDatabase set offline with rollback immediate
This will kick everyone out, and prevent them from reconnecting.
February 5, 2007 at 11:35 pm
also u can use the script from the below link to kill all users in the database.
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=30
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply