locked
Need the difference between Differential and Incremental Backups RRS feed

  • Question

  • Hi all

    I want to know  the difference between Differential and Incremental Backups. Please help meon this

    Wednesday, June 9, 2010 7:25 AM

Answers

  • Hi,

    SQL Server supports (a) full and (b) differential backups: Reference: Backup Overview (SQL Server)

    (a) Full backup

    A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

    (b) Differential backup

    A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential base is a full backup of read/write data. A differential backup contains only the data that has changed since the differential base.


    Incremental Backup

    This type of backup is performed at Windows OS level for file backup. It is not a type of backup fully supported in SQL Server at database level.

    An incremental backup provides a backup of files that have changed or are new since the last incremental backup. For the first incremental backup, all files in the file set are backed up (just as in a full backup). If you use the same file set to perform a incremental backup later, only the files that have changed are backed up.

    Refer this link: Description of Full, Incremental, and Differential Backups

    Also refer the following links.

    Back Up Your Database in SQL Server 2005

    How to Take Incremental Backup in sql server

    Database Backups- The Crib Sheet


     

     

    Wednesday, June 9, 2010 7:53 AM

All replies

  • Hi Baba,

    This article should help you:

    http://msdn.microsoft.com/en-us/library/ms191239.aspx

     

    HTH


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    Wednesday, June 9, 2010 7:36 AM
  • Hi,

    SQL Server supports (a) full and (b) differential backups: Reference: Backup Overview (SQL Server)

    (a) Full backup

    A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

    (b) Differential backup

    A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential base is a full backup of read/write data. A differential backup contains only the data that has changed since the differential base.


    Incremental Backup

    This type of backup is performed at Windows OS level for file backup. It is not a type of backup fully supported in SQL Server at database level.

    An incremental backup provides a backup of files that have changed or are new since the last incremental backup. For the first incremental backup, all files in the file set are backed up (just as in a full backup). If you use the same file set to perform a incremental backup later, only the files that have changed are backed up.

    Refer this link: Description of Full, Incremental, and Differential Backups

    Also refer the following links.

    Back Up Your Database in SQL Server 2005

    How to Take Incremental Backup in sql server

    Database Backups- The Crib Sheet


     

     

    Wednesday, June 9, 2010 7:53 AM
  • Hi Ramesh

    I am having onemore doubt. Already i posted as a ? but i didn't get any answer. Can you guide me on this

    backup log b to disk=''

    When i am taking the backups continuously i am getting different O/P s  like

    some times 8 pages

    after that 4 pages

    after that 0 pages

    why i am getting this type of the O/P.

    Wednesday, June 9, 2010 8:27 AM
  • Sorry, but what is O/P?  P-pages?
    Wednesday, June 9, 2010 8:52 AM
    Answerer
  • After creating the DB when i took the backup of log file i got the msg as

    Processed 2 pages for database 'ME', file 'me_log' on file 5.

    BACKUP LOG successfully processed 2 pages in 0.129 seconds (0.095 MB/sec).

     

    after few seconds again i took the backup of log file the msg is

    Processed 0 pages for database 'ME', file 'me_log' on file 6.

    BACKUP LOG successfully processed 0 pages in 0.082 seconds (0.000 MB/sec).

     

    I want to know what is the reason. Please guide me

     

    Wednesday, June 9, 2010 9:31 AM
  • Because there were no transactions to back up between the first and second backups?
    Wednesday, June 9, 2010 10:07 AM