none
Differential vs Partial Differential Backups

    Question

  • While reading on Backups today I noticed that on the page on Partial Differential Backup they mention the fact that a difference between the base differencital which is a full backup and the new state of the server is stored. In the Differential Backup page this fact is strangely omitted. I began to worry if Differentail means something different and the key word is Partial. Also the Partial Differential page is clearly written but the other one starts from afar, circles around and never settles down to explain what it all means.


    Differential.

     

    Partial Diffrential

     

    I guess I am asking for a clarification.

     

    Thanks.

    Tuesday, December 09, 2008 10:03 PM

Answers

  • Hello Alex

    1) Full back up:

    A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished. 

    A full database backup contains all the data in the database. For a small database that can be backed up quickly, the best practice is to use just full database backups. However, as a database becomes larger, full backups take more time to finish and require more storage space. Therefore, for a large database, you might want to supplement full database backups with differential backups.

    -> This back up contains all the filegroups (files containing data)

    2) Differential backup:

    A differential database backup records only the data that has changed since the last full database backup. This full backup is called the differential base. Differential database backups are smaller and faster than full database backups. This saves backup time at the cost of increased complexity. For large databases, differential backups can occur at shorter intervals than database backups. This reduces the work-loss exposure.

    Differential database backups are especially useful if a subset of a database is modified more frequently than the rest of the database. In these cases, differential database backups enable you back up frequently without the overhead of full database backups.

    -> This backup contains the extents of the database which are changed after the previous full back up. In case if a full back up was taken at 2pm. Then if we try to take a differential back up  at 4 pm it contains all the changes since 2pm. After taking this differential back up, if we try to take another differential back up at 6pm, still it contains all the changes since 2pm.

    3) Partial back up:
    A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, a partial backup contains all the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup of a read-only database contains only the primary filegroup.


    4) Partial Differential backup:

    A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup.

    -> If a partial back up (backing up specific filegroup B excluding read only filegroups A ) is taken at 2pm. If there are certain changes occured in the filegroup B at 3 pm. Then if we perform the differential partial  back up at 4pm, it gives the only change occurred at 3pm (contains all the changes between 2pm and 3pm).

    Hope it clarifies.

    Thanks
    Sreekar

    Monday, December 15, 2008 1:36 PM

All replies

  • Hello,

     

    Partial backups enable to backup selected filegroups of databases.

    Please, could you have a look on these links ?

     

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

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

     

    and

     

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

     

    2 main advantages for the partial backup :

    - if a filegroup is damaged , you may restore it from a partial backup

    - i think it is possible to do several partial backups in the same time towards different tapes ( 1 tape for each partial backup ).If you have put your filegroups on separate physical drives, you should shorten the time necessary to backup the database ( useful for huge databases )

    - the partial backup is always shorter than the normal backup

     

    Don't hesitate to post again for more help or explanations

     

    Have a nice day

     

    Wednesday, December 10, 2008 7:53 AM
  • Hello Alex

    1) Full back up:

    A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished. 

    A full database backup contains all the data in the database. For a small database that can be backed up quickly, the best practice is to use just full database backups. However, as a database becomes larger, full backups take more time to finish and require more storage space. Therefore, for a large database, you might want to supplement full database backups with differential backups.

    -> This back up contains all the filegroups (files containing data)

    2) Differential backup:

    A differential database backup records only the data that has changed since the last full database backup. This full backup is called the differential base. Differential database backups are smaller and faster than full database backups. This saves backup time at the cost of increased complexity. For large databases, differential backups can occur at shorter intervals than database backups. This reduces the work-loss exposure.

    Differential database backups are especially useful if a subset of a database is modified more frequently than the rest of the database. In these cases, differential database backups enable you back up frequently without the overhead of full database backups.

    -> This backup contains the extents of the database which are changed after the previous full back up. In case if a full back up was taken at 2pm. Then if we try to take a differential back up  at 4 pm it contains all the changes since 2pm. After taking this differential back up, if we try to take another differential back up at 6pm, still it contains all the changes since 2pm.

    3) Partial back up:
    A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, a partial backup contains all the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup of a read-only database contains only the primary filegroup.


    4) Partial Differential backup:

    A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup.

    -> If a partial back up (backing up specific filegroup B excluding read only filegroups A ) is taken at 2pm. If there are certain changes occured in the filegroup B at 3 pm. Then if we perform the differential partial  back up at 4pm, it gives the only change occurred at 3pm (contains all the changes between 2pm and 3pm).

    Hope it clarifies.

    Thanks
    Sreekar

    Monday, December 15, 2008 1:36 PM
  • Hello Sreekar,

     

    Within a SQL Server instance, I'm looking for a way to fully backup only those databases that have been modified since the last backup. In other words, differential on an instance level instead of differential on a database level.

    Do you know how to realize a backup script on an instance level in such a way that only modified databases will be fully backupped?

    Thanks in advance,

    SJW

    Monday, January 30, 2012 10:59 AM
  • Hello,

    Please, could you tell us the full version ( including last SP ) and the edition of your SQL Server ? Some features are only available for some specialized editions

    I suppose that you want to backup only the databases where the data of at least one table has been modified ( or do you want to backup databases having a modified structure , for example a stored procedure has been created or dropped , or a column has been added/modified/dropped in a table ? )

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Monday, February 13, 2012 4:25 PM
  • Hello Papy,

    Thanks for your reply.

    Yes, I want to backup only databases where the data of at least one table has been modified.

    My own system runs SQL Server 2008 R2 10.50.2500.0.

    Many thanks,

    SJW

    Tuesday, February 14, 2012 8:30 AM