The textbooks basically suggest the following alternatives for restoring databases to their original state after individual automated test runs:
(1) Let each test insert its own testdata on initialization, and when done, revert those changes, as well as everything the test did update.
(2) Embed the whole test run in a transaction, and rollback the transaction at the end.
(3) Restore the database from its original state before each test.
Unfortunately, none of those options worked for us. (1) is not practicable on complex database operations. (2) is not possible if there is no access to the database connection / transaction applied by the tested component. Plus we us a different data access layer for verifying test results (as the same data access layer could camouflage errors), so there is no way to work within the same transaction scope. Option (3) - when taken conventionally (applying traditional backup/restore) - was just too slow; because of several reasons, that very test database is about 150MB of size, and we have over 2000 automated tests to run.
What helped me out was SqlServer's wonderful Database Snapshot feature (available in Developer and Enterprise Editions). Snapshots are readonly-views of the database at a certain point in time. Their copy-on-write approach allows for much faster database restore, as the database now keeps track of which data pages have really changed.
So what I basically did was to create a snapshot at the beginning, then run the test on the original database, and then restore that database from the snapshot. Lightning fast!
