Single user mode while restoring database

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.

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.

StackExchange Database Administrators Q#191404, answer score: 9