This topic describes strategies you can use to establish appropriate backup guidelines.
The strategies are:
- Full Backup
- Differential Backup
- Partial Backup
- Partial Differential Backup
Full Backup:
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.
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 file-groups (files containing data)
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.
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.
Partial Backup:
A partial backup resembles a full database backup, but a partial backup does not contain all the file-groups. Instead, a partial backup contains all the data in the primary file-group, every read/write file-group, and any optionally-specified read-only files. Partial backups are useful whenever you want to exclude read-only file-groups.
A partial backup resembles a full database backup, but a partial backup does not contain all the file-groups. Instead, a partial backup contains all the data in the primary file-group, every read/write file-group, and any optionally-specified read-only files. Partial backups are useful whenever you want to exclude read-only file-groups.
A partial backup of a read-only database contains only the primary file-group.
Partial Differential Backup:
A differential partial backup records only the data extents that have changed in the file-groups since the previous partial backup.
A differential partial backup records only the data extents that have changed in the file-groups since the previous partial backup.
If a partial back up (backing up specific file-group B excluding read only file-groups A ) is taken at 2pm. If there are certain changes occurred in the file-group 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)
