SQL Server Backups suddenly change size

Question

Yesterday while I was traveling, one of our SQL Server 2016 servers choked on a TempDB that was full. Since I'm the only DBA, one of the system engineers tried to "fix" the server by rebooting.

This triggered some sort of iSCSI/SAN/LUN/etc. cascade of insanity that resulted in the entire data drive being lost (user DBs, system DBs, error log files... all gone). Their response was to supply me with a nice, new, iSCSI drive since they couldn't get the old one back. (sigh)

So today, I restored the system DBs from backup, then restored the user DBs from backup. I then did a full backup on all of the freshly restored databases. Just in case.

But I noticed something.

I restored a two-day-old backup of Master that was 785,489 KB. After completing all of the other restores, the new backup of Master is 718,519 KB.

I restored a two-day-old backup of MSDB that was 36,019 KB. After completing all of the other restores, the new backup of MSDB is 18,333 KB.

(I can't really do a similar comparison on user DBs because they were Full, Diff, and Log restores...)

The backups are compressed. These were full backups that I restored from, and full backups I created. As system databases, there are no diff or log backups being applied in between.

Is it normal for a just-restored database's backup to not be the same size or at least nearly the same size as the original?

Answer 1

A database backup contains the allocated extents plus the parts of the log required to replay any activity that occurred during the backup, and restore the database to the point-in-time at which the backup completed.

So your first backup file could contain more log records than your second backup.

David

Answer 2

In addition to David's answer, I'd like to add that you shouldn't be looking at a backup's size for any indication of correctness. If you want to test that the backup is valid, restore it somewhere. This is a good habit to get into in any case, since a backup isn't valid unless you can restore it. Once it's restored, then you could use any number of schema/data comparison tools to spot check (or even exhaustively check) that it contains the data you expect. You can also run CHECKDB on the restored copy which can give early warnings about certain types of corruption or other issues that may not have bubbled up on the primary yet.

There are a host of reasons why a backup's file size could change. A few off the top of my head:

• Again, like David said, there could be more or fewer log records now than there were when the first backup was taken.
• Changes to the data (e.g. dropping tables, purging data, switching out and dropping partitions, inserting more data).
• Changes to the structure (e.g. creating a clustered index, reorganizing an index, rebuilding an index, turning on snapshot isolation, changing compression).
• Perhaps the previous backup file actually contained multiple backups, and the new one was created WITH INIT (or vice-versa). FWIW I stay away from re-using a single backup file to host multiple backups; YMMV.