Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/StackDBAs/status/1019734559959678976
explained what "takes ages" means
Source Link
Patrick Collins
Patrick Collins
  • 33
  • 4

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.

I could doThe 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 thisthe whole operation takes ageslonger 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?

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.

I could do a full backup and restore each night, and then run the latest migration scripts on test. However, the production database is huge (300Gb+) and this takes ages.

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?

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?

Source Link
Patrick Collins
Patrick Collins
  • 33
  • 4

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.

I could do a full backup and restore each night, and then run the latest migration scripts on test. However, the production database is huge (300Gb+) and this takes ages.

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?