Newbie Q: Restore fails with "Database in use"

  • kranitz

    Old Hand

    Points: 338

    I'm apparently very late to the game here - I intermittently (reluctantly) end up becoming someone's DBA and used to use dbforums.com all the time - great community, lots of great help provided. Looks like that ship sailed a couple years back! Anybody know what happened to them? That crap site in place of it is kind of a joke. To replace all that great knowledge with another newsfeed is really a shame...

    Anyhoo, I'm trying to improve a simple DB restore script that intermittently fails with "Database in use". Some nights it runs no problem. Other nights, not so much.

    Here's the script:

    ALTER DATABASE nfache1
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    go

    RESTORE DATABASE nfache1nrt
    FROM DISK = 'C:\Temp_NRT\nfache1NRT.BAK'
    WITH MOVE 'Sneferu_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.NRTDB\MSSQL\DATA\nfache1nrt.mdf' ,
    MOVE 'Sneferu_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.NRTDB\MSSQL\DATA\nfache1nrt_log.ldf' , replace
    go

    -- set back to multi
    ALTER DATABASE nfache1 SET MULTI_USER
    go

    Super simple 2016 SQL Server environment, only hosting three databases.

    The "workaround" that I have is to just manually restart the SQL server and the run the restore script: Voila! It works.

    I'd rather approach it programmatically by adding something to the above script rather than running a separate schedule system task to complete the SQL restart.

    Any suggestions from you pros?

    Thanks in advance 🙂 Glad to be here...

    BTW, I couldn't help it - "Avatar" for my avatar! I'm sure that's the oldest pun in the book but hey...

    • This topic was modified 7 months, 2 weeks ago by  kranitz.
    • This topic was modified 7 months, 2 weeks ago by  kranitz.
  • Michael L John

    One Orange Chip

    Points: 25917

    Simply, when the restore is attempted, there are open connections to the database.

     

    It appears you are attempting to close the connections to the database by setting it to single user, which should work fine.

    However, if you look at your code, you are setting the database named "nfache1" to single user, and then you are attempting to restore the database named "nfache1nrt".

    It appears you need to set the database named "nfache1nrt" to single user.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • kranitz

    Old Hand

    Points: 338

    Super helpful - I'm going to adjust that and try it out tonight. As you can see, I shouldn't be allowed to manage other folks' SQL environments lol 😉

    This might have been a super easy one - I will let you know the results.

    Many thanks for taking the time to help out, Michael.

  • Chris Harshman

    SSC-Forever

    Points: 42081

    I tend to use this instead:

     ALTER DATABASE [mydbname] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

    this would prevent any non SYSADMIN type users from getting in before the restore properly initiates.

  • kranitz

    Old Hand

    Points: 338

    OK - incorporating both Chris and Michael's suggested edits. How does this look:

    ALTER DATABASE nfache1nrt
    SET RESTRICTED_USER WITH
    ROLLBACK IMMEDIATE
    go

    RESTORE DATABASE nfache1nrt
    FROM DISK = 'C:\Temp_NRT\nfache1NRT.BAK'
    WITH MOVE 'Sneferu_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.NRTDB\MSSQL\DATA\nfache1nrt.mdf' ,
    MOVE 'Sneferu_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.NRTDB\MSSQL\DATA\nfache1nrt_log.ldf' , replace
    go

    -- set back to multi
    ALTER DATABASE nfache1nrt SET MULTI_USER
    go

    I'm assuming that the second to last line will set the database back to multi user so everybody can get back into it?

    Thanks!

  • Michael L John

    One Orange Chip

    Points: 25917

    The second to last line will not matter, unless the database was set in that manner prior to the backup you are now restoring.

    Restoring the backup will re-create the setting as they were when the backup was taken.

    If it's already multi-user after the restore, this code will not really do any harm. It will, however, kill all the connections to the database.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • kranitz

    Old Hand

    Points: 338

    OK - Thanks for the clarification. That makes sense to me.

    I'll check in the AM to see how it goes. Thank you, gentlemen!

  • Admingod

    SSCertifiable

    Points: 5827

    If it's DEV or QA then probably you can delete the db and then restore it.

  • prettsons

    SSCertifiable

    Points: 7439

    RESTRICTED_USER Access Mode

    In RESTRICTED_USER access mode, only the users who have db_owner or db_creator permission can access. Users who belong to the sysadmin fixed server role can also access the database which is in RESTRICTED_USER access mode.

    SQL Database Recovery Expert 🙂

  • kranitz

    Old Hand

    Points: 338

    I wanted to let all know that the suggested changes worked perfectly. I have had no issues with the restore since implementing. MANY thanks to Michael and Chris for their assistance. While my interactions may be few, I'm grateful for your expertise 🙂

  • kranitz

    Old Hand

    Points: 338

    Spoke too soon!

    Last night, I encountered another case of "Exclusive access could not be obtained because the database is un use. [SQLSTATE 42000] (Error 3101).

    I read up more on the forum and wanted to see if the following change makes sense.

    I changed the following:

    ALTER DATABASE nfache1nrt
    SET RESTRICTED_USER WITH
    ROLLBACK IMMEDIATE
    go

    with

    USE MASTER
    ALTER DATABASE nfache1nrt
    SET OFFLINE WITH
    ROLLBACK IMMEDIATE
    go

    Finally, can anyone confirm that while I'm initially taking the database offline, the following command will bring it back online at the same time as making it multi user?

    -- set back to multi
    ALTER DATABASE nfache1nrt SET MULTI_USER
    go

    Thanks in advance...

    • This reply was modified 6 months, 3 weeks ago by  kranitz.
  • kranitz

    Old Hand

    Points: 338

    No need to reply, as the proof is in the pudding! The updated process works like a charm (until it doesn't).

    Thanks again

Viewing 12 posts - 1 through 12 (of 12 total)

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