You are here: » Code base » How to determine if a SQL Server backup is compressed?

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
Code language: SQL (Structured Query Language) (sql)

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.

Psst, looking to learn how to Manually failover all databases in an SQL Server Database Mirroring configuration?

Leave a Comment

Your email address will not be published. Required fields are marked *