How can I create a test copy of my production database that is updated daily on SQL Server?

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?

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.

StackExchange Database Administrators Q#210834, answer score: 3