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
