Get SQL database backup details with PowerShell

Learn how to query the .bak file for header information. Whenever I need to restore an SQL Server .bak backup file, I want to know some properties of the backup file to make sure I'm working with the correct file(s). Luckily, the .bak file and PowerShell provide all the information I need, so I don't have to start a Restore procedure in SQL Server Management Studio (SSMS) just to view the header information. As you know, SSMS is slow...

The RESTORE HEADERONLY option allows you to see the SQL Server backup header information for all backups for a particular backup device. Doing so allows you to verify you are working with the correct .bak backup file. This may come in handy in a more automated DevOps environment. This article shows you a PowerShell way with Invoke-SqlCmd, and how you can use Microsoft.SqlServer.SMO assembly in PowerShell.

SQL Server provides the RESTORE HEADERONLY statement, allowing you to store a result set containing all the backup header information for all backup sets on a particular backup device in SQL Server. In most cases each backup you create only has one backup stored in a physical file, so you will probably only see one header record, but if you had multiple backups in one file you would see the information for each backup. Combined with PowerShell Invoke-Sqlcmd, you can perform such a restore statement on a .bak backup file.

$backupFile = "S:\manualbackup\database.bak"
$dumpHeaderInfo = "RESTORE HEADERONLY FROM DISK='$backupFile'"
Invoke-Sqlcmd -Query $dumpHeaderInfo -Encrypt Optional | Select-Object -ExpandProperty ServerName

The requested result set is printed on screen: name of the server that wrote the backup set.

If you want to use Microsoft SQL Server Management Objects (SMO) directly, you can use the following in PowerShell to load required assemblies (see Load the SMO Assemblies in Windows PowerShell), connect to the database server and request the backup header information:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

$backupFile = "S:\manualbackup\database.bak"
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
$smoRestore.Devices.Add($backupDevice)
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)
"Backup created on server: " +$smoRestoreDetails.Rows[0]["ServerName"]

Code partially taken from PS script to get SQL version from BAK file and Get Database Backup Details with PowerShell.

Get the backup's SoftwareVersionMajor, SoftwareVersionMinor and SoftwareVersionBuild properties to prevent trying to restore .bak files against wrong versions of SQL Server.

Conclusion

If you need to restore SQL Server backups regularly, across different servers, it's recommended to verify you work with the right backup files. This post showed you how to use T-SQL RESTORE HEADERONLY with PowerShell's Invoke-SqlCmd, and how you can load Microsoft SQL Server Management Objects (SMO) assemblies directly in PowerShell to accomplish the same. If you'd like more information about SQL Server Management Objects, I recommend Microsoft's SQL Server Management Objects (SMO) Programming Guide.

How to determine if a SQL Server backup is compressed?

Frequently asked questions

Thank you very much! <3 ❤️