I have a production database on SQL Server. I want a copy of that database to use for running tests. I'd like the test copy to be updated nightly, so that I always have access to fresh data every morning.

The simple solution would be a full backup and restore each night, and then run the latest migration scripts on test. However, the production database is huge (300Gb+), we have to copy across a not-so-fast link, and the whole operation takes longer than one night.

What I would like to do is perform one full backup-restore and then use differential backups each night. Would it be possible to rollback all changes made on test during the day, then restore the latest diff from prod, then run the latest migration scripts? If so, how should I do this?

  • What is "ages"? Backup and restore seems fine here.
    – Jacob H
    Jun 28 '18 at 11:38
  • You cannot use "Diff Shipping" as you describe because you would have to Recover the database to make changes during the day, and then you can no longer apply the next DIFF.
    – Kevin3NF
    Jun 28 '18 at 11:48
  • you can do pull subscription type of replication. Jun 28 '18 at 12:32
  • @ScottHodgin Thanks for providing a clear answer. I would accept your answer, expect you replied in the comments. Now it's time for me to go back to the drawing board. Jun 28 '18 at 14:01

The answer to your question is NO. Even rolling back all changes to the test database, you are still stuck with the fact that you cannot restore a differential backup to a database that is already recovered. You will always have to start over by restoring the latest FULL backup, then restore the latest DIFF that belongs with the latest FULL. Even if you save a copy of a 'FULL' backup on the test server (as Aaron points out in step1), you will eventually have to copy over a newer FULL backup to allow you to restore DIFFS that belong with that FULL.


If it runs overnight, until you get into the 12 hour range, who cares how long it takes? As Kevin says, the first diff will work, but the second will fail because you've used your migration scripts to alter the database.

Surely you are already taking full backups of your primary database? You don't need a full backup every night, all you need to do is:

  1. Keep a copy of your latest backup (say, Saturday night). You should be keeping this anyway. Let's call this B1.
  2. Take diffs nightly. Call these Diff1, Diff2, ...
  3. On Sunday night, restore B1 with replace, then Diff1, then apply your migration scripts.
  4. On Monday night, restore B1 with replace, then Diff2, then apply your migration scripts.
  5. ...
  6. The following Sunday, start over, using B2.

If you're not already taking and keeping full backups as part of a sane recovery plan, this should be a wake-up call. :-)

  • Is it even possible to get a recovered database back into in NORECOVERY state even if no changes were applied ? Jun 28 '18 at 12:27
  • Nope. Going into RECOVERY is a one way street (except for doing one more restore, of course). UNDO has been performed, no way to undo that UNDO. (STANDBY saves information about the UNDO it does into the file you specify, so a subsequent restore can undo that UNDO - but it of course only allow read operations in the database.) Jun 28 '18 at 12:38
  • @BłażejCiesielski Why do you need to go back into norecovery after it's been recovered? On day 2 you just replace. Jun 28 '18 at 12:45
  • This is a serious mission-critical system with backups and redundancy and all that. What I'm asking about is how I can get a fresh daily copy of up-to-date data on a secondary test server. I've updated the question to be more specific about how long this takes. What I am really looking for is an answer to my original question: Would it be possible to rollback all changes made on test during the day, then restore the latest diff from prod, then run the latest migration scripts? Jun 28 '18 at 12:53
  • @PatrickCollins I believe you might be out of luck with this approach, even if you use database snapshot to rollback changes to the state just after you restored the database, then I don't think there is an easy way of putting the database back into NORECOVERY state so that you could apply further diffs Jun 28 '18 at 13:24

It depends what operation takes the most time.

If you only need some of the changes to be applied frequently, a better solution might be to use synchronization software like DbForge Data Studio to sync only the necessary data (e.g. you can sync transactions and leave out logs, addresses, contractors and other data that is not so relevant to the tests).

You can combine this to have a mixed solution - e.g. small syncs during week, and full backup-restore during weekend.

If transferring the backup over the network is the longest task here, then you might want to stick with differential backups as much as possible(as much as it is in pair with the production backup plan).

If the data is not really you are after (and might also cause some GDPR issues in some cases), then you could also have the database and objects scripted from the production database and prepare some scripts that would fill it with test data - this could also bring some advantage for automated testing scenarios as you would have constant scenarios in the test database.

  • I want a copy of the production data on a test server. I want to have exactly the same data, as up to date as possible, so that I can run my tests. The data isn't leaving the organization, just moving to a secondary test server. Syncing with a third-party tool during the week might do the trick. Jun 28 '18 at 12:59
  • Syncing partial data can give benefits, but if you intend to sync the whole database then backup-restore will be more efficient as third-party tools do have to compare row by row every record in the database and it may take even longer then backup-restore. Jun 28 '18 at 15:09

I would suggest you to go for log shipping. Set up the log shipping for production database with target initialize from a full backup.Then schedule the backup job LS Backup from the primary to run at night and schedule restore also accordingly.

You can put the target test db in STANDBY mode so that you will have read access to the data.

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.