I'm doing system testing of a product, and several tests are severely changing the data of a demo database.

The data is quite complex to create queries which will undo the changes done by the tests; moreover, those tests are subject to change.

Using a backup/restore is not an option, since restoring the database takes 30 seconds, which is too long in a context of automated testing.

Using insert into select is complex too: again, tests are subject to change, and can affect tables they don't affect now; copying the whole database this way is not an option neither, for performance reasons.

Microsoft SQL Server has snapshots feature, but given that I don't have Enterprise version on localhost, I can't use it. I can't use transactions neither, since the system tests involve two applications with a complex interaction between them. Of course, they don't share the same connection.

What can be done to quickly undo the last changes of a database?

To make the problem easier,

  1. The database is in a controlled environment:

    • It is on localhost, so I am the only one who can access it,

    • The only changes are from system tests. All the changes can (and are expected to) be lost.

    • Any recovery model can be chosen.

  2. The preparation (for example some sort of backup) can take long time or even be a manual task, since it would be performed very rarely. It's only the speed of undoing the changes from this point which is important, since it's done after every test which affects the database.

  • can't you do all the testing sql in one big transaction and then roll it back? Jul 8, 2013 at 19:36

3 Answers 3


If this is just for development/testing, you can get a license for Developer Edition (free for 2014+ and ~$50 for 2012 and lower). and use snapshots, since Developer Edition supports all of the features supported in Enterprise Edition. The only difference is licensing - you cannot use Developer Edition in production.

You're not going to be able to cook something up that's much simpler or faster than reverting from a database snapshot. But do some research - you want to be aware of things like this.


I'd recommend looking into DB Snapshots. They are (in theory) the fastest way to undo changes unless you're wrapping each change in its own transaction with a rollback at the end (after you've written the results of the test to a database).

If you're willing to work it into the framework of your testing, encapsulating each individual test in a rollback block might work out, as that would undo every change made to the environment after every test. This would make it more difficult to combine automated tests that rely on each other though.

  • 2
    A large encapsulating transaction might not work so well, since if any of the code performs its own transactions, and there are no such thing as autonomous / nested transactions, and the testing may be done by more than one session... Jul 8, 2013 at 16:09
  • Then give every session it's own copy of the database. Otherwise hell awaits you anyway once the first tests overlap.
    – TomTom
    Jul 8, 2013 at 16:13
  • That's a good point, considering that tests could be really complex. We use a "cleanup" function that is called as the post action of any given test, which goes through and restores to a db snapshot. It's really the cleanest way to do it given our specific challenges.
    – Sean Long
    Jul 8, 2013 at 16:17

I'd divide the tests in "themed" sections and each section would get their own transaction, which is rolled back to allow the next section of tests runs on the controlled data.

The tests itself would have no transaction control - as this allows to create more testing scenarios where selected tests are performed in a separate section with it's own transaction.

You could setup an DB SnapShot if some of your sections modifies too much data. And get that Developer Edition, the time you save is more than worth the price of it. ;-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.