Unable to restore database on sql server (single_user)

Question

I am trying to restore a database in my SQL Server 2005 Express Editor. I know that to restore a database I need to do it for one user. I pass this command to make it the only user

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

This command is executed correctly, and I even see a small image in the object explorer in this database, showing that now it is the only user.

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

But I still get this error: "Exceptional access could not be obtained because the database is in use (microsoft.sqlserver.smo). I missed something. I have googled and all the majority of sites indicate that the database should be in single-user mode and nothing more.

I did not try to detach and bind the database method. I have never done this before and would like to know if it is safe to do it.

edit: thanks for the answers. Both offered me the same answer, so I mark one answer as the selected one.

I even chose to overwrite the existing database from the options.

Answer 1

First, it’s best to back up and restore, rather than disconnect and attach.

Secondly, most likely, the session that you use to configure the database on SINGLE_USER is the one that still occurs when you try to start recovery (since you use the graphical interface, it connects to its own session so that it cannot receive access).

Either perform the recovery as a text command, or switch the query window to use another database, such as master, first. Or you can simply close the request window so that it is no longer connected.

You can always check who is associated with select * from master.dbo.sysprocesses .

Update

Assuming that the database that you want to restore already exists, and if there is one backup file on the disk (it does not have several backups), there is no need to restore the log files after restoring a full backup, and then restore via script super Super easy:

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

Learning this syntax will make your life easier, because when you set SINGLE_USER, you are already in the only session that is connected. In addition, I find that typing the restore command is faster than using the GUI, and I like the control I have. Repetition of this ultimately cements it in your mind, and you no longer need to look for syntax.

It is not even that difficult to recover log files. Just one 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 very easily restored your log files. You can even restore the exact point in time using WITH STOPAT ! Also, if you forget and accidentally send the last expression about restoring the WITH NORECOVERY , you simply issue RESTORE DATABASE DBName WITH RECOVERY; to complete the final steps to make the database useful (rollback of pending transactions, etc.).

Answer 2

You can use this script to kill all processes using the database, and then try to restore it again:

 declare @sql as varchar(20), @spid as int select @spid = min(spid) from master..sysprocesses where dbid = db_id('<database_name>') and spid != @@spid while (@spid is not null) begin print 'Killing process ' + cast(@spid as varchar) + ' ...' set @sql = 'kill ' + cast(@spid as varchar) exec (@sql) select @spid = min(spid) from master..sysprocesses where dbid = db_id('<database_name>') and spid != @@spid end print 'Process completed...' 

Answer 3

• Go to "Parameters" only in the "General" section in the list on the left.
• Make sure that the option "Overwrite existing database" is checked (section "Restore settings").

Good luck.