Is there a simple way reset a MSSQL 2005 database to a predefined state during debugging?

Question

I often find myself in the following situation:

1. The customer reports corrupt data in his database.
2. We investige the problem, sometimes by try and error.
3. We try to solve the problem by using our application or one or two repair tools we ship with our product. (Only in bad cases we write a repair script for individually addressing a specific problem)
4. We send a step-by-step procedure to the customer or to customer service to repair the problem.

All steps (except the first) lead to changes of the customer's database. But it is vital, especially in step 4, that everything works with the original database.

Currently we ensure that by working only with copies of it and before every test we switch back to a fresh copy. That becomes really annoying when there are one-time processes involved. (Things that can only be done once, like creating a specific invoice, processing a specific delivery, ...)

Of course that is a very slow process, especially with large databases. I've tried the backup feature, but that seems to be even slower than just copying the .mdf file.

Is there any way to quickly revert any changes made after a predefined checkpoint?

Answer 1

Take a look at "Snapshots" as they might be what you're after:

http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/ http://msdn.microsoft.com/en-us/library/ms175876.aspx

The only other option, really, is to take copies of the specific tables you're manipulating and then restore those (copy original table by SELECT INTO, manipulate, drop old, rename copy).