Post reply

Cannot restore - Database in use even after kill process

  • 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

  • March 8, 2007 at 4:07 pm

    #693960

    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

  • March 8, 2007 at 4:14 pm

    #693964

    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.

  • March 8, 2007 at 4:41 pm

    #693966

    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

  • March 9, 2007 at 2:28 am

    #694014

    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

  • March 9, 2007 at 3:40 am

    #694027

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

  • March 9, 2007 at 6:02 am

    #694059

    just to be sure, put

    use master

    go

    before the restore step

  • March 9, 2007 at 11:30 am

    #694156

    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.

  • March 9, 2007 at 11:14 pm

    #694218

    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

  • March 14, 2007 at 7:14 am

    #694919

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

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

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