| Author |
Topic |
|
chwanhun
Starting Member
22 Posts |
 Posted - 2005-12-05 : 22:30:03
|
| am trying to use a SQL Server job to restore a database. The job keeps failing so I tried to run the same query in Query analyzer but I keeping getting two error messages.Here is the query:RESTORE DATABASE db_name from backupdevice_nameI also use the DISK = option and specifed the location of the file and still obtained the same results.Here are the results:Server: Msg 3101, Level 16, State 1, Line 1Exclusive access could not be obtained because the database is in use.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.When I go to detach the database in Enterprise Manager there is usually 1 user attached. I clear the user and cancel out of the detach window. When I go back into the detach window, there are no users attached. However, after I run the above queries, I still get the same message. Please help. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2005-12-05 : 23:20:11
|
| make sure you run the restore script in another DB (like master) rather than the target database that you are restoring to-----------------[KH] |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-12-05 : 23:20:36
|
| you must have an application that is reconnecting, many times web based apps will reconnect very quickly.You have a couple of options.1. Find a script that kicks all the users out of a datbase and then run the restore2. bring up two copies of Enterprise Manager. In the first EM, click throught the restore settings up to the point where you click the OK button to start (don't click it though). In the 2nd EM go to the current connections page and kick all users connect to your database. Make sure to refresh this view from the left panel since it does not dynamically refresh and can be inaccurate. When you have kicked all users, switch to the first EM and fire off the restore.also, running sp_who2 from query analyzer will tell you all the connections in the database and is usually quicker, more reliable than the view in EM.EDIT:I can dig out the script that I use and post to this thread as a followup (I use option 1 above). Give me a few minutes to get it from my work machine.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-12-05 : 23:28:54
|
Here is the Kill_All script that I use to kick all users out of a database. Not sure where I originally found this, but it seems to get the job done. You can put this in your master database, or more preferably in an ADMIN or DBA database that you use for administrative code..Execute the code like this (assuming you installed in a database called "ADMIN"):exec admin.dbo.Kill_All 'database name'-- Vinod : 2003. This procedure helps you to kill all the connections-- made to the database name passed.-- Have this SP on the master database and call it to close all the connectionsCREATE procedure Kill_All @dbname varchar(50) as Begin -- Declare all the relevant variables declare @counter int, @spid int -- Temp table to store all the connections Create table #StoreConnections ( id int identity(1,1), spid int, kpid int, dbid int ) -- Insert all the connection SPIDs ... Insert into #StoreConnections Select spid,kpid,dbid from master..sysprocesses where kpid = 0 and dbid = db_id(@dbname) and spid > 40 Set @counter = 1 Set @spid = (Select spid from #StoreConnections where id = @counter) -- Start killing all the SPIDs ... While @spid <> '' Begin Select @spid Exec ('Kill ' + @spid) Set @counter = @counter + 1 Set @spid = (Select spid from #StoreConnections where id = @counter) End -- Cleanup activity. Drop table #StoreConnections End GO-ec |
|
|
|
chwanhun
Starting Member
22 Posts |
Posted - 2005-12-05 : 23:42:43
|
| I was able to restore the db by taking the database offline via Enterprise Manager. Now I'm trying to find a script that kills all users and takes it offline so that I can add it as a step in the sql server job. Is it ok to kill all of the spids connected to the db? Thanks for all of your help |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-12-06 : 00:08:10
|
quote:
Originally posted by chwanhun
I was able to restore the db by taking the database offline via Enterprise Manager. Now I'm trying to find a script that kills all users and takes it offline so that I can add it as a step in the sql server job. Is it ok to kill all of the spids connected to the db? Thanks for all of your help
I'm not sure If I would add that to a step in a job. Killing all connections is a bit of a last resort. I normally like to contact all my users and have them gracefully disconnect from the database. However, you should be able to put together a script that does what you want. That seems fairly simple to accomplish. The script i posted earlier does most of the work for you, you just need to figure out the code to take a database offline.-ec |
|
|
|
chwanhun
Starting Member
22 Posts |
Posted - 2005-12-06 : 02:04:16
|
| It's all working now. I took KHTAN's advice and selected the master db instead of the db I was actually targeting in the sql server job. It ran flawlessly. Thanks for all of your help and your quick responses.cwh |
|
|
|
Kristen
Test
22859 Posts |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-12-06 : 12:33:55
|
quote:
Originally posted by Kristen
I useALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATEKristen
The only problem with this is if you have a web app that is real quick to reconnect. It can get in as that single user, not you.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-06 : 16:06:00
|
| Yeah, I can see that. Is there a way to do it "atomically"?Setting it to DBO at the same time might help - 'coz no website connects using 'sa', right? <thud/>Kristen |
|
|
|
|
SQL Server Restore job