What are the challenges you face when working across database platforms? Take the survey

Log shipping and "regular" backups

jwilleyjwilley Posts: 23
edited October 3, 2008 1:11AM in SQL Backup Previous Versions
We just set up log shipping on a test database between two data centers on opposite sides of the US and it works great (thanks Red Gate!). When we add log shipping to our production databases, I'm concerned about the interaction between it and our 'normal' backup processes. Currently, we use red gate backup to do weekly Fulls, daily Diffs and hourly TLog backups. Am I right that we'll need to stop these normal backups when we install log shipping due to the various backups releasing inactive TLog records while others still need them? Can somone recommend a way to continue to make normal backups and also use log shipping?

many thanks -- john


  • Options
    peteypetey Posts: 2,358 New member
    With regards to log shipping, you only need to ensure that your normal database backup operations do not:
    - truncate the log without backing it up i.e. using the TRUNCATE_ONLY option
    - back up the tranction log, otherwise you will need to apply these backups to the secondary database too

    With regards to full and differential backups, they have no effect on the transaction log restore chain, so you can perform as many of these types of backups as you require.

    With regards to differential backups, you need to know that they will require the last full backup as the baseline in order to be restored. If you still perform full and differential backups using a mix of SQL Server and SQL Backup, you'll need to check the msdb..backupset table details to determine which full database backups are required for each differential backup that you may want to restore.

    With regards to other database maintenance operations that may be running, you'll need to ensure that none of them changes the database's recovery model to simple and back again, as this will break the log restore sequence.

    With regards to the distance between your primary and secondary databases, please note the concerns raised here on the use of the integrated file copying feature. An interval of 1 hour should have no adverse effects (assuming backup and transfer will never exceed 1 hour), but it's something you should take note of if you plan to reduce the interval between your transaction log backups on your primary database for disaster recovery reasons.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.