Trying to drop a database via daily update

Question

Trying to drop a database via daily update

jarid.lawson

SSCrazy

Points: 2953
More actions
March 12, 2012 at 3:38 pm

#269821

I am in a good mood because my first SSIS package is up and running. Sorry, I just wanted to brag for a second.:-D This was one that I set on our client's system which backs up our records only from their main database. They have an automated push to our SFTP server, and now I am trying to set up a process that will put that data where it is supposed to go on our side.
Since they are sending over a .bak file, it is as simple as running a restore statement, right?...uhhh, no. To restore the database I have to isolate it from all users. This shouldn't be an issue since it will run at 5:30 AM (even my power users aren't on that early), but I know there will be times when users will still be attached in some way (even if it is a simple as the system didn't release the connection).
How do I kill all connections without manually looking up each SPID and running a kill statement? I thought I'd use...Alter Database DB_Name Set Offline ...but when I tested the command it ran for almost 4 minutes and was doing nothing. I had another connection open to the DB to ensure it would kill the connection, but it never went down.
Thoughts?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]

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

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

you can try setting it to single user mode. then after you restore alter the database back to multi-user mode. is should work and will kill any connections to the database other than the one issuing the command.

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

http://technet.microsoft.com/en-us/library/ms345598(v=sql.110).aspx

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

capn.hector (3/12/2012)
you can try setting it to single user mode. then after you restore alter the database back to multi-user mode. is should work and will kill any connections to the database other than the one issuing the command.
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
http://technet.microsoft.com/en-us/library/ms345598(v=sql.110).aspx

This is what I have done at the top of a restore script that automated the restore of a database on a reporting server. Worked well.

jarid.lawson SSCrazy Points: 2953 More actions · Answer 3

Perfect, that seems to have gotten it so far. Now I'll see if this works in an SSIS package. I'll post an update about how it goes when I test it out.

Thanks!

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]