How to undo every change made to a database in a controlled environment?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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. ;-)