For a little twist - I have a requirement to create a reporting copy of the database to be refreshed on a nightly basis. The database must be available to the users by 6am the following morning - with the data as current as we can get.
Our backups are done using Litespeed - so the backup file is already compressed.
The issues here:
1) The database is 4.5TB allocated - with almost 3TB used.
2) Restoring across the network will take too long...
3) Copying backup files across the networs will take too long...
3) Database Mirroring/Log Shipping are not options
a) Mirroring is not an option because we already mirror to DR
b) Log Shipping is not an option because source system is 2005, destination is 2008 R2
Worked with the storage team to create a mirror of our backup drive. The mirrored drive can then be split - so we can present the snapshot of the backup drive to our reporting server. Once presented, we can mount the volume - perform the restores locally - dismount the volume and re-synchronize the mirror set.
Steps included in the agent job:
1) Split the mirror
2) Mount the volume
3) Generate restore scripts (scripts generated from latest available backup and all log backups available up to this time)
4) Execute generated scripts to restore databases
5) Dismount the volume
6) Synchronize the volume
7) Post restore tasks (e.g. add reporting users, fix up orphaned users (if needed), set databases read only, etc...)
This process allows us to restore the copy from live in about 2.5 hours. It also provides us with validation that our backups are good - and allows us the ability to perform an integrity check on the database (granted - not on the current but it is better than not running them).