14

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

2
  • How long does the Insert 50,000 rows part take? Could you just reapply that instead? Jun 17, 2016 at 20:19
  • we do create database from model, that way you can run multiple tests in parallel
    – amohr
    Aug 12 at 1:54

2 Answers 2

12

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.

4

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.

4
  • Yes, we've been using backup/restore - but that's in the 5-7 seconds range, while restoring from a database snapshot is well below 1 second - that's why we're looking for an alternative to backup/restore
    – marc_s
    Jun 17, 2016 at 15:47
  • @marc_s Well to me a 5-7 sec is acceptable without limitations and bugs vs a 1 sec with limitations and possible bugs :-)
    – Kin Shah
    Jun 17, 2016 at 15:50
  • To us, it is not acceptable - we're looking for a quicker solution
    – marc_s
    Jun 17, 2016 at 15:52
  • @marc_s I see your point as not acceptable. But you already are hitting the limitation thats by design. You can use only 1 snapshot but still need a backup to revert back. Choice is yours - use a single snapshot e.g. with your testdata and use backup to restore to base data.
    – Kin Shah
    Jun 17, 2016 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.