What’s in This SQL Server Backup File?

So, there you are cleaning up files to make additional space and you find a file named “db.bak.” It’s a SQL Server backup file, that much is clear. But what’s in it? When was it created? Is it important? Or can it go? Why can’t people name backup files so that they describe what’s in them, like “AdventureWorks_20220301_copyonly.bak?”

Although, we can’t answer that last question, we can gain some information about the “db.bak” backup file.

SQL Server backup files

When SQL Server backs up a database or transaction log, it includes some additional header information about the backup sets on the media. This information includes the server name, the database name, the backup type (Full, Differential, Log, File), the backup start and finish times, who backed it up, whether it was a copy only backup, and much, much more.

SQL Server uses the header information when it attempts to restore a backup file. For example, the header information helps ensure that log files are being applied to the right database and in the right order, etc.

And we can access the information, too, to learn more about the file itself.

Using RESTORE HEADERONLY

When we find a backup file with little context for what it is, we can examine the header information to determine what the file contains. We do that using the RESTORE HEADERONLY command, as shown below, where /var/opt/mssql/backup/db.bak is the fully qualified path to the backup file.

RESTORE HEADERONLY FROM DISK = '/var/opt/mssql/backup/db.bak';

When we execute this statement from Management Studio or Azure Data Studio, it’ll return the header information. A snapshot of the output is below.

You can see that sa backed up the BasebaseData database on the sql2019 server. Looking at other columns not shown in the image we can see when the backup started, when it completed, the database compatibility level, the recovery model, and even some Log Sequence Number information. Lots of helpful information.

Want to work with The SERO Group?

Have some uncertainty around your SQL Server backups? Schedule a free 30-minute call to see if our SQL Server Health Check or one of our SEROShield plans is right for you.