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?