How to determine if a SQL Server backup is compressed?

Photo of author
Written By Jan Reilink

Windows Server system administrator & enthusiast.

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].Compressed
Code 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 1
Code 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.

Did you like: How to determine if a SQL Server backup is compressed?

Then please, take a second to support Sysadmins of the North and donate!

Your generosity helps pay for the ongoing costs associated with running this website like coffee, hosting services, library mirrors, domain renewals, time for article research, and coffee, just to name a few.

Hi! Join the discussion, leave a reply!