SqlBak Guide: SQL Server Differential and Transaction Log Backups

Differential and transaction log backups differ from full backups in that they only backup data that have been changed. Differential backups will backup all the new data since the last full backup, while transaction log backups will backup all the new data from the moment of any backup. This fundamentally distinguishes them from full backups, which contain all database data.

• Full backup – contains all database data
• Differential backup – contains data that have been changed since the creation of the previous FULL backup
• Transaction log backup – contains data that have been changed since the creation of any backup, including a previous transaction log backup

What are the benefits of differential or transaction log backups?

1. Smaller size – backup size is proportional to the amount of data that has been changed
2. Takes less time to complete – since these backups are smaller, they are not only faster to complete, but also faster to compress and send to the destination
3. Because of smaller sizer and requiring less time, you can make these backups much more often than regular full backups

What are the disadvantages of these backups?

1. If you restore a database manually, then you need to understand and follow a backup chain. SqlBak does this automatically, as it keeps a record of all the backups it has created.
2. In case of an error in one file, the entire backup chain is corrupted. To minimize these risks, it is recommended to make a full backup from time to time and test a restore process.
3. To create transaction log backups, a database should be in full or bulk-logged recovery mode.

Backup schedule recommendations

Set the “First Full backup start” option to nighttime (for example, 3:00 am). This is done so that full backups are performed at night. If full backups should be run with a frequency of 168 hours (one week), then set the “First Full backup start” option to the nighttime of a weekday or weekend, depending on the specifics of using the database.

Full + Differential

Full – every 24 hours
Differential – every 3 hours

Full – every 168 hours
Differential – every 6 hours

Full + Differential + Transaction Log

Full – every 24 hours
Differential – every 3 hours
Transaction Log – every 15 minutes

Full – every 24 hours
Differential – every 6 hours
Transaction Log – every 1 hour

Full – every 168 hours
Differential – every 24 hours
Transaction Log – every 3 hours