Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    6

    Unanswered: Database Restore Problem

    I've created a Restore Job with owner sa, run as dbo, to refresh a Test database from Production.

    After ONE successful refresh from the Production database, this
    job fails with:
    Msg 3101, Sev 16: Exclusive access could not be obtained because the
    database is in use. [SQLSTATE 42000]
    Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
    42000]

    There are no users connected to the database or processes running against it.

    The syntax is as follows:
    ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE DBA_pfaids_test
    FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
    WITH REPLACE,
    MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
    MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'

    ALTER DATABASE DBA_pfaids_test SET MULTI_USER

    Does this have something to do with the initial refresh from Production?
    How can I get it to work?

    Thanks.
    Mary

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.

    You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.

    Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.

    Things would run so much better if we just didn't have to let users into our databases

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Mar 2006
    Posts
    6
    Thanks for you reply.

    Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by tomh53
    Things would run so much better if we just didn't have to let users into our databases
    ~"This job would be great if it were'nt for the customers"
    -Clerks
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).

    Lex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •