Search This Blog

Tuesday, 14 April 2015

Fixing database is in use error while restoring sql database.

Following is the one of the most useful query while restoring database. It is used when we get error: Database is in use while restoring db from the backup.
Simply, run below query and then again try to restore database. This time, you will able to restore it. Here is the magical query:

------------------------------------------------------------
--<Config>
------------------------------------------------------------ 
DECLARE @DatabaseName VARCHAR(max)

SET @DatabaseName = 'yourdbname'

------------------------------------------------------------
--</Config>
------------------------------------------------------------
USE master

DECLARE @processes TABLE (ID INT)
DECLARE @databaseID INT

SELECT @databaseID = dbid
FROM sys.sysdatabases
WHERE NAME = @DatabaseName

INSERT INTO @processes
SELECT spid
FROM sys.sysprocesses
WHERE dbid = @databaseid

WHILE EXISTS (
  SELECT TOP (1) *
  FROM @processes
  )
BEGIN
 DECLARE @Process INT
 DECLARE @statement VARCHAR(max)

 SELECT TOP 1 @process = id
 FROM @processes

 SET @statement = 'kill ' + cast(@process AS VARCHAR(20))

 EXEC (@statement)

 DELETE TOP (1)
 FROM @processes
END