unable to restore database in sql server (single_user)

Question

I am trying to restore a database in my sql server 2005 express edition. I know that to restore the database I need to make it to single user. I am giving this command to make it to single user

USE [master]
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

This command executed properly and I can even see a small image in the object explorer on this database showing that this is now single user.

Now I am trying to restore the database, by following these steps ->right click on the database and tasks and then to restore database. I am selecting the path where the backup file is located and clicking on restore.

But I still get that error "Exclusive access could not be obtained because database is in use(microsoft.sqlserver.smo). Am I missing anything. I have googled it and all most all the sites suggest that database needs to be in single user mode and nothing else.

I did not try the detach and attaching of database method. I have never done that before and would like to know if that is safe to do.

edit: thanks for the answers. Both suggested me same answer so I am marking one answer as chosen.

I even selected overwrite the existing database from options.

Answer 1

First, it's best to back up and restore rather than detach and attach.

Second, it's most likely that the session you're using to set the database to SINGLE_USER is the one that still has it when you try to run the restore (since you're using the GUI, it's connecting under its own session so it's unable to get access).

Either do the restore as a text command or switch the query window to use another database first, such as master. Or you could just close the query window so it's no longer connected.

You can always check who's connected with select * from master.dbo.sysprocesses.

Update

Assuming the database you want to restore already exists, and if you have a single backup file on disk (that doesn't have multiple backups in it) and there's no need to restore log files after restoring the full backup, then restoring via script is super, super easy:

RESTORE DATABASE DBName FROM DISK = 'C:\path\DBNameBackup.bak';

Learning this syntax will make your life easier because then when you set SINGLE_USER you're already in the sole session that is connected. Plus, I find that typing the restore command is faster than using the GUI, and I like the control I have over things. Repetition of this eventually cements it in your mind and you don't have to look up the syntax any more.

It's not even that difficult to restore log files. Just one simple thing to remember, WITH NORECOVERY:

RESTORE DATABASE DBName FROM DISK = 'C:\path\DBNameBackup.bak' WITH NORECOVERY;
RESTORE LOG DBName FROM DISK = 'C:\path\DBNameBackup1.log' WITH NORECOVERY;
RESTORE LOG DBName FROM DISK = 'C:\path\DBNameBackup2.log' WITH NORECOVERY;
RESTORE LOG DBName FROM DISK = 'C:\path\DBNameBackup3.log' WITH NORECOVERY;
... 4 5 6 7 and so on
RESTORE LOG DBName FROM DISK = 'C:\path\DBNameBackupX.log' WITH RECOVERY;

There... you've restored your log files, very easily. You can even restore to an exact point in time using WITH STOPAT! Also, if you forget and accidentally submit the last log restore statement WITH NORECOVERY then you just issue RESTORE DATABASE DBName WITH RECOVERY; to perform the final steps to make the database usable (rolling back uncommitted transactions, etc.).