SQL Server database snapshots for integration testing

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?!?!?

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.

StackExchange Database Administrators Q#141527, answer score: 12