Close existing connections

  • While attempting to use SSMS to do a "database" restore I am unable to select "Close existing connections to destination database" on the options page of the restore wizard. In the past I have been able to use this. All of my server except one are now like this. Any ideas what I have done to cause this?

  • I seem to see the same thing. Probably a glitch in the SSMS GUI. (Which I never use.)

    You can always issue ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMIEDATE followed by ALTER DATABASE db SET MULTI_USER after the restore.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • I do it by script like Erland did except I go back to Multi-User immediately after the set to Single-User. Web servers won't be able to respond fast enough to interfer with the restore and it keeps you from having to find and kill all the web servers if you just happen to lose the connection while the restore is in progress.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Script it, much cleaner:

    Use Master

    Alter Database [DATABASE_NAME_HERE]

    SET SINGLE_USER With ROLLBACK IMMEDIATE

    RESTORE DATABASE [DATABASE_NAME_HERE] FROM DISK 'X:\backup\DATABASEBACKUPFILE.bak'

    WITH REPLACE

    GO

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

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

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