Differential and transaction log backups are available only for local SQL Server connections. If you backup SQL Server using SqlBak, then you can add differential or transaction log backups to your backup plan in the “Advanced backup schedule” settings.
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?
- Smaller size – backup size is proportional to the amount of data that has been changed
- 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
- 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?
- 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.
- 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.
- 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
A transaction log backup captures all the transactions made since the last transaction log backup.
It only includes the changes recorded in the transaction log, not the actual data pages.
Transaction log backups are smaller in size compared to differential backups because they only contain the log records.
Hello Arianne,
Thank you for bringing this to our attention; we have updated the article.