3

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?

Improve this question
4
  • What is "ages"? Backup and restore seems fine here.
    – Jacob H
    Commented Jun 28, 2018 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
    Commented Jun 28, 2018 at 11:48
  • you can do pull subscription type of replication.
    – Edgar Allan Bayron
    Commented Jun 28, 2018 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.
    – Patrick Collins
    Commented Jun 28, 2018 at 14:01

4 Answers 4

Reset to default
3

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.

Improve this answer
2

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. :-)

Improve this answer
7
  • Is it even possible to get a recovered database back into in NORECOVERY state even if no changes were applied ?
    – Błażej Ciesielski
    Commented Jun 28, 2018 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.)
    – Tibor Karaszi
    Commented Jun 28, 2018 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.
    – Aaron Bertrand
    Commented Jun 28, 2018 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?
    – Patrick Collins
    Commented Jun 28, 2018 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
    – Błażej Ciesielski
    Commented Jun 28, 2018 at 13:24
0

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.

Improve this answer
2
  • 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.
    – Patrick Collins
    Commented Jun 28, 2018 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.
    – Błażej Ciesielski
    Commented Jun 28, 2018 at 15:09
0

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.

Improve this answer

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.