Differential backup

SQL Server maintains an internal structure called a differential map. This structure maps all changes are done in database extends from the last full backup. This is very useful in cases when a transaction log backup is executed often. Let's see an example where the administrator needs to back up a database on a daily basis, similar to our example in the transaction log backup section, but the transaction log backup is executed every ten minutes. It leads to six transaction log backups every hour and to more than a hundred transaction log backups every day. Such an amount of backup raises a risk of unreadability or loss of certain backups and also increases time spent by the restore process. To reduce the risks and the time of restore, the administrator can add the differential backup to his backup strategy. Differential backup has these characteristics:

• It is a kind of full backup (backups extents changed from the last full backup and do not maintain the transaction log)
• It is cumulative (backup extents changed from last full backup, hence allows you to skip more transaction log backups during restore)
• It is faster and smaller than full backup (does not slow down the database for too long and can be executed against user work without decisive influence on performance)
• It does not need any additional settings on the database or server level

The syntax for differential backup is as follows:

BACKUP DATABASE AdventureWorks TO DISK = 'D:\myBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL

From the syntax point of view, differential backup is just a database backup with one more option. Other options such as INIT/NOINIT are also possible. If the use of differential backups is recognized, the timeline of backups will be according to the following table. This table describes a daily-based strategy for smaller databases with all backups stored in the same backup file: