How to determine if a SQL Server backup is compressed?

Compressed SQL Server backups can be verified in PowerShell using a handy PowerShell function. This comes in handy when you need to verify if existing SQL Server backups are compressed.

The following PowerShell function returns 1 when a SQL Server backup is compressed. You need to provide the (remote) database server and backup file location.

Param
(
  [string]$Server,
  [string]$BAKFile
)

[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.SMO')
[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.SMOExtended')

$SMOServer = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$Res = New-Object Microsoft.SqlServer.Management.Smo.Restore

$Res.Devices.AddDevice($BAKFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$Res.ReadBackupHeader($SMOServer).Rows[0].CompressedCode language: PowerShell (powershell)

Provide server hostname ($Server) and backup file ($BackFile), usage:

PS C:\Users\janreilink> .\Desktop\IsBackupCompressed.ps1 localhost D:\mssql\backups\testdb.bak
1Code language: PowerShell (powershell)

Source: JNK on Database Administrators StackExchange. There is also an Stored Procedure available by Max Vernon to determine if a database backup file is initialized for compression.

Manually failover all databases in an SQL Server Database Mirroring configuration

Show Your Support

If you want to step in to help me cover the costs for running this website, that would be awesome. Just use this link to donate a cup of coffee ☕($10 USD or €10 EUR for example). And please share the love and help others make use of this website. Thank you very much! <3 ❤️