Database backup methods

Full Backups

Full backups, as the name suggests is a back up of the files and folders. Frequent full backups results in a faster restore. However, full backups are slow. The storage space requirement is also high. They are the most comprehensive type of backups and can also be scheduled for backing up the selected files and folders on a periodic basis.

Differential Backups

Differential backup backups up files that have been changed since the last backup. This reduces the time to backup since only changed files need to be restored. Restoring differential back up is faster and storage requirements are less. The list of files changed since the last back up is recorded in a catalogue file with .bkc extension.

Transaction Log Architecture Backups

Transaction log records all transactions and the database modifications made by each transaction. Transaction log helps in:

• Recovering individual transaction in case of failures
• Helps in recovering incomplete transactions if a running SQL server fails.
• It helps to restore the database to the point where it failed.

The transaction log is a separate file or set of files. The transaction log cache is managed separately from the data pages making it robust.

File and Filegroup Backups

File or Filegroup backups support backing up and restoring individual files or filegroups. This is usually used in very large database where there may not be a lot of time to backup the complete database. File and file groups can be restored from a full database backup set. This allows a quicker recovery because only the file and the file groups that are damaged are restored first.