locked
Backups and Restore RRS feed

  • Question

  • Hi ,

    We configured Full backups weekly once on sunday and differential bkps Monday to saturday and Log every 1hr designed in sql maintenance plan.

    Last week I configured it was supposed to work from Sunday full bkp followed by remaining backups. I found differential bkps are failing last two days.

    If I reconfigure the sql server maintenance plan and manually execute the differential backups does it work with Full backups(which I already taken last sunday) also after reconfiguring sql backup maintenance plan does it break any log chain with previous log backups.

    Please suggest..Thanks


    DBA

    Tuesday, February 6, 2018 4:50 AM

Answers

  • Only a FULL backup resets the chain for DIFFERENTIALS and TRANSACTION LOGS.


    Martin a FULL backup would never reset transaction log chain. A full backup has no affect on transaction log chain or LSN.

    DBADays,

    A differential backup is valid ONLY with last full backup which started the differential chain. If you take full backup in between and then take diff backup, the new diff backup would now link to the recent full backup you took


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by TiborKMVP Tuesday, February 6, 2018 12:20 PM
    • Marked as answer by dbadays Monday, April 9, 2018 4:15 AM
    Tuesday, February 6, 2018 6:49 AM
  • If I reconfigure the sql server maintenance plan and manually execute the differential backups does it work with Full backups(which I already taken last sunday) 

    -- The Differential backup will work with the latest Full Backup ( i,e the last Full Backup ) in your case its last sunday so yes it will


    also after reconfiguring sql backup maintenance plan does it break any log chain with previous log backups.

    -- It will depend how you configure you Maintenance Plan. Checkout below to find about it

    Breaking of chain can due to any of the below mentioned reasons.

    Reference

    https://social.technet.microsoft.com/wiki/contents/articles/25439.various-causes-of-log-chains-breaking-issue-in-sql-server.aspx

    1. Switching the Recovery modes.
    2.Using the Truncate Only option while taking log backups. The Truncate Only option does not work with SQL versions 2008 and above.
    3.Using the No_Log option to take log backup.   The No_Log option will not work with SQL versions 2008 and above.
    4.Using the Database Snapshot for reverting the database. This is not going to be fixed by the differential backup and so requires a full backup Jump of the database.
    5.To fix this breaking of the chain, you need to take a full or differential backup and then continue taking log backups


    -------------------------------------------------------------------
    Please Mark This As Answer if it solved your issue 
    Please Vote This As Helpful if it helps to solve your issue so that other forum members can benefit from it 

    Yuvraj Singh Bais
    ------------------------------------------------------------------- 

     
    • Marked as answer by dbadays Monday, April 9, 2018 4:16 AM
    Friday, February 16, 2018 8:53 AM

All replies

  • Only a FULL backup resets the chain for DIFFERENTIALS and TRANSACTION LOGS.

    Differentials are defined as all the dirty pages since the last full backup.  A Full backup clears the dirty page map so that it starts from nothing again.

    The transaction log chain is also only bound to the first full backup.  If you only had the first full backup plus each and every transaction log backup since then, you could still restore to a point in time without any of the Differentials.  The Differentials are only valid in your scenario to reduce the number of RESTORE commands to be executed to complete a restore to a point in time.

    I would question you on what your permitted data loss in the event of a disaster is?  Can you afford to lose 1 hours of data?  If not then your plan is not fit for purpose to start with.

    However:

    (1)     You ONLY need your Full backup from the Sunday PLUS all the Transaction Log backups since then to be able to do a restore.

    (2)     You can take extra Differential backups at any time.   To do a restore you will need the FULL from the Sunday, the last Diff that was taken BEFORE the point in time you want to restore to PLUS all the Transaction Log backups since that Differential.  So no, the Differentials do not have any chain associated with them.

    (3) The Transaction Log Chain is related back to the Full backup on the Sunday.   Therefore it is more important that your Full PLUS all of your Transaction Log backups succeed and are available for a restore.


    Martin Cairney SQL Server MVP

    • Proposed as answer by Akash Pawar Tuesday, February 6, 2018 6:21 AM
    Tuesday, February 6, 2018 5:05 AM
  • Only a FULL backup resets the chain for DIFFERENTIALS and TRANSACTION LOGS.


    Martin a FULL backup would never reset transaction log chain. A full backup has no affect on transaction log chain or LSN.

    DBADays,

    A differential backup is valid ONLY with last full backup which started the differential chain. If you take full backup in between and then take diff backup, the new diff backup would now link to the recent full backup you took


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by TiborKMVP Tuesday, February 6, 2018 12:20 PM
    • Marked as answer by dbadays Monday, April 9, 2018 4:15 AM
    Tuesday, February 6, 2018 6:49 AM
  • As I mentioned, "The transaction log chain is also only bound to the first full backup."

    You cannot take transaction log backups until you have taken a full backup.


    Martin Cairney SQL Server MVP

    Tuesday, February 6, 2018 10:56 PM
  • Differential backups performs same operation as full backup. It contains all the data that has changed, or been added since the previous full backup. 

    Transaction log backups contain all the transaction records generated since the last log backup. You do not need differential backup if you have taken full backup and transaction log backup. 

    Go through the following link: 

    https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx

    Friday, February 16, 2018 7:47 AM

  •  You do not need differential backup if you have taken full backup and transaction log backup. 

    This is wrong statement considering we do not know RPO and RTO, you may need differential backup and best use of differential backups is to lower the recovery time object(RTO). Instead of restoring all the log backups taken after full backup most recent diff backup can be restored after full backup and then few log backups taken after diff backup to bring database online in comparatively very less time. So as per RPO and RTO you may need it

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 16, 2018 7:52 AM
  • If you reconfigure SQL Server Maintenance Plan and manually execute the differential backup it will work with your last full backup considering you haven't taken any full backup post the sunday one. 

    Reconfiguring maintenance plan does not break any log chain, log chain will be broken if you ran any backup by any way.

    Mark this as Answer if this is helpful.


    Thanks & Regards Manvendra Deo Singh My Blog:- http://techyaz.com/


    Friday, February 16, 2018 8:38 AM
  • If I reconfigure the sql server maintenance plan and manually execute the differential backups does it work with Full backups(which I already taken last sunday) 

    -- The Differential backup will work with the latest Full Backup ( i,e the last Full Backup ) in your case its last sunday so yes it will


    also after reconfiguring sql backup maintenance plan does it break any log chain with previous log backups.

    -- It will depend how you configure you Maintenance Plan. Checkout below to find about it

    Breaking of chain can due to any of the below mentioned reasons.

    Reference

    https://social.technet.microsoft.com/wiki/contents/articles/25439.various-causes-of-log-chains-breaking-issue-in-sql-server.aspx

    1. Switching the Recovery modes.
    2.Using the Truncate Only option while taking log backups. The Truncate Only option does not work with SQL versions 2008 and above.
    3.Using the No_Log option to take log backup.   The No_Log option will not work with SQL versions 2008 and above.
    4.Using the Database Snapshot for reverting the database. This is not going to be fixed by the differential backup and so requires a full backup Jump of the database.
    5.To fix this breaking of the chain, you need to take a full or differential backup and then continue taking log backups


    -------------------------------------------------------------------
    Please Mark This As Answer if it solved your issue 
    Please Vote This As Helpful if it helps to solve your issue so that other forum members can benefit from it 

    Yuvraj Singh Bais
    ------------------------------------------------------------------- 

     
    • Marked as answer by dbadays Monday, April 9, 2018 4:16 AM
    Friday, February 16, 2018 8:53 AM