Newbie Q: Restore fails with "Database in use"

  • kranitz

    SSC Veteran

    Points: 272

    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 3 weeks, 1 day ago by  kranitz.
    • This topic was modified 3 weeks, 1 day ago by  kranitz.
  • Michael L John

    One Orange Chip

    Points: 25792

    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

    SSC Veteran

    Points: 272

    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: 41810

    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

    SSC Veteran

    Points: 272

    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: 25792

    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

    SSC Veteran

    Points: 272

    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: 5630

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

  • prettsons

    SSCertifiable

    Points: 7437

    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

    SSC Veteran

    Points: 272

    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 🙂

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

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