February 22, 2010 at 12:52 pm
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!
February 22, 2010 at 1:29 pm
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.
February 22, 2010 at 1:37 pm
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
Learn to play, play to learn !
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 but most of the time this is me
February 22, 2010 at 1:44 pm
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.
February 22, 2010 at 1:51 pm
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
Learn to play, play to learn !
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 but most of the time this is me
February 22, 2010 at 1:55 pm
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.
February 22, 2010 at 4:12 pm
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!
January 23, 2011 at 7:02 pm
MIKE S's script works perfectly for purging active connections.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply