SQL Server database snapshots for integration testing

Question

I am trying to define a way of working with a test database (in SQL Server) for our integration testing.

My idea was to do these steps at the launch of the integration test assembly:

• create a totally empty database
• run the "create database objects" script to create all relevant database objects (table, views, sequences etc.)
• fill the "base data" (lookup values etc.)
• take a database snapshot called (db)_Basis as the "base line" for future integration tests

Now before every test class (containing 1-n tests), I was planning to simply do a "restore from snapshot" to get back to the well-defined, more or less "empty" state of the database. Works like a charm so far.

However, there are a set of integration tests that need to operate on a large test database - so I was hoping to do this before each of those test fixtures (classes with n individual tests)

• restore database from the (db)_Basis snapshot
• insert those 50'000+ rows of data into the database
• create another snapshot (db)_With_Testdata snapshot

and then for each test, reset the database to the well-defined (db)_With_Testdata snapshot version, run the tests, verify the outcome and so forth.

Trouble is: I cannot seem to have two db snapshots at the same time - once I do, I cannot restore my database to either of them.... I keep getting this error:

Msg 3137, Level 16, State 4, Line 9
Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.

Is that really how SQL Server database snapshots work?? Seems awfully restricting..... I would understand if I couldn't go back directly to the original "(db)_Basis" snapshot maybe - but just because I now have two snapshots, I cannot even go back to the most recent one?!?!?

Answer 1

Unfortunately, it's by design.

Taken from BOL page "Revert a Database to a Database Snapshot":

Limitations and Restrictions

Reverting is unsupported under the following conditions:

• The database must currently have only one database snapshot, to which you plan to revert.
• Any read-only or compressed filegroups exist in the database.
• Any files are now offline but were online when the snapshot was created

As an alternative, you could drop the first snapshot (db)_Basis. I can understand that this seems very limiting but look at it this way: snapshots are sparse files based on the original data files, so reverting to a specific snapshot would invalidate all snapshots anyway (the base data files would be changed by the revert operation). The limitation can be annoying, but doesn't look unreasonable.

Answer 2

Another perspective will be to take backups and restore them - since you are just creating empty database and schema with some lookup values.

Also, just inserting 50K rows, the database wont be that big. If you use compression the backup size will be less as well.

You can have a TSQL Agent jobs or just scripts (may be you can create stored procedure and just call it after your tests based on the output you get).

• Base backup - (db)_Basis
• With testdata backup - (db)_With_Testdata

have your integration scripts run and based on the final output, you can run either of the above jobs to revert to what ever point you want.

I feel that backup/restore method is much elegant in your scenario since you are hitting database snapshot limitation. Also, Paul Randal blogged about a nasty bug in all versions up to and including SQL Server 2012 (not sure if it is fixed in a later CU)

When you revert to the database snapshot, the transaction log file of the source database is ripped out and replace with a 0.5MB log file with two 0.25MB VLFs.