4 Replies Latest reply: Mar 11, 2011 4:59 PM by Hrvoje Crvelin RSS

SQL Server differential backups

David Hampson

We have some SQL servers which dump their data up to flat files which then are backed up by Networker.  Microsoft use the term full and differential backups but it is not entirely clear that differential means the same thing as it does in Networker - the definition Microsoft gives implies that these are the same as incremental backups in Networker (everything changed since last backup at any level) but the example restore they give implies that they are the same as differential backups in Networker (restore full then last differential).

Can someone clarify this one for me please?

  • 1. Re: SQL Server differential backups

    Hi David,


    I would say that those terms are universal.


    Incremental is a backup of latest changes since the last backup (any level) so when a full recovery is needed you would need to restore the last full backup plus all the incrementals until the PIT you want to restore.


    Differential bakcups are incremental backups since the last full backup, I mean, the differential backups will always save the differences between the last full. Let me add an example:


    INCREMENTAL backups:


    Sunday      Monday                          Tuesday                        Wednesday                     Thursday    Friday    Saturday     Sunday


    Full            changes since Sunday    changes since Monday   Changes since Tuesday    Changes since Wednesday...........





    Sunday      Monday                           Tuesday                         Wednesday                     Thursday                         Friday                           Sunday


    FULL         Changes since Sunday    Changes since Sunday    Changes since Sunday     Changes since Sunday    Changes since Sunday     FULL


    To restore Incrementals on Friday you will need to restore Sunday plus all incrementals until Friday, to restore on Friday from Differentials you need to restore Sunday plus differential on Friday only.


    It this doesn't apply to "Microsoft's answer" then they should reply and let us know what they mean


    Please let us know if you need any further information.


    Thank you.


  • 2. Re: SQL Server differential backups
    David Hampson

    Thanks Carlos, but those terms aren't universal, for example what Symantec call a differential backup is what EMC call an incremental backup... its not entirely clear from Microsoft's website which side they sit on as there are several statements which are sufficiently vague to leave you unsure as to what they mean!  However this webpage seems to confirm that SQL differentials are the same as Networker differentials:


  • 3. Re: SQL Server differential backups

    Hello David,


    I agree, as per the link you sent I understand that Microsot refers to differential bakcups in the same way we do.


    Please note the difference betweeen the SQL DB model (Simple or Full).


    Apart from that I think is clear now, isn't it? If it is not please let me know which informaiton are you looking for and I'll find it for you.


    Thank you.


  • 4. Re: SQL Server differential backups
    Hrvoje Crvelin

    Hi David,


    Table 1 on page 18 in SQL admin guide explains it.  In short:

    - NW full backup - that's easy

    - NW differential backup is sort of differential backup as per NW

    - NW incremental backup is used for transaction log backups


    I use just only full (1x per day) and incremental backup (1x per hour) in my setup.  Differential in docs is explained as all changes since full which implies as you would use same differential level backup per day (in NW terms).  Obviously, this implies you can't have what traditionally would be incremental backup (backup since last lower level backup).  My limited SQL knowledge remembers that SQL sees backups as complete (full), differential (differential) and transaction (incremental) and MS differential backup is always backup containing all changes since last full.  At least this is the case with SQL 2005 (I'm just about to start playing with SQL 2008, but I doubt MS has introduced support for differential levels here).