I'm using SQL Server 2008 R2, I need to restore a database from a .bak file. There is always an error that the database is in use until I restart the SQL Server service.
Is there a better option to do that?
1
-
It's the easiest - and sometimes only - method I found that works consistently.Suncat2000– Suncat20002019-12-03 20:20:56 +00:00Commented Dec 3, 2019 at 20:20
Add a comment
|
9 Answers
Use this code to destory all existing connections before restore:
USE master;
GO
ALTER DATABASE YourDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDB
SET MULTI_USER;
GO
3 Comments
Muhammad Musavi
Just close all connections you opened, I mean close all windows you opened for New Query (and maybe change user default database to the master db).
Kellen Stuart
@Mohammadlm71 I wasn't aware that the query window acts as an active connection. That's weird actually
dgo
FWIW to anybody else. It was the query windows for me as well. @MuhammadMusavi - thanks.
Select the backup file to restore and go to options to select "Close existing connections to destination database" and click ok
answered Sep 14, 2016 at 9:58
Lokesh kumar Chippada
1,4711 gold badge12 silver badges10 bronze badges
6 Comments
kepung
Other approaches might work or it might not. Pretty sure this approach works.
Ignacio Soler Garcia
I don't have this options available ... :\
Suncat2000
This didn't work for me. The destination database didn't even exist yet.
Raymond Dumalaog
this works for me, because some of my team are using the db. have to forcibly close any existing connection to the db i want to restore
Wibisono Indrawan
well this didn't work for me, and now my DB is stuck in single user mode
|
Restarting the SQL Service solves my problems.
3 Comments
Marcus Downing
This isn't a particularly satisfying solution, but it worked for me too where the others didn't.
yeska
This is the only solution in some situations
Babu James
This worked straight
What worked for me was:
- right click on your db -> Tasks -> Take Offline
- restore db
- then, to bring it back online, right click on your db -> Tasks -> Bring Online
1 Comment
Community
As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
The problem is that your database is currently being used by others and probably has open connections. What you would need to do is close all the existing connections to it, and then restore your backup.
Here is a link showing how to kill active connections: http://wiki.lessthandot.com/index.php/Kill_All_Active_Connections_To_A_Database
Comments
USe activity monitor and filter on the DB you are wanting to restore. Then check with the user that is using it and make sure it is ok to resotre the DB. Then run the following query.
USE Master
KILL <session_id from activity monitor>
Comments
Shut down the database engine and start it again. Do not just restart because it doesn't stop the instance completely. The error may be fake but check your environment to confirm there are no running applications that automatically reconnect after losing a database connection.
I have never found an answer to explain why restoring from a file to a new database is considered "in use".
Sometimes, @Serjik's answer to take the origin database down to single-user and back has worked for me. About 80% of the time, I have to shut it all down and start it up again before I can restore through SQL Server Management Server.
Comments
If still not solved try to execute following
Restore database YOUR_DB_NAME with recovery
Comments
try restoring using SQL
RESTORE DATABASE my_new_database FROM disk = 'E:\path\name.bak'
sometimes I had these issues with UI but worked fine with SQL statement. you need to kill all the open process before restoring, sometimes the process can not close themselves due to some issues with your windows or SQL installation files.
