Monday, February 19, 2018

SQL Server | DB Restore Error

When you are working on the task of database backup and restores, you might have error while restroe the database "database in use". This error populate because the current database is in use on same machine or over the network. There can be below cases:
1. On SQL Server database query window is open
2. Database query window will be open on any other computer in network
3. Database is currently connected with application

So in order to restore the database you need to disconnect the application from you machine or from another machine over the network.

Now, it is easy to disconnect the query window which is opened in your machine

Problem
It is difficult for you if any body using database over the network. Then how to disconnect all the connections

Solution

Sql Server 2012
DECLARE @query_v nvarchar(4000) = '';  
SELECT @query_v = @query_v + 'kill ' + CONVERT(varchar(10), session_id) + ';' FROM sys.dm_exec_sessions WHERE database_id  = db_id('Your_database_name')
EXEC(@query_v);


For SQL Server less than equal to 2008
DECLARE @query_v nvarchar(4000);
SELECT @query_v = @query_v + 'kill ' + CONVERT(varchar(10), spid) + ';' FROM master..sysprocesses WHERE dbid = db_id('Your_database_name')
EXEC(@query_v);

Note * - 
change the database name in above query, for which you want to restore the database
After change the database name you need to execute the above query


While query execution you can have below error

Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.

Make sure while executing above query, close the current database query window or connect to "master" database

No comments:

Post a Comment

web stats