none
Full, Differential and Transaction Log Backups RRS feed

  • Question

  • If I do :

    - a full backup on Monday at 9am

    - a differential backup on Tuesday at 9am

    - a differential backup on Wednesday at 9am

    - a differential backup on Thursday at 9am

    - a differential backup on Friday at 9am and a Transaction Log backup at 9:30am and every 39 mins after that.

    Say my database fails at 10:01am

    Why do I have to restore the Full back from Monday and the Differential backup from Friday and all Trans log backups as opposed to the Full back from Monday and all Trans log backups i.e. why do I need to restore the latest Diff backup also? The full backup has the translog changes and my first trans log backup on Friday at 9:30am has all changes to the trans log since the full backup? So why must I restore the latest Diff backup before restoring all the trans log backups?


    CG

    Wednesday, May 29, 2013 10:12 PM

Answers

  • we can do in two way:
    Method 1
    Restore Full backup from Monday
    Restore Differential backup on Friday at 9am
    Restore Transaction log backup at Friday 9.30am and transaction log after that until disaster happen

    Method 2:
    Restore Full backup from Monday
    Restore All the transaction log after full backup until disaster happen

    First method will be more faster

     if we have transaction log backup every 39 mins which are around 40 transaction log file need to restore per days. More file need to restore compare in method 1 compare to method 2.

    But both will give same result

     you may want refer to here more detail


    Performing a Complete Database Restore (Full Recovery Model)

    http://msdn.microsoft.com/en-us/library/ms187495(v=sql.105).aspx

    Thursday, May 30, 2013 3:34 AM

  • Why do I have to restore the Full back from Monday and the Differential backup from Friday and all Trans log backups as opposed to the Full back from Monday and all Trans log backups i.e. why do I need to restore the latest Diff backup also? The full backup has the translog changes and my first trans log backup on Friday at 9:30am has all changes to the trans log since the full backup? So why must I restore the latest Diff backup before restoring all the trans log backups?


    CG

    Hi 

    Appreciate if you go through below technet article ,this will explain ur query

    http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx

    Diff backup is cumulative and backs all changes that has happened after latest full backup..and if you are aware about Transaction logs, LSN (log sequence no)are used to identify it..so if you miss ur diff backup during restore(subject to condition u r taking diff bkp) LSN will break and recovery will not be possible Applying backup in serialized manner full-Diff-trn helps SQL engine to recover ur database to point in time and perfectly

    If u dont take diff backup

    You can apply trn backup after latest full bkp ..but that will take long time (resrtoring 100's of log) and wll increase ur downtime and ur SLA will go for toss..so the concept of diff backup is there ....


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, May 30, 2013 5:07 AM
    Moderator

All replies

  • we can do in two way:
    Method 1
    Restore Full backup from Monday
    Restore Differential backup on Friday at 9am
    Restore Transaction log backup at Friday 9.30am and transaction log after that until disaster happen

    Method 2:
    Restore Full backup from Monday
    Restore All the transaction log after full backup until disaster happen

    First method will be more faster

     if we have transaction log backup every 39 mins which are around 40 transaction log file need to restore per days. More file need to restore compare in method 1 compare to method 2.

    But both will give same result

     you may want refer to here more detail


    Performing a Complete Database Restore (Full Recovery Model)

    http://msdn.microsoft.com/en-us/library/ms187495(v=sql.105).aspx

    Thursday, May 30, 2013 3:34 AM

  • Why do I have to restore the Full back from Monday and the Differential backup from Friday and all Trans log backups as opposed to the Full back from Monday and all Trans log backups i.e. why do I need to restore the latest Diff backup also? The full backup has the translog changes and my first trans log backup on Friday at 9:30am has all changes to the trans log since the full backup? So why must I restore the latest Diff backup before restoring all the trans log backups?


    CG

    Hi 

    Appreciate if you go through below technet article ,this will explain ur query

    http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx

    Diff backup is cumulative and backs all changes that has happened after latest full backup..and if you are aware about Transaction logs, LSN (log sequence no)are used to identify it..so if you miss ur diff backup during restore(subject to condition u r taking diff bkp) LSN will break and recovery will not be possible Applying backup in serialized manner full-Diff-trn helps SQL engine to recover ur database to point in time and perfectly

    If u dont take diff backup

    You can apply trn backup after latest full bkp ..but that will take long time (resrtoring 100's of log) and wll increase ur downtime and ur SLA will go for toss..so the concept of diff backup is there ....


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, May 30, 2013 5:07 AM
    Moderator
  • Hi Nutech

    Without full backup restoration how would you restore the next backups ( diff  or .trn).

    My intention is  without full backup you can't restore any backups(diff,.trn) .

    Please follow up Soonyu and shanky answers.

    Thursday, May 30, 2013 8:01 AM
  • You don't need to restore the diff backup if you don't want to. In-between full or diff backups to not break or affect the log backup chain.

    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, May 31, 2013 3:16 PM
    Moderator