Backup

TYPE OF BACKUP

Full Backup

• It will be a complete backup

Incremental (Differential) Backup

• It will be applied on full backup and cannot be created until full backup exists
• Each differential backup will contain all the changes since full backup has been taken
• Header of the database has special page called DCM (Differential Change Map), this page will have the list of all pages which have been changed after full backup.
• Once we take a full backup, DCM will be wiped out.
• Differential Backup 1 = changes made after full backup
• Differential Backup 2 = changes made after full backup
• Differential Backup N = changes made after full backup

Transaction Log Backup

• It will be applied on full backup and cannot be created until full backup exists
• Each transactional log backup will contain the data which has been changed after full backup/last transactional log backup
• Each transaction will have LSN (Log Sequence Number). Each transactional log backup will contain the data (COMMITTED) since last LSN which was part of full backup/last transactional log backup.
• Any gap in LSN will corrupt taking/restoring backup, which can be solved by taking a full backup.
• Transactional Log Backup 1 = changes made after full backup
• Transactional Log Backup 2 = changes made after Transactional Log Backup 1
• Transactional Log Backup 3 = changes made after Transactional Log Backup 2
• Transactional Log Backup N = changes made after Transactional Log Backup N-1

FileGroup Backup

PROCESS OF TAKING BACKUP

• Lock the database and lock all transactions
• Place a mark in transaction log
• Releases lock
• Extracts all the pages from database files and put them in backup device
• Lock the database and lock all transactions
• Place a mark in transaction log
• Releases lock
• Extracts all the pages between marks and append them in backup

RESTORING A BACKUP

• If it is full backup then it is very easy to restore it using a wizard.
• If it is differential/transactional log backup, we have to follow below steps
• Restore a full backup WITH NORECOVERY option.
• Once you restore a backup WITH NORECOVERY, it will display like this.
• Restore a first transactional log backup WITH NORECOVERY option
• Restore final transactional log backup / differential backup WITH RECOVERY option which is default
• Once backup has been restored WITH RECOVERY option, no other transactional / differential backup can be restored on top of that.