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