RichardB (4/9/2009)
GermanDBA (4/8/2009)
you could either backup the database, run the test and then restore the backup or take a snapshot of the database and restore that after the unit test.Only issue being that it's a 1.5TB database... so around a day to restore. :w00t:
Ahaa.... two solutions for that.
1. Either use the Database Snapshot and restore from that: really fast, because only the changes are stored in a sparse file so these would be 'rolled back' instead of restoring the entire 1.5TB
A quick explanation with example can be found here : http://blogs.technet.com/mscom/archive/2007/08/08/using-sql-2005-snapshots-as-a-rollback-procedure.aspx, otherwise BOL / Google.
2. Use SQLBackup from Redgate, we use it and it is screaming fast compared to native backup and restore, with the added benefit of compressing the backups to save disk space.
@1 - Database snapshots are for Enterprise Edition only. However, we are talking about unit tests, so you can do your unit tests on SQL Server Developer Edition (same feature set as Enterprise, but for development use only and available for $50 or so IIRC).
regards
GermanDBA
Regards,
WilliamD