Taking database backups is still considered to be the most important task that any database administrator does. That’s why it is also important to make sure that database backups not only complete successfully but have also been validated and tested. I’ve written several blog posts about the importance of backups and why having successful backups is not enough.

I also don’t see much discussion about the concept of SQL Server multibase differential backups. As per SQL Server Books Online, “a multibase differential backup is a differential backup with multiple bases.”

Say, what? What does that even mean?

For years, I’ve simply relied on the definition of what differential backups are to understand what it does. Here’s an excerpt from SQL Server Books Online about differential backups: A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential.

When you read that definition, you’ll probably think that the most recent full database backup is all you need to restore the most recent differential backup. And, that’s what I thought for years. Boy, was I wrong! Maybe because I try to keep my backup strategies as simple as I possibly can to reduce the amount of complexities in the restore strategy. That was until I read this blog post.

How Could That Happen To Me?

It’s a good thing you asked. When I first read this blog post, I thought there was no way for me to have a multibase differential backup if all I’m doing is a combination of FULL, DIFFERENTIAL and LOG backups. After all, that’s what I’ve learned when I first started doing database backups. SQL Server Books Online highlight some causes of multibase differential backups.

• taking differential backups that is based on a set of file backups
• when access to a filegroup has been changed since the last differential backup

These may not be regular occurrences in your databases and might not warrant any attention. That’s probably why I didn’t know about it until I’ve read the blog post. But what’s more common nowadays is taking differential backups and using them to synchronize Availability Group replicas or database mirroring partners. If the mirror or the secondary replica has been out-of-sync, we need to synchronize them by taking the latest backup and restoring them in NORECOVERY mode. This task is often referred to as re-syncing the database. But if you have a very large database (VLDB,) it wouldn’t make sense to take a FULL database backup, copy it across the network to restore to the mirror or replica. That’s where you might use a differential backup instead. And if you take regular differential backups as part of your backup strategy, then you may have introduced a multibase differential backup.

Here’s a video I recorded two years ago that explains what multibase differential backups are and how they work. It might be a good exercise to review your existing backups to see if you have any lying around.

[youtube id=”SQjbI9B86Yc”]