3

As most likely in a lot of places, I find myself in the roll of "DBA" by default rather than by skill.

I currently am setting up new SQL Servers using SQL Server 2016 Standard Edition. I have been able to successfully set up the log shipping agents and schedules to where it took a full back up at start, and now does log shipping every minute, and does a restore on the secondary every 5 minutes. I have verified its working as expected by altering the primary database and, after the 5 minute boundary has passed, I can see the changes in the secondary database.

First question I have is, once the restore on the secondary has been done, can the transaction log files be deleted?

Next question is, if I wanted to combine the transaction log shipping with a full/differential backup plan, is there anything out there that explains better about how to set this up. I have read multiple articles, but each seems to be missing one component or the other and its making it difficult for me to understand how these differential backup components work together.

Improve this question

1 Answer 1

Reset to default
1

once the restore on the secondary has been done, can the trn files be deleted?

Yes you can but you want to consider keeping files for certain period of time depending on your recovery point objective.

if I wanted to combine the transaction log shipping with a full/differential backup plan

Full and differential backup will not break your log chain.

Make sure you do not take any log backup outside log shipping. All log backups must be done through the log shipping. Because that will break your log chain and hence log shipping will fail.

Improve this answer
5
  • I guess the more i think about it, if my log files get shipped every minute and restored into the backup server every 5 minutes, is there really any reason to do a full or differential backup if restoring back to a previous state is not a concern?
    – Keith Clark
    Jun 7, 2017 at 18:20
  • It all depends on your RPO and RTO. More here.
    – SqlWorldWide
    Jun 7, 2017 at 18:33
  • Note that you presumably want to keep at least one copy of transaction logs that are still part of your basic "restore to a point in time backup scheme." Remember that if some thing goes significantly wrong and isn't caught for 15 minutes, then it's wrong on both your servers, and restoring from the latest full/diff/tlog set is your way back.
    – RDFozz
    Jun 7, 2017 at 18:53
  • @RDFozz valid point. Thank you. I did update my answer.
    – SqlWorldWide
    Jun 7, 2017 at 19:17
  • @SqlWorldWide To be clear - it wasn't so much that there was something wrong with the answer (you certainly weren't saying "Delete every copy of your backups once they're copied to the new system") - I just worry about people sometimes taking things too literally without thinking, and thus am always willing to point out the obvious instead of leaving it assumed that the reader will make the correct inference. :-)
    – RDFozz
    Jun 7, 2017 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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