SQL Server 2008 R2 – Unable to Restore Database or Change User Mode

restoresql-server-2008-r2

I am unable to restore a database.

The database is stuck in single user mode. I cannot change to multi-user mode.

I was restoring the SQL Server database from Prod to Dev as I have done many times without issue.

I run the following scripts:

--1
use master

go

ALTER DATABASE [Acct] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Go

The database changed to single user mode.

--2
RESTORE DATABASE [Acct] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\ProdAcct.bak' 
WITH  FILE = 1,  MOVE N'ProdAcct' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Acct.mdf', 
 MOVE N'ProdAcct_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Acct_log.ldf',  
 NOUNLOAD,  REPLACE,  STATS = 10
GO

Error: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

--3 I run the following script to see the connection 

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'Acct'
go

--4 kill the connection
Kill 85
Kill 51

--5
exec sp_dboption 'Acct', 'single user', 'FALSE'
go

and I've tried:

ALTER DATABASE [Acct] SET MULTI_USER
Go

Both commands fail.

--6
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'Acct'
go

--7 connection 51 was  accessing
kill 51

But I wasn't able to kill it.

At this point I had to call the application owners and asked them to stop the applications they were running. After they stopped their app, I was able to restore the database.

Question:

  1. What kind of mistake did I make to run into this kind of problem?
  2. Is there a way I am able to restore without asking people to stop their application whenever I want to refresh the Dev database?

Best Answer

Here is what I do to ensure that I get the ONE (1) connection for SINGLE_USER.

USE MyDB;  -- Use the database you want to make SINGLE_USER
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE ----; /* Or whatever you need to do. */
GO
ALTER DATABASE MyDB SET MULTI_USER;
GO

The trick is to be in the database already when you switch to SINGLE_USER mode. This way, you are the SINGLE_USER and nobody else can get in until you return the database to MULTI_USER.

NOTE: If you exit the database while in SINGLE_USER mode someone else can take over the database. This can also cause you problems.

Related Question