March 28, 2012 at 3:24 pm
Hello!
I have a new server that I have installed SQL Server 2008 R2 SP1 on. I created a single user database on it and ran a script (provided from a 3rd party vendor) to create a few objects in the new database. Unfortunately, by accident, I ran the script on the msdb, rather than the user database. Now I have tables and views and in the msdb that aren't supposed to be there. I decided to attempt a restore on the msdb from a backup that I created last night. I stopped the SQL Agent via Config Manager and attempted the restore. The restore failed, and I am getting the following message:
[font="Courier New"]Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.[/font]
I have nothing else running or accessing the server, so what else is using the msdb if the SQL Agent is stopped?
Thanks for your help!
March 28, 2012 at 3:28 pm
reefshark (3/28/2012)
Hello!I have a new server that I have installed SQL Server 2008 R2 SP1 on. I created a single user database on it and ran a script (provided from a 3rd party vendor) to create a few objects in the new database. Unfortunately, by accident, I ran the script on the msdb, rather than the user database. Now I have tables and views and in the msdb that aren't supposed to be there. I decided to attempt a restore on the msdb from a backup that I created last night. I stopped the SQL Agent via Config Manager and attempted the restore. The restore failed, and I am getting the following message:
[font="Courier New"]Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.[/font]
I have nothing else running or accessing the server, so what else is using the msdb if the SQL Agent is stopped?
Thanks for your help!
I would imagine that SQL Server itself uses it for stuff like keeping track of backups, replication stuff, etc, just from a cursory look.
March 28, 2012 at 3:32 pm
Kill the connections still using msdb.
But rather than that - I would likely just drop the new objects and move on.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2012 at 3:50 pm
I would drop those new objects, but there are hundreds of tables that were created. Is there any EASY way of doing this?
March 28, 2012 at 3:55 pm
Ask the vendor if they have a rollback script.
Sometimes they do.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 29, 2012 at 4:02 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply