2

Is it necessary to put the database into single user mode before restoring a database? If so which one is more preferable putting the sql server into single user mode or putting the database only into single user mode before restoring ! Thank you.

  • 1
    If you are restoring from SSMS, just close all of the connections (check box on the restore GUI) before restoring. – WEI_DBA Nov 21 '17 at 14:25
6

You would only need to put the SQL Server instances in single user mode if you were restoring the master database. For user databases, you have to make sure there are no active connections to the database you're restoring. You'd either have to determine and kill any active SPID's (which would not require the database to be in single user mode) or actually put the database in single user mode using one of the following (referencing a post by Greg Robidoux Getting exclusive access to restore SQL Server databases:

ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
OR 
ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK AFTER 30 
OR 
ALTER DATABASE [Test4] SET SINGLE_USER WITH NO_WAIT 
  • WITH ROLLBACK IMMEDIATE - this option doesn't wait for transactions to complete it just begins rolling back all open transactions
  • WITH ROLLBACK AFTER nnn - this option will rollback all open transactions after waiting nnn seconds for the open transactions to complete. In our example we are specifying that the process should wait 30 seconds before rolling back any open transactions.
  • WITH NO_WAIT - this option will only set the database to single user mode if all transactions have been completed. It waits for a specified period of time and if the transactions are not complete the process will fail. This is the cleanest approach, because it doesn't rollback any transactions, but it will not always work if there are open transactions.

Once the database has been put in single user mode, you have exclusive access to the database and can then do the restore without a problem.

Note: when using the ROLLBACK option you are rolling back any open transactions that still exist for the database. The rollback process should work without issue, but if you have very long running transactions the rollback process could take a long time, so be aware of what is running on your systems. For test and development systems since you are doing a restore you don't care about the transactions anyway, so rolling things back should not be an issue, but you still need to be aware that long running transactions may take some time to rollback.

2

You cannot restore database if you are connected to this database, you'll get the error:

RESTORE cannot process database 'my_db_name' because it is in use by this session

This means that you should restore database being connected to another database, (recommended) master.

But if you put database in single_user (before restore) from master, there is a possibility that someone connects to that database between your putting it to single_user and restoring.

To avoid this situation just put your database offline and not in single_user:

alter database ... set offline with rollback immediate;
restore database ...;
  • This isn't quite right. When you set a database to SINGLE_USER, only the SPID that set the option is allowed to connect to it until that SPID disconnects. So you can't be caught with some other user connecting to it by accident before you do your restore — it doesn't matter what database you are connected to at the time. – NReilingh Oct 20 '18 at 23:48
  • @NReilingh No, your comment is not correct. It can happen that the user who put the database in single user still cannot access it because another process access this database prior to him. It is also documented: "Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. For more information, see ALTER DATABASE SET Options (Transact-SQL)." – sepupic Oct 22 '18 at 7:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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