My company is releasing new mission critical software. For thorough QA, we are doing database comparisons.

Our process is:

  1. Restore full backup.
  2. Test code.
  3. Compare.
  4. Repeat.

This database is relatively large, and step 1 can take up to 2 hours.

Is there any method someone could suggest that would speed this up? We are making minimal changes when the new code is being run and compared. I'd like to quickly revert back to before the test were run.

Thanks in advance!

  • So you restore a copy of production, make changes to the copy, test the changes, then you want to make the copy look just like production again? There are tools for this. – Aaron Bertrand Oct 5 '14 at 16:59
  • Aaron - The only way to do achieve this is with 3rd party tools? If we only have SSMS, are we stuck to doing complete 2 hour restores after every test? – tcshain Oct 5 '14 at 18:41
  • That's not what I said. But SSMS doesn't have native compare like some of those tools. You can script objects but not as a diff. SSDT does, but I don't think it does as good a job as some of the other tools available. YMMV. You are certainly not limited to backup/restore, but you can invest in a tool (license and/or learning curve) or the time to write your own (and this isn't free - please read the entire post I linked). – Aaron Bertrand Oct 5 '14 at 21:02
  • Try with adeptsql.com, there is a trial version. Since you wrote that you have a minimal changes, you can set it to look for one or more tables and therefore speed-up the process. – Kenan Zahirovic Oct 5 '14 at 21:51
  • @tcshain - curious to know whether you solved this issue? – David Atkinson Aug 31 '15 at 16:30

I believe you could use database snapshots. Though snapshot can not replace full database backup, since it contains only amended pages from the moment the snapshot was created.

Essentially, you restore the production database onto the development server, take a snapshot, perform whatever test work you need to. When you need to return to the original state of the database, revert the snapshot.

Here is MSDN articles that describes process of recovery to the state before a snapshot was taken.


  • 1
    Good answer. For people finding this later, I want to draw attention to the Limitations and Restrictions section in that article -- the database as restored from a snapshot may not be restored to exactly the state one might expect (in particular, the behaviour around fulltext indexes). Snapshots are a great starting point for this type of solution, though. – Jon Seigel Oct 6 '14 at 13:48
  • I agree this is the best native solution but I think it has two issues. 1 the test code would not be able to do any updates as snapshots are read-only and 2 when reverting the snapshot the QA database would be behind production which might then mean another full restore from production would be needed to work on up-to-date data. These may not be requirements but if they were the only solution I can think of is something like the Delphix product. – James Anderson Oct 8 '14 at 8:45

This is a becoming a big requirement in todays environments and I don't see any easy solution other than using a 3rd party tool.

I'm no expert but I was looking at Delphix site the other day and it sounds very interesting. http://www.delphix.com/products/how-virtual-data-machines-work/

It solves the problem with the use of virtualisation and versioning of the VMs that hold the data. Just a shame it has the overhead of virtualisation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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