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.
- What kind of mistake did I make to run into this kind of problem?
- Is there a way I am able to restore without asking people to stop their application whenever I want to refresh the Dev database?