Posted by Erland Sommarskog on 11/10/07 23:32

(codefragment@googlemail.com) writes:
> We run through a process to debug some problem, we then want to revert
> the database back to the state it was in so we can repeat that process
> to help in debugging. At the moment I can get around this by making a
> note of the tables involved and use deletes, inserts to get the data
> back to how I want it but it would be nice if I could just click
> 'backup' and and then later revert. If it was fast it could be a real
> help.

Indeed BACKUP/RESTORE is the easiest way to do this, although it can
take a bit too long time with big databases.

Database snapshots on SQL 2005 overcomes that problem.

For a single test, it often works with putting the entire test in
BEGIN/ROLLBACK TRANSACTION, but this may not always work. As long as
the test succedes there are no problems, but if there is a rollback
in the code being tested, the rest of test may be run without a rollback.
And if the test includes doing things from a GUI, or using multiple
processes, BEGIN/ROLLBACK is not practical at all.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]