I'm trying to not reinvent the wheel here, and could use some help from those of you that might have run into something like this.
We are running SQL 2008 R2 Standard.
Our company will be moving to a new production server in October. I have been tasked with figuring out a solution for refreshing our development environment from production.
A top level view of what needs to be done:
1. BACKUP databases
2. compress BACKUPs (could be part of Native SQL Backup)
3. FTP compressed BACKUPS to our development environment
4. decompress/RESTORE in the development environment
The production server is available only through a VPN.
At the moment, the total compressed size of all backups is ~70GB, and is expected to grow.
The databases contain sensitive financial information, all of which will be deleted after the RESTORE. I had suggested investigating log shipping, but this won't fly, as they don't want to store the sensitive data on site, as would be the case using WITH STANDBY.
Since there are dependencies, i.e. we don't want to FTP until the BACKUP is completed, and we don't want to RESTORE until the FTP has completed, I think that some form of encapsulation is required.
Perhaps a Powershell solution might be possible, but before going down the road too far, I wanted to see how others have solved this issue.
Thanks in advance --