Restore DB Options 2

A task that you may be faced with as a DBA is the need to refresh a test or development database on a periodic basis.  This may be a scheduled process or it may be an ad hoc process.  One of the things that you need to do when restoring a database is to ensure you have exclusive access to the database otherwise the restore process will not work.

The best approach for doing this would be to use the ALTER DATABASE command to set the database to single user mode along with rolling back any open transactions.  The command looks something like one of the following.

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 AFTERnnn - 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.