Återställa backup på databas som används av massa andra users

Post date: Sep 7, 2009 7:13:42 AM

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 30OR 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.

Summary

Once the database is in single user mode it is now easy to perform the

restore process. Here is a sample set of code that puts the database

in single user mode and does the restore.

ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE<br><br> RESTORE DATABASE [Test4]<br> FROM DISK = 'c:\test4.BAK'<br>WITH MOVE 'Test4_Data' TO 'c:\data\Test4.mdf',<br> MOVE 'Test4_Log' TO 'c:\data\Test4_log.ldf'