Can You Restore Multiple Differential Backup Files?

I was recently asked if you can restore multiple differential backups in preparation for a migration. I responded that yes, technically you can restore multiple differential backups, however it will not speed up your cutover. I explained that the last differential will still restore all the differential data since the full backup. They were thinking that the last differential restore would skip data that was previously restored from an earlier differential restore. I could understand their logic and they wanted to believe what I was telling them, but sometimes it is easier to believe something when you can see it for yourself. For that reason, I put together a quick demo to demonstrate restoring multiple differential backups.

To get started, I created an empty database called DIFF_DEMO and performed a full backup. I then created a table called noise.

Once the table was created, I inserted a record into each column using an insert statement and looped 25,000 times. This amount of data insertion provided a reasonable amount of data for the demo.

After the insert, I performed a DIFFERENTIAL backup

BACKUP DATABASE DIFF_DEMO TO DISK = ‘C:\Backup\Demo_Diff\Diff_Demo_diff1.bak’ WITH COMPRESSION, DIFFERENTIAL, INIT

I repeated this process two more times for a total of 3 batches of inserts and 3 differential backups.

Next was performing the restores. I started with a full restore as my base, then restored the differential backups in order showing that each differential restore restored the full size of the differential backup.

Differential restore 1 was 697 pages, differential restore 2 was 1289 pages, and the final differential restore was 1833 pages. The only data change before each differential backup was inserting 25,000 unique records. Each differential backup captures the state of any extents that have changed between when the differential base (FULL backup) was created and when the differential backup is created. That means that differential backup 3 would contain all extents that were changed since the full backup, which also includes those captured in differential backups 1 and 2. You can easily see that each differential backup is larger than the one before it due to the records getting inserted.

Can you restore multiple differential backups, absolutely, however what is the business case or reason why? I’m sure I could come up with a justification, however for the purpose of speeding up a migration with utilizing differential backups, restoring an earlier differential isn’t going to speed that up.

Check out my demo video converting this. It’s just under 4 minutes long, short and sweet. Make sure to click like and subscribe to help me grow my channel.