From MS SQL Backup to RG Log Shipping, what can I keep?

Rob B

Hi Guys,

At the moment I'm running a Full backup every night at 00.00, Diffs every 4 hours and Trans every 15 mins using a plain old Maintenance plans.

The full backup plan starts with an Index rebuild, followed by a Full Backup and finishing with a Cleanup Task and History Cleanup.

Both the Diff and Trans plans are just the backups and Cleanup tasks.

I'm looking to move to a Log Shipping solution and interested to find out what I can and can't keep of the above running at the same time.

A few weeks ago I found some details on the website, or possibly the forums stating that RG Backup's do not break the MS backup chain, and they can be run alongside each other. I now can't find those notes and don't know if I just imagined it!

The Log Shipping solution is for a DR site, and having some local short term backups is still a requirement, although their frequency could be changed. Can I continue to take backups of various types while the Log Shipping runs, and if so can I use RedGate to take them?

Any help or suggestions on what I can keep of my original plan would be very useful.

Thanks
Rob

Eddie D

Thank you for your post into the forum.

From the scenario you have described, you will be able to continue with the Daily Full Backup and the 4 Hourly Differentials.

Transaction Log Backups are sequential, so if you performed log backups using both native SQL Server Backups and Red Gate SQL Backups you will break the log sequence chain. You can continue to take native SQL Server Log backups with you include the COPY_ONLY keyword in the backup task syntax. This action will not break the log backup chain.

Also you stated you perform a Index rebuild. As part of this Index rebuild, do you change the Recovery Model of the database, for example from FULL recovery to SIMPLE recovery, perform the rebuild then change the recovery model back from SIMPLE recovery to FULL recovery? If the answer is yes, this action will also break the log ship chain. Consider modifying the process to switch the recovery model between FULL and BULK_LOGGED, you will then not break the log chain sequence.

I hope the above answers your question.

Many Thanks
Eddie

Rob B

Thank you very much for the response. Could I ask for some small clarification on one point?

You can continue to take native SQL Server Log backups with you include the COPY_ONLY keyword in the backup task syntax

Would this be needed on the Full and Differentials syntax, or was this wording in relation to keeping the Transactional running on the Native SQL Instance maintenance plans?

The index rebuild is done without changing the recovery model, so this should not be an issue. Thank you for clarifying and pointing out the difference.

Many thanks for the help
Rob

Eddie D

Thank you for your reply.

You will only need to add the COPY_ONLY keyword on the Transaction Log backups created using native SQL Server backup in your maintenance plan. Performing Full and Differential backups does not affect the log backup LSN sequence in a log shipping configuration, so the COPY_ONLY keyword is not required for these two backup types.

I forgot to add in my previous post, SQL Backup has a Maintenance Plan Conversion Wizard. So if you wanted to move across and perform all your backup tasks using SQL Backup, the wizard will help update your existing maintenance plans to use SQL Backup.

Many Thanks
Eddie