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

Can you simply delete existing database and then restore it using the same name?

Answer 2

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:\pathname\MyDBNameBackup.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.

Answer 3

• Go to "Options" item just under "General" on the left hand side list.
• Make sure that "Overwrite the existing database" is checked ("Restore Options" section).

Good luck.