Backing up your SQL Server database is essential for protecting your data.
When databases come bigger, you’re going to run out of time to do a full backup every day.
That’s why many companies have differential backups running over the night (daily) and backups full on weekends.
What are differential backups?
The differential backup is based on the latest full backup of a complete or partial database or a set of data files or filegroups.
It has the pages that are different – Not only the sum of the total of the transaction logs that have happened since the last full).
When the disaster comes, to do a restore, you need:
- The most recent full backup
- The most recent differential backup
- The transaction logs since the differential
Common issues with Differential backups
Lacking Full Backup
Do you have the last full backup file safe? Is It accessible and working correctly?
The differential backup is useless without the full backup.
If your maintenance plan was not set up properly, it might delete your full backups and keep only the recent differentials. For example, maintenance Plans like Ola Hallengren have a feature that automatically deletes backups older than X days.
Breaking the backup Chain
It is not unusual to find backups being run by some mysterious third-party tools in an enterprise environment.
This might seem like a simple enough task, but If a tool or someone else takes a full backup on production to refresh the development server, you’d better know exactly where that file is. The next differential would refer to the most recent full backup – even if you were not the one who did it.
A not too long time ago, we faced a situation where a client was getting an error when he was trying to restore the differential backup. The message from SQL Server was, “The log backup chain is broken” error when the log backup process fails in SQL Server”.
In an in-depth investigation, we figure out that a tool from the VM machine was running every day and breaking the backup Chain:
As you can see above, the last differential (DIFF_20200922_000501.bak – 09/22/2020) relies on a full backup made on 09/21/2020 at 5:00 PM. So, in this case, the full backup “FULL_20200920_000502.bak” can not be used for restoring. Once another FULL was taken, this broke the backup Chain.
That is why having two backup strategies can be a recipe for disaster. Unfortunately, the last full backup needed to restore the last differential backup file was not accessible for the client.
The good news is that Transaction Log backups are NOT susceptible to this problem. Only DIFFERENTIAL backups are.
If you need an extra “Adhoc” backup of a live database, check out copy-only backups that don’t affect the Backup Chain.
In short, you can specify a copy-only full backup using TSQL the syntax:
BACKUP DATABASE MyDatabase TO DISK 'E:\Backup\CopyOnly_MyDatabase.bak' WITH COPY_ONLY;
Another option is to use the GUI version in SSMS, just checking the ‘Copy Only Backup’ checkbox in the Backup window.
Rebuilding indexes after the full backup
Do you have a maintenance plan that rebuilds the indexes every day on your server? Take care. If you are rebuilding all indexes on all tables, expect your differential backups to be about the same size as your full backups.
When taking a full backup and then rebuilding the indexes afterward, all the changes involved in rebuilding the indexes will be reflected in differential backups in the future.
To prevent the differential backups from being excessively large, schedule the index maintenance right before the full backup.
The differential backups can save your maintenance window when done right. But if you are not prepared for the additional management discipline they require, they are worse than no backups at all.
Check the best practices to keep your SQL Server backups safe and avoid mistakes.
Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.
