February 23, 2006 at 7:14 pm
Greetings, this may be sort of a newbie question. We are testing our backup/restore process prior to putting a new database into production. Prior to the test I backed up the database and copied the backups to tape. After this the machine was wiped clean and the O/S reinstalled. I then reinstalled SQL Server and am now attempting to restore the Master and MSDB database from the backups. I am attempting to restore the MSDB database using the Enterprise Manager, highlighting the database then ALl Tasks-> restore database and then locating the backup. When I attempt to begin the restore I am presented the message
Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.
My question is hos to do this. I have not been able to locate information with instructions on completing this task. Any assistance is greatly appreciated.
Thanks.
February 23, 2006 at 7:22 pm
This error message is quite common when trying to restore a database. I would normally have a script handy that kills all active connections (spids) to a database before restoring.
BOL states:
Meta Data Services uses msdb as the default repository database. An open connection between Meta Data Services and msdb will disrupt an msdb restore. To release the connection, restart Enterprise Manager and then restore msdb. Do not click the Meta Data Services node in Enterprise Manager until msdb is fully restored.
I think this is more your problem.
February 23, 2006 at 8:03 pm
Greetings, I restarted both the MSSQLSERVER and SQLSERVERAGENT services. I then bring up the Enterprise Manager and attempted the restore again with the same message displayed. I did not click on the Meta Data Services at all. What I did see was two processes accessing the msdb so I stopped the SQLSERVERAGENT and was then able to restore the msdb database. It appears to have been successful as my maintenance plans are back. I am not quite certain I understand what happened, if you could add some detail to the explanation I would appreciate it. Thanks.
February 23, 2006 at 9:44 pm
The msdb database contains meta-data information including sql server agent jobs (when you create scheduled jobs), DTS packages, Replication, etc. So you can see that SQLSERVERAGENT will need access to the msdb database at some point on a regular basis.
When you stopped SQLSERVERAGENT, you no longer have a connection to the msdb database. This is important, as it prevents you from restoring the msdb database. And you realised this when you execute the sp_who2 command in QA.
All processes running on a server are stored in the master..sysprocesses system table. Here, you can determine what processes have connections to a particular database (here represented by the server process id or spid for short).
When you make a new connection from QA, you will see (at the status bar near the bottom) a number in brackets next to your user name - this is the spid for your current connection. By opening another window in QA will create a new connection and a different spid.
To kill a process, simply use kill [spid], where [spid] is the numeric value of the process id. For more info, consult the online documentation (BOL), and type 'SPID'.
Congratulations on you success!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply