Differential Backup in SQL Server
Differential Backup in SQL Server
By : Kasim Wirama, MCDBA, MCITP, MVP SQL Server
This article I would like to share about differential database backup in SQL Server. SQL Server gives 3 kinds of database backup options, i.e. full database backup, differential database backup, and transaction log backup. It is not incorrect decision to use all of them, it depends on your SLA (Service Level Agreement), in what extent your organization allows data loss in case there happens database disaster. Let’s read through for database differential backup discussion.
Differential database backup will backup last changed data since full database backup. How does SQL Server recognize data changes since last full backup? Here is the secret under the hood. During full backup, SQL Server will reset bitmap for each extent. An extent contains 8 contiguous 8 KB-size pages. When a data changes in one extent, a bitmap will be changed and when differential database backup happens, it will check current bitmap, when it is changed since last full backup, the extent will be backup.
How do you use differential backup for restore purpose? If you have last full database backup and several differential database backup, you just need to restore last full database backup and follow by only last differential database backup to have your data back as recent as possible.
Other things you need to consider differential backup in your backup strategy is that the size of differential backup is typically smaller than full database backup. It is possible that size of differential backup is same as full database backup. When it happens, you need to revise the length period between full database backup and next full database backup. Differential database backup time is shorter than full database backup. It is recommended especially you have limited backup space.