SQL Server DB restore fails

Question

I am trying to do a SQL Server database restore using this T-SQL code:

----Make Database to single user Mode
ALTER DATABASE xyz
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE xyz
FROM DISK = 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\xyz_backup_201204100301.bak'
WITH MOVE 'abc' TO 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.MDF',
MOVE 'abc_log' TO 'E:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.LDF'

but while running the above i get the following error:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'xyz' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

How can I run my restore successfully? Is restarting the database the only way to solve the problem. Any other suggestions?!

Answer 1

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'xyz' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

This error does not occur if YOU are the single-user in the database. It only occurs if someone else is in it AND it is ALREADY in single-user mode.

For example, try this:

create database test
--
use test
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Note: there are 4 separate batches, and the 4th one doesn't error. You're setting the mode while IN the database, and also setting it a 2nd time. No error.

Assuming you have been given the mandate to restore the DB regardless of what is happening, do an sp_who2 to look for the user who is connected, and KILL the spid. You can then proceed with the RESTORE.

Answer 2

Your Database is probably already in single_user mode, and a session lock has been acquired on it.
I suggest you use the following code to find the session that owns the lock:

USE master; 
SELECT request_session_id 
FROM sys.dm_tran_locks
WHERE DB_NAME(resource_database_id)='xyz'
AND request_session_id != @@SPID

Now use KILL to release the lock:
for example if the query returned 60 run KILL 60 and recheck for session locks.
(if new sessions keep appearing there might be an application trying to connect,
you can use that code to create dynamic SQL and perform a quick kill + restore)

Once you have no more locks use this script instead of yours:

----Make Database to single user Mode
USE master;
ALTER DATABASE xyz SET MULTI_USER
GO
ALTER DATABASE xyz SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

----Restore Database
RESTORE DATABASE xyz
FROM DISK = 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\xyz_backup_201204100301.bak'
WITH MOVE 'abc' TO 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.MDF',
MOVE 'abc_log' TO 'E:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.LDF'  

Let me know if that worked for you, and if any further assistance is needed.

Regards,
Roi.

Answer 3

Assure that you are the only account trying to access the database, then try restarting the database.

Answer 4

Checkout these threads would be help you:

https://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state

http://msdn.microsoft.com/en-us/library/ms191253.aspx

http://www.howtogeek.com/50354/restoring-a-sql-database-backup-using-sql-server-management-studio/

Thanks