2

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?
3

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.

3
  • @RLF..I use the same script you wrote. But I did USE Master Go. do you think that might be the problem. I should have done USE Acct GO – SQL_DBA_Bini May 5 '15 at 16:07
  • 1
    @SQL_DBA_Bini - the point RLF is making is that if you USE MyDB then switch the database to SINGLE USER mode, you ensure no other connection is able to connect while the RESTORE DATABASE statement is getting ready to run. – Hannah Vernon May 5 '15 at 16:37
  • Thank you @a-horse-with-no-name for editing my question . – SQL_DBA_Bini May 5 '15 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.