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.

Why compress backups?

Backup compression is supported on SQL Server Enterprise, Standard, and Developer, and every edition of SQL Server 2008 (10.0.x) and later can restore a compressed backup. A huge advantage of having your backups compressed is: a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

PowerShell function with SMO

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. Caveat: the user account executing this function must have CREATE DATABASE permissions, as ReadBackupHeader executes RESTORE HEADERONLY underneath.

[CmdletBinding()]
param(
	[Parameter(Mandatory)][string]$ServerInstance,
	# Server-side path(s)
	# For a striped backup: all files of the same media set
	[Parameter(Mandatory)][string[]]$BackupFile,
	[int]$ConnectionTimeout = 15
)

# Load the SMO assemblies
Import-Module SqlServer -ErrorAction Stop

$smo = $null; $restore = $null
try {
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance
	$smo.ConnectionContext.ApplicationName = 'Test-SqlBackupCompression'
	$smo.ConnectionContext.ConnectTimeout  = $ConnectionTimeout

	$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
	foreach ($file in $BackupFile) {
		$restore.Devices.AddDevice($file, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
	}

	foreach ($row in $restore.ReadBackupHeader($smo).Rows) {
		# Compressed comes back as a bit or as the string '0'/'1'. Never cast it
		# to [bool]: [bool]'0' evaluates to $true in PowerShell.
		$isCompressed = if ($row.Compressed -is [DBNull]) { $null }
						else { [int]$row.Compressed -eq 1 }

		[pscustomobject]@{
			ServerInstance		 = $ServerInstance
			BackupFile			 = $BackupFile -join ';'
			Position			 = $row.Position
			DatabaseName		 = $row.DatabaseName
			BackupType			 = $row.BackupType
			BackupStartDate		 = $row.BackupStartDate
			Compressed			 = $isCompressed
			BackupSize			 = [decimal]$row.BackupSize
			CompressedBackupSize = [decimal]$row.CompressedBackupSize
		}
	}
}
catch {
	throw ("Failed to read the backup header of '{0}' on '{1}': {2}" -f
		($BackupFile -join ';'), $ServerInstance, $_.Exception.GetBaseException().Message)
}
finally {
	if ($restore) { $restore.Devices.Clear() }
	if ($smo -and $smo.ConnectionContext.IsOpen) { $smo.ConnectionContext.Disconnect() }
}

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

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

This code is inspired by JNK‘s code on Database Administrators StackExchange. There is also an Stored Procedure available by Hannah Vernon to determine if an SQL database backup file is initialized for compression.

T-SQL without SMO

What SMO under the hood performs, minus the assembly loading:

$path = 'D:\Backup\db.bak'
$query = "RESTORE HEADERONLY FROM DISK = N'$($path.Replace("'","''"))'"
Invoke-Sqlcmd -ServerInstance . -Query $query -TrustServerCertificate |
    Select-Object Position, DatabaseName, Compressed, BackupSize, CompressedBackupSize

Note the -TrustServerCertificate: as of the SqlServer module version 22, Encrypt defaults to Mandatory, so without it Invoke-Sqlcmd fails against a self-signed certificate. See Enable TLS / SSL in System.Data.SqlClient – encrypted SQL connection for more information.

Plain sqlcmd works too (sqlcmd -S . -E -Q "RESTORE HEADERONLY FROM DISK = N'...'"), but you cannot SELECT from RESTORE HEADERONLY, so you get all ~60 columns dumped across your screen. Filtering means creating a temp table with the complete, version-dependent column list.

dbatools – the shortest route

The fastest, easiest method perhaps is using dbatools. As an SQL Server administrator, I’m sure you have this PowerShell module installed. Right? 🙂

Read-DbaBackupHeader -SqlInstance . -Path 'D:\Backup\db.bak' |
    Select-Object DatabaseName, Position, Compressed, BackupSize, CompressedBackupSize

Get-DbaBackupInformation -SqlInstance . -Path 'D:\Backup\db.bak'

From the backup history in msdb

You can also get the required backup information using plain T-SQL on the msdb database in SQL Server:

SELECT TOP (5)
       bs.database_name, bs.type, bs.backup_finish_date,
       bs.backup_size, bs.compressed_backup_size,
       CAST(CASE WHEN bs.compressed_backup_size < bs.backup_size THEN 1 ELSE 0 END AS bit) AS IsCompressed,
       bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bs.media_set_id
WHERE bmf.physical_device_name = N'D:\Backup\db.bak'
ORDER BY bs.backup_finish_date DESC;

It returns IsCompressed = 0 or IsCompressed = 1, among other backup information. This is fast, and it does not need CREATE DATABASE permission, but it only works on the instance that took the backup, and history gets purged (sp_delete_backuphistory).

Query whether new backups are compressed

Do you want to know whether new backups are to be compressed in SQL Server? Use the following query:

SELECT name, value_in_use FROM sys.configurations WHERE name = 'backup compression default';

But in short: use Read-DbaBackupHeader if dbatools is an option, otherwise Invoke-Sqlcmd with RESTORE HEADERONLY.