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 = "E:\backup\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 = "E:\backup\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 querying SQL Server backup files with PowerShell
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
How to fix the error message "Invoke-Sqlcmd : Cannot open backup device 'database.bak'. Operating system error 5(Access is denied.). RESTORE HEADERONLY is terminating abnormally." If your SQL Server service account does not have write permissions on the folder where your SQL Server backup .bak file is located, this error message is returned by Invoke-SqlCmd