Cannot restore - Database in use even after kill process

Question

Cannot restore - Database in use even after kill process

Ramesh Vekaria

Ten Centuries

Points: 1157
More actions
March 8, 2007 at 3:59 pm

#117889

Help! - Trying to restore a db to another server. I have a job that kills all processes (SPIDs) and then a restore and getting the error message below.Sometimes it will work but most nights it will not.
""Executed as user: NT AUTHORITY\SYSTEM. Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed"
Have checked via SP_who2 to ensure no one is using or logged on but still to no avail. Have even put the database in single user mode but still getting error when running the job
I can restore manually via the EntMgr restore database without putting the database in single user mode, but I need to automate it and run overnight.
Appreciate any help. Ram

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

If you can restore it using EM and your SP_WHO2 does not show connected users, your restore should work. Are you sure you are restoring to the correct DB? Are you using the WITH MOVE and WITH REPLACE clauses?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ramesh Vekaria Ten Centuries Points: 1157 More actions · Answer 2

Yes, I am using WITH REPLACE. However, I am not using WITH MOVE as the location structure is the same as the production server. There is only one app database on the server.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Make sure there are no jobs running. If you can, stop the SQL Server Agent until you are done.

Are you using Enterprise Manager and then opening Query Analyzer to do the restore? If so, that is two connections EVEN if you open Query Analyzer from within Enterprise Manager.

-SQLBill

Ramesh Vekaria Ten Centuries Points: 1157 More actions · Answer 4

There are no jobs running and I am not using QA. I have a job setup which is failing. To restore manually I right click on the db in EntMgr and selct restore database, select a device pointing to the back file and that works.

Ram

Si SSCommitted Points: 1537 More actions · Answer 5

In your SQL job, youve not coded 'use dbname' where the dbname is the one your trying to restore have you?

george sibbald2 SSC Enthusiast Points: 155 More actions · Answer 6

just to be sure, put

use master

go

before the restore step

william david wynn SSC Enthusiast Points: 120 More actions · Answer 7

Ramesh, I have seen that occasionally when a tran log is active. Try putting in a command to detach the database before starting the restore process.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

If you want to make sure the database is not in use, just set it offline before running the restore:

use master
alter database MyDatabase set offline with rollback immediate

Ramesh Vekaria Ten Centuries Points: 1157 More actions · Answer 9

Thanks to all. I used Williams and Michael suggestion and that seems to have cured it. Thanks again ! - Ram