Detach database dropping connections

  • A1Vijay

    Old Hand

    Points: 366

    Hey All,

    Is there a script to detach database w/ forcing to drop any existing connections? We have a few hundred databases and its real hard to do it manually for every single one. Thanks!

  • Mike - CI

    SSCarpal Tunnel

    Points: 4146

    You could do this by first bringing the database offline. You can see that described in this article

    http://www.sqlservercentral.com/articles/Administration/deattachandreattachdatabases/646/

    This seems like it should do what you are wanting done.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    You've been pointed to the solution 😎

    But, what's the reason you want to detach all databases without actually stopping the SQL instance ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • A1Vijay

    Old Hand

    Points: 366

    If I bring SQL offline, dont I need to bring it online to detach the DBs? In that case, I establish new connections before I can run the detach script.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    I'm not quit with you on your statement .. :ermm:

    Why do you want to detach the database ?

    What's the point of detaching your dbs ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Mike - CI

    SSCarpal Tunnel

    Points: 4146

    Yes you would have to bring it back online to detach, however you would have the ability to bring it back online in single_user mode, which would keep those other users from being able to connect. Plus you would be able to script this out in such a way that you would be able to detach the database immediately after bringing the db online (single user mode would just give you that much more room to maneuver).

    Something like

    alter database yourdb

    set offline with rollback immediate

    GO

    alter database yourdb

    set SINGLE_USER

    GO

    exec sp_detach_db @dbname = 'yourdb'

    If you wanted to scroll through other DB's and parameterize this so that you do not have to manually type them in there are certainly ways you could do that as well.

  • GTR

    SSCertifiable

    Points: 7977

    A1Vijay (2/22/2010)


    If I bring SQL offline, dont I need to bring it online to detach the DBs? In that case, I establish new connections before I can run the detach script.

    As ALZDBA mentioned why do you want to detach a DB? Is there any specific reason you want to do this, It is not recommended until and unless required.

    EnjoY!

    EnjoY!
  • mangokun

    Valued Member

    Points: 69

    MIKE S's script works perfectly for purging active connections.

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

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