Post reply

Restoring Database - Kill Connections

  • September 16, 2009 at 2:39 am

    #210917

    Hi,

    How do one kill all connections to a database before restoring it. I know one can do it via the Managament Studio but I need a script to kill all connections to a database before restoring it. I want to automate the restore process via a command line.

    Any help please

    Thanks

  • September 16, 2009 at 2:46 am

    #1054299

    search for kill connections in the search box on this site and you will get numerous scripts

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • September 16, 2009 at 2:48 am

    #1054300

    Thanks!

    P.S. First Time user....Very nice site!

  • September 16, 2009 at 6:36 am

    #1054380

    You can set the database to single user mode or, what I usually do, restricted user mode and use WITH ROLLBACK IMMEDIATE. That will remove all the connections and leave the database in a state where no one else can connect to it.

    "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

  • September 18, 2009 at 5:16 am

    #1055506

    use master

    go

    USP_KILLPROCESS Database_Name

    go

  • September 18, 2009 at 5:54 am

    #1055523

    If you set database in a single_user mode WITH ROLLBACK IMMEDIATE, please make sure your own single_user connection doesn't keep the database in use, which will interrupt any restores. This is very common situation.

    I usually just take database offline before restore, and it kills all user connections of course.

  • September 18, 2009 at 6:01 am

    #1055527

    Thanks for all the info

    I have created an sp based on the info I got on the site. everything is working very well.

    Thanks

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

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