Post reply

Log Shipping - Switch to Simple

  • November 30, 2011 at 11:31 pm

    #251375

    Hi Guys

    I have log shipping enabled and running without any issues

    If I change the recovery model of the primary database to Simple, I know that this breaks the log shipping chain and log shipping will need to be restarted by taking a full backup and restoring it to the secondary database, leaving the secondary DB in standby

    My question is: at which point can I re-enable the "logbackup" job on the primary database, can log backups start running while the full backup is running?

    Thanks

  • November 30, 2011 at 11:38 pm

    #1415035

    Uh, why do you want to break the log chain?

    There aren't really any good reasons to do so.

  • November 30, 2011 at 11:41 pm

    #1415037

    SpringTownDBA (11/30/2011)

    Uh, why do you want to break the log chain?

    There aren't really any good reasons to do so.

    I'm doing this on a test basis - I want to know that if the log chain does break at some point - What turnaround time I am looking at to get the DB's in sync again.

  • December 1, 2011 at 12:02 am

    #1415041

    Fair enough.

    Well, you're still going to have to wait till the full is done restoring before you can start applying logs, so even if you could restart the log backups before the full is done, i don't think it would buy you anything.

    A differential backup can restart your log chain as well, so the quickest way is probably to immediately start restoring your most recent full, take a differential and restore it, then restart the log backups.

  • December 1, 2011 at 12:04 am

    #1415042

    Hi,

    If you break the log chain, you cannot re-enable log shipping between primary and secondary databases.

    If the full backup is running, log backup wont happen.

    Thanks

    Sam

  • December 1, 2011 at 12:06 am

    #1415044

    derekr 43208 (11/30/2011)

    SpringTownDBA (11/30/2011)

    Uh, why do you want to break the log chain?

    There aren't really any good reasons to do so.

    I'm doing this on a test basis - I want to know that if the log chain does break at some point - What turnaround time I am looking at to get the DB's in sync again.

    Ok

    So my biggest time consumption would be on taking the full backup and restoring

    I just wanted to check whether log backups can run while a full backup is running or if I need to wait until the backup is 100% complete.

    Thanks

    Derek

  • December 1, 2011 at 12:07 am

    #1415045

    pandisam66 (12/1/2011)

    Hi,

    If you break the log chain, you cannot re-enable log shipping between primary and secondary databases.

    If the full backup is running, log backup wont happen.

    Thanks

    Sam

    I know that to re-enable log shipping between the DB's requires a full restore to the seconday DB - I just wanted to check the process of getting it back in sync

  • December 1, 2011 at 12:19 am

    #1415048

    Well, you don't have to take a new full as the most recent full (from before the break in the chain) + a differential backup taken after switching back to full recovery works just fine.

    You're always going to have to eat the cost of restoring a full backup, or a full+differential, but you can start those restores a lot sooner if you only have to take a diff on the primary.

  • December 1, 2011 at 12:22 am

    #1415053

    Hi,

    If you dont have full backup, you should wait 100% completion of Database full backup. Then only you can take Transactional log backup.

    Thanks

    Sam

  • December 1, 2011 at 12:23 am

    #1415054

    SpringTownDBA (12/1/2011)

    Well, you don't have to take a new full as the most recent full (from before the break in the chain) + a differential backup taken after switching back to full recovery works just fine.

    You're always going to have to eat the cost of restoring a full backup, or a full+differential, but you can start those restores a lot sooner if you only have to take a diff on the primary.

    Ok

    What happens in the case where, all the work done on the primary while in simple needs to be reflected on the secondary.

    Is a full backup then required?

  • December 1, 2011 at 12:24 am

    #1415055

    pandisam66 (12/1/2011)

    Hi,

    If you dont have full backup, you should wait 100% completion of Database full backup. Then only you can take Transactional log backup.

    Thanks

    Sam

    Ok, got it

  • December 1, 2011 at 2:14 am

    #1415081

    i think the best thing to do would be to set logshipping up in a test environment which mirrors live.

    leave the databases in full recovery, and do your log backups like normal.

    then at a random time, stop the secondary servers agent to stop copy and restore jobs.

    delete a couple of the log backups on the primary which have not been copied over to the secondary.

    restart your secondary agent, let it copy and restore, and it should fail due to the LSN's being out of sync.

    that way you can still take a full backup, and do log backups at the same time as you have not changed the recovery model of the database.

    Changing from FULL to SIMPLE requires a full backup to make it happen, and so does SIMPLE to FULL, so you would have to do two full backups to do the "chain breaking" in your setup, which in a production environment, you wont be doing as your recovery model shouldnt of changed.

    This way you have a true representation of how long it would actually take to recover should anything like that happen in prod.

    As the you dont have the full log chain you will then have to backup the DB, restore it, then do your logs in order, but atleast you dont have to do two full backups to get to that point, just the one.

  • December 1, 2011 at 3:43 am

    #1415115

    begining 2005, log backups can run simultaneously with full backups. Full backups do not block log backups.

    When you change the recovery model from simple to full (for renabling log shipping), you will need to run full backup once. Unless you take a full backup, your db still behaves in psedo-simple recovery model till a full backup is taken (even if it's in full recovery mode).


    Pradeep Singh

  • December 1, 2011 at 4:32 am

    #1415151

    Thanks All

  • December 1, 2011 at 9:37 am

    #1415369

    ps. (12/1/2011)

    begining 2005, log backups can run simultaneously with full backups. Full backups do not block log backups.

    When you change the recovery model from simple to full (for renabling log shipping), you will need to run full backup once. Unless you take a full backup, your db still behaves in psedo-simple recovery model till a full backup is taken (even if it's in full recovery mode).

    This is a myth. A Full backup is not the only way to restart the log chain.

    A differential backup will restart the chain.

    Paul Randal covered this:

    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2030)-restarting-a-log-backup-chain-requires-a-full-backup.aspx

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply