Shortcut: Using Database Snapshots to Provide Large Unit Testing Data with Quick Restores

SQL Server databases have a reputation for being hard to test, or at least hard to test appropriately.

For good testing, and particularly for unit tests, you really want the following:

• Database in a known state before each test
• Database containing large amounts of (preferably masked) data (production-sized)
• Quick restore after each test before the next test

For most databases, this is hard to achieve. The restore after each test means that a normal database restore can’t be used. What I often see instead, is people using transactions to try to achieve this i.e. the process becomes:

• Start a transaction
• Run the test
• Check the results
• Roll back the transaction

In some situations, that works well but the minute that you start trying to test transactional code, things fall apart quickly. SQL Server doesn’t support truly nested transactions. When you execute a ROLLBACK, it doesn’t matter how deep this occurs, the outer transaction is being rolled back too.

One option that I think many people should consider is database snapshots.

Your testing mechanism becomes:

• Create a snapshot of the database to be tested
• Test against the original database
• Check the results
• Revert the database to the snapshot

Both creating a snapshot database, and restoring from the snapshot are very quick operations. The creation is always quick and the revert time depends upon how many pages were changed during the test. That’s often not many.

Creating a snapshot is described here.

Reverting a database from a snapshot is described here.

(Thanks to Lee Beer for pointing out that the links have now changed so I’ve updated them)

2017-12-14