SQL Server DB restore fails 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?!
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.