October 18, 2007 at 1:38 am
Hi,
Just hoping one of you guys here can help me out with a problem I'm having with restoring our master database in sql 2005 as I am continually getting this "Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally" error.
The way things are now, I am stopping the server, which should kick off any residule connections, restarting it in single user mode, then attempting the restore with no luck. Is there anything i'm missing cause for the life of me I cant figure it out.
No one else is accessing the server and all I can see accessing the db is a couple of background services which cant be killed? I tried killing one, but it failed so I didnt bother with any others.
Tried stopping all the services relating to sql on the machine but that didnt help it either.
And strangely enough, the process works fine if i attempt it on our sql 2000 environment.
Any thoughts and input will be much appreciated.
Many Thanks,
Andrew
October 18, 2007 at 5:36 am
hi
do yuo have any jobs or any other background process thats accessing master db.
"Keep Trying"
October 19, 2007 at 1:41 am
Hi
How are you trying to restore?
SQLCMD may be an option - see BOL "How to: Restore the master Database (Transact-SQL)"
C:\> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak';
2> GO
My gotcha - I started in single user, then connected to my server via ssms (OK) then tried to open a query window to run the restore (second session - blat).
Good luck.
October 19, 2007 at 2:07 am
Using sqlcmd is certainly a good idea :). People often forget that SSMS's Object explorer also uses a connection, and in single user mode this will prevent new query windows.
Regards,
Andras
October 21, 2007 at 5:18 pm
Thanks for the replies guys,
Found out what was doing the kaput on the restore job and it was when the server was restarting in single user mode, the restore function was being sent abit too early while the server was still starting up and not ready yet, (though it should of been) so it was rejecting it and giving the crazy error code about exclusive access. Inserted a wait command and a retry if failed and presto, working restore.
Whoever put in that particular error code for that instance should be shot 😀
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply