Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Oct 4 '12 at 19:52

This question came from our site for professional and enthusiast programmers.

5  
I would guess that switching the DB context (i.e. "USE master" at the beginning) would do the trick. You cannot restore a DB if you are in that DB context with your connection. –  TToni Oct 4 '12 at 19:57
    
5 upvotes? Do you guys work with SQL Server at all? –  孔夫子 Oct 4 '12 at 22:34

4 Answers 4

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
Is restarting the database the only way to solve the problem.Any other suggestions?! –  John Oct 4 '12 at 20:40
5  
@John take the database offline. Why do you care if you take if offline? You're about to replace it and kick out all the users anyway. What's the problem? –  Aaron Bertrand Oct 4 '12 at 21:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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