0

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?

  • I do run CheckDB regularly (and did so immediately after the restores), and have the backup engine verify the backups upon completion. – CaM Apr 4 '18 at 17:11
  • What does "verify the backups" mean? Nothing except a restore will really verify anything – Aaron Bertrand Apr 4 '18 at 17:33
  • "Verify the backups" means that I run a "RESTORE VERIFYONLY FROM DISK='...' WITH CHECKSUM" with my backups. Given that I have over 100 user databases in my environment, some of which are multiple GB in size, there's no practical way to test each and every DB and Log backup via restores. – CaM Apr 5 '18 at 13:20
  • No, I am not suggesting you restore every full and log backup every day, but you should periodically spot check them by actually restoring them. You don't have to do this of course, but it's a much more reliable test than VERIFYONLY. – Aaron Bertrand Apr 5 '18 at 14:04
3

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.