4

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!

6
  • 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. Oct 5, 2014 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, 2014 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). Oct 5, 2014 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. Oct 5, 2014 at 21:51
  • @tcshain - curious to know whether you solved this issue? Aug 31, 2015 at 16:30

2 Answers 2

3

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.

http://msdn.microsoft.com/en-us/library/ms189281.aspx

2
  • 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, 2014 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. Oct 8, 2014 at 8:45
1

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 and acknowledge you have read our privacy policy.

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