0

I am in the process of migrating a server to AWS. Right now it is just for Dev/QA work, but will eventually be turned into our production server. It is a 2 node AG. Server is all up and running, but our data is over 2 months old, and our team would like to periodically (every 5 days) get new data into the DB from the existing Prod DB, and I have been asked to use log shipping to do so. The database needs to be usable, so keeping log shipping intact isn't an option since it will be read only or no recovery. We need to test how it works for eventually populating the nodes for go live, so 2 birds with one stone so to speak. The DB is 1.2TB, so transferring it over the NW to do a backup and restore normally is a bit time consuming. Would the below work, or does anyone have a better way to do this?

  1. remove the DB from the AG to allow it to be added as a secondary LS destination(i have not found info that an AG primary can be used as a LS secondary.)
  2. Restore the most recent backup of prod DB to the destination server, leaving it in restoring mode.
  3. Add the DB as a secondary and let the LS copy and restore jobs bring it up to sync. choose the no recovery option on the DB.
  4. once the DB is synced, disable the restore job and bring the database online using restore DB with recovery command.
  5. add DB back to the AG so they can test everything for the next 5 days.

So five days later, I would need to copy over a new backup since we only keep 7 days of log backups in order to have all the log backups needed to get new data. Is there a way to disable the log backup cleanup for a specific secondary? I don't want to bloat the live prod server with weeks log log backups...

Would this process work, or does anyone have a different way to do this?

0

Once you bring the log shipped database online you would have to restore a new full backup to restart log shipping, regardless of how long it has been. This is because you've recovered the database and cannot then restore log backups to that database.

There is no way to incrementally refresh the Dev/QA database from production with performing a full backup restore, except possibly there are third-party tools that can do this for you. You're better off working on a solution to automate the refresh of your Dev/QA environment and have this process run outside normal operating hours to reduce the impact on your team.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.