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

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

PS C:\Users\janreilink> .\Desktop\IsBackupCompressed.ps1 localhost D:\mssql\backups\testdb.bak
1

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.


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 ($5 USD for example). And please share the love and help others make use of this website. Thank you very much!


About the Author Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization.

follow me on:

Leave a Reply

Be the First to Comment!

avatar
  Subscribe  
Notify of