March 17, 2009 at 2:46 am
Hi all,
I want to restore a backup file on an existing database. But it is warning "Database in use" and ends restore.
How do i stop active connections to restore backup without detaching database.
Regards.
March 17, 2009 at 2:58 am
This [/url]script might work for you.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 17, 2009 at 4:24 am
start with setting the current database to another state !
e.g.
Alter database set single_user with rollback immediate
-- don't put GO overhere or you may lose your db connection !! (so your queries are executed in a single batch !!! )
restore .....
-- keep in mind your db will be restored to the same state as when the backup has been taken.
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
March 17, 2009 at 6:39 am
as ALZDBA suggested put your DB in single user model it should kick everyone out and then restore the database and fix the Orphan users
March 17, 2009 at 7:31 am
if you're going to overwrite it then just detach it and tell the GUI to kill all connections. i've found some apps like weblogic like to reconnect as soon as you kick them out
March 17, 2009 at 7:51 am
All the other scripts and commands seems good (single_user),
I have this command before restore command and it works:
ALTER DATABASE [Database_Name]
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Hope That Helps
March 18, 2009 at 7:16 am
If you decide to set the database to single user mode, don't forget to set it back to multi user mode when finished.
ALTER DATABASE [Database_Name] SET MULTI_USER
March 18, 2009 at 7:58 am
rlondon (3/18/2009)
If you decide to set the database to single user mode, don't forget to set it back to multi user mode when finished.ALTER DATABASE [Database_Name] SET MULTI_USER
Keep in mind your db will be restored to the same state as when the backup has been taken.
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
March 18, 2009 at 8:21 am
How about deleting the database, since the restore will delete it anyway. Then no one can reconnect before the restore begins.
March 18, 2009 at 12:28 pm
If you set it to single user you risk getting blocked before you can start the restore by a user or application connecting to it.
Better going the ALTER DATABASE x SET OFFLINE WITH ROLLBACK IMMEDIATE route......It'll be brought online when the restore finishes.
March 18, 2009 at 12:33 pm
homebrew01 (3/18/2009)
How about deleting the database, since the restore will delete it anyway. Then no one can reconnect before the restore begins.
You still have to get the users out of the database before you can drop it.
Just go with this and then do the restore.
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply