Daily refresh of production server into lab?

[kevinnaughton]

Assuming this has been covered by didn't find in a search. We want to create a copy of one of our production SQL servers and refresh that copy nightly from the backed up data on the production server and have it live. In other words, use the backup of our prod server to restore onto a secondary server automatically nightly and make that secondary server available for use. Any suggestions for simplest way to accomplish this? We can live with some overnight downtime of the secondary server. We just want a fresh copy of the production server from a single point in time the night before to be available during the next work day.

- Script a deletion of the secondary server, and restore to a new vm of the backup nightly?
- Script a restore of SQL databases only from backup to the secondary server?
- Simplest seems like it would be to replicate to the secondary from the backup of production, but how do we make that live each day without affecting production?

Any suggestions welcome. Thank you!

[foggy]

Haven't you considered SureReplica for that purpose?

[kevinnaughton]

Foggy,

Thanks for the suggestion. However, we need this to come up on our live network, not on an isolated lab network. And it would need to come up with a different name than the production server obviously so as not to be a duplicate name on the network. We basically just need a read only copy of the SQL databases from a single point in time the prior day. We've also considered SQL Always-On, but since we don't need live data only daily updates, the Enterprise licensing to set that up is overkill. And log shipping and restore to the secondary server would work, but be complicated to setup and maintain. Was hoping for a simple solution via Veeam since we're already using that for daily backup and offsite replication of the server.

[Naim]

You could do the replication and then re-IP it. You'd have a different VM name but the actual Windows name (and SID) would be the same.

Having it available on the production network... You could still use Virtual Labs and have it in an isolated network with a masqueraded IP (similar to NAT) . Not sure if that suits your case though.