6

I am creating a series of web automation tests which require test data to be in a database (SQL Server 2008). To generate the required data for each test I have to call some C# code which will insert the correct data into the DB (i.e. I can't just write SQL scripts to insert the data). My problem is I don't want to pollute my test db with lot's of test data from these automated tests. So would like to rollback all of the changes made to the DB during the test.

Can anyone suggest a sensible way of achieving this?

tshepang
  • 12,111
  • 21
  • 91
  • 136
James Hollingworth
  • 14,040
  • 12
  • 39
  • 57

6 Answers6

5

Simple way would be to create a backup of the database before running the tests, and then just restore back at the end.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • Or even do the restore at the end of the tests, so that next time the tests run the clean db is already in place. Hi Tim, hi James! – mcintyre321 Nov 29 '10 at 10:19
  • Jamie, this would be the way I'd go as well. Set the Database to 'Full' Recovery Model. Do a one-off Full backup then at the start of each test run do a differential backup run this should be very quick. At the end of the tests do a 'Recovery to point in time' http://msdn.microsoft.com/en-us/library/ms190982.aspx - use the management studio to show you the scripts needed to automate it. – Joel Mansford Nov 29 '10 at 11:10
4

Two ways of doing this.

One is to enclose your test within a transaction and roll it back. Another is to use a cleanup script as part of your test completion code (we do this for some of our integration tests where a transaction does not work).

Bob Palmer
  • 4,714
  • 2
  • 27
  • 31
  • On a side note - we generally work with a specific reference database for our integration tests, so the system is always in a known good state before our integration tests run (even with rollback data). Makes things a lot easier. – Bob Palmer Nov 28 '10 at 16:30
2

When I don't have control over the transaction scopes of my tests, I usually drop and recreate the database from scratch each time.

Obviously, this is only feasible if the tests can run against the bare schema (or with hardcoded lookup values inserted in the create scripts).

When I test against a snapshot database pre-populated with lots of data, I have before used cleanup scripts, say delete all records from each table above the max id of my baseline snapshot.

Haven't tried automating the backup/rollback as AdaTheDev suggests, but it sounds like probably your best option if you don't want to maintain potentially complicated (and buggy) cleanup scripts (depends on the complexity of your snapshot data / how often you might change your snapshot and have to modify your cleanups accordingly).

Have you considered mocking out the data access, so that your web tests run against an in memory data store? Then test the data access procedures internally where you can still rollback the transaction scopes?

Tim Iles
  • 2,232
  • 2
  • 24
  • 26
2

The wizards over at Red Gate have just released SQL Virtual Restore, which will actually mount a backup file as a live, readable, writable database - so you could have a backup file representing the baseline state of your system before tests, take a copy of this backup, mount the copy as your test database, run the tests, and then unmount and wipe the copy.

Virtual Restore is at http://www.red-gate.com/products/sql_virtual_restore/index.htm and there's 14-day trial if you want to try it out.

I have no affiliation with Red Gate, btw - I'm just an enthusiastic user of their tools.

Dylan Beattie
  • 53,688
  • 35
  • 128
  • 197
  • Not clear for me what's the added value compared to restoring a backup to MyTestDb ? No tools required... – iDevlop Jun 27 '11 at 09:29
1

It sounds like it would be hard to use transactions, since you're going to be making multiple web requests in a single test - but that would be my first preference because it's faster than restoring a database from backup.

If you've got the right version of SQL server, you could use database snapshots instead of backups: http://msdn.microsoft.com/en-us/library/ms175876.aspx, simply because they are faster :)

Rob Fonseca-Ensor
  • 15,510
  • 44
  • 57
0

Obviously it all depends on how you call the tests but would the "Rollback" attribute work that is in MbUnit?

caveman_dick
  • 6,302
  • 3
  • 34
  • 49