Post reply

Restoring DB error

  • June 24, 2020 at 4:21 pm

    #3764964

    Hi all,

    New guy on the block please be patient if Q's are dumb!!!

    When I try to restore a DB it keeps giving me an error that the DB is in use.

    I have closed the DB and even then I get the error. So I will have to close SSMS and restart for it to work.

    What am I doing wrong.

    Thanks

    Paps

  • June 24, 2020 at 4:46 pm

    #3764966

    If there is an application that uses this database, then it's likely trying to connect over and over. Closing it will clear the exiting connections.

    If you are in SSMS, you need to be in the master database.

     

    If you are using the GUI to do a restore, the one of the options is to close existing connections. If you are restoring by using a T-SQL command, then add this to the top of the script:

    USE master;

    ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

     

    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/

  • June 24, 2020 at 5:55 pm

    #3764969

    Thanks

  • June 25, 2020 at 11:52 am

    #3765035

    If you're by yourself on the server and you've recently had the database open in the Object Explorer in SSMS, say looking at tables or something, in addition to changing your connection to another database, make sure that in Object Explorer you open a table list or something in the other database. I've seen SSMS maintain that other connection until told to do something else. Setting restricted use might not help since you have restricted access.

    Also, if this is a production system, be darned sure you're on the right database before you kick everyone out with ROLLBACK IMMEDIATE. It's not lying. It'll rollback all transactions, kick out everyone but restricted users. Many people have accidently brought down production systems because they did this in the wrong spot.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

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