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.
In this article
PowerShell function with SMO: SQL backup compression since SQL Server 2022
From the backup history in msdb
Query whether new backups are compressed
A reproducible example, and what it actually shows: TDE and MAXTRANSFERSIZE: the old advice is dated, Striped backups: name every file, Edition limits cut only one way, Bonus: the same header covers encryption
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
1This 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.
SQL backup compression since SQL Server 2022
SQL Server 2022 changed this question in a way the methods above do not cover: a backup is no longer simply compressed or uncompressed, it is compressed with something. Next to the classic MS_XPRESS there is now QAT_DEFLATE, which offloads the work to Intel QuickAssist Technology, in hardware where the accelerator is present and in software mode where it is not.
That distinction is operational, not academic. A backup written with QAT_DEFLATE needs the QAT drivers present on the instance you restore it to. Hand such a file to someone without saying so and their restore fails on a dependency they have never heard of. So "which algorithm" is part of the answer, not a footnote to it.
Three places that tell you the algorithm
In the backup file. RESTORE HEADERONLY gained a CompressionAlgorithm column. It holds an empty string when the backup is not compressed,and MS_XPRESS or QAT_DEFLATE when it is:
RESTORE HEADERONLY FROM DISK = N'D:\Backup\db.bak';
-- Compressed, BackupSize, CompressedBackupSize, CompressionAlgorithmIn the backup history. msdb.dbo.backupset gained a matching compression_algorithm column, NULL when the backup is not compressed.
In the instance configuration. Next to the familiar backup compression default there is now a backup compression algorithm setting that decides what a plain WITH COMPRESSION actually uses:
SELECT name, value_in_use
FROM sys.configurations
WHERE name LIKE '%compression%';That last one is worth checking before you go looking at backup files at all. When backup compression default is 0 and the job does not say WITH COMPRESSION, you get an uncompressed backup no matter what anyone assumed.
A history query that reads a flag instead of guessing
The backup history method shown earlier infers compression from compressed_backup_size < backup_size. It has to, because msdb.dbo.backupset has no boolean for it: there is no is_compressed column, only the two size columns. On SQL Server 2022 and later you can stop guessing:
SELECT TOP (20)
bs.database_name,
bs.backup_start_date,
bs.type,
bs.backup_size,
bs.compressed_backup_size,
bs.compression_algorithm,
CASE WHEN bs.compression_algorithm IS NOT NULL THEN 1 ELSE 0 END AS IsCompressed,
CAST(bs.backup_size * 1.0
/ NULLIF(bs.compressed_backup_size, 0) AS decimal(5,2)) AS Ratio
FROM msdb.dbo.backupset AS bs
ORDER BY bs.backup_finish_date DESC;Keep the size comparison for SQL Server 2019 and older, where compression_algorithm does not exist and this query will not even compile.
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, CompressedBackupSizeNote 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.
Bonus: If you want, for example, to check whether a FILESTREAM is present in your SQL backup file, use RESTORE filelistonly FROM DISK and look for Type S:
$backupFile = "E:\temp\backup.bak"
$dumpFilelistInfo = "RESTORE filelistonly FROM DISK='$backupFile'"
Invoke-Sqlcmd -Query $dumpFilelistInfo -Encrypt Optional
...
LogicalName : Db_blobs
PhysicalName : D:\data\MSSQL\DATA\Db_blobs
Type : S
FileGroupName : Blobs
Size : 192643232951
MaxSize : 0
FileId : 65537
CreateLSN : 35000000016000003
DropLSN : 0
UniqueId : 884aa91e-6643-451c-8367-caba38397cf9
ReadOnlyLSN : 0
ReadWriteLSN : 0
BackupSizeInBytes : 196941119488
SourceBlockSize : 4096
FileGroupId : 2
LogGroupGUID :
DifferentialBaseLSN : 356243000039633600006
DifferentialBaseGUID : 5824d8de-7d1b-4958-a5e1-3027f18fe7e9
IsReadOnly : False
IsPresent : True
TDEThumbprint :
SnapshotUrl :And to query its size:
Invoke-Sqlcmd -Query "RESTORE HEADERONLY FROM DISK='$backupFile'" -Encrypt Optional | Select-Object Compressed, BackupSize, CompressedBackupSize, DatabaseVersion, BackupFinishDate
Compressed : 1
BackupSize : 512148965376
CompressedBackupSize : 259521522658
DatabaseVersion : 904
BackupFinishDate : 14-6-2026 14:00:00dbatools - 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 (20)
bs.database_name, bs.backup_start_date, bs.type,
bs.backup_size, bs.compressed_backup_size,
bs.compression_algorithm, -- SQL Server 2022+
CASE WHEN bs.compression_algorithm IS NOT NULL THEN 1 ELSE 0 END AS IsCompressed,
CAST(bs.backup_size * 1.0 / NULLIF(bs.compressed_backup_size, 0) AS decimal(5,2)) AS Ratio
FROM msdb.dbo.backupset AS bs
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';A reproducible example, and what it actually shows
I set out to break the size comparison, expecting that compressing incompressible data would produce a backup that is not smaller, which would make the old query report a compressed backup as uncompressed. Here is the test, on SQL Server 2022 (16.0.4275.2). It creates a database whose contents are cryptographically random, so there is nothing for a compressor to find:
CREATE DATABASE CompressDemo;
ALTER DATABASE CompressDemo SET RECOVERY SIMPLE;
GO
USE CompressDemo;
CREATE TABLE dbo.Incompressible
(
Id int IDENTITY PRIMARY KEY,
Payload varbinary(8000) NOT NULL
);
INSERT dbo.Incompressible (Payload)
SELECT TOP (25000) CRYPT_GEN_RANDOM(8000)
FROM sys.all_columns a CROSS JOIN sys.all_columns b; -- 190 MB of noise
GO
BACKUP DATABASE CompressDemo TO DISK = N'C:\Backup\demo_plain.bak'
WITH INIT, NO_COMPRESSION;
BACKUP DATABASE CompressDemo TO DISK = N'C:\Backup\demo_comp.bak'
WITH INIT, COMPRESSION;The result was not what I expected:
| Backup | Compressed | BackupSize | CompressedBackupSize | Algorithm |
|---|---|---|---|---|
NO_COMPRESSION | 0 | 210,863,104 | 210,863,104 | (empty) |
COMPRESSION | 1 | 210,863,104 | 204,156,024 | MS_XPRESS |
Even with 190 MB of pure noise the compressed backup came out 3.2% smaller, so compressed_backup_size < backup_size still answered the question correctly. The reason is that the payload is not the whole backup: page headers, allocation pages and the transaction log compress perfectly well, and that is enough to keep the comparison on the right side.
Two things worth taking from that. First, the size comparison is more robust than it looks, and it has no false positives either: on an uncompressed backup CompressedBackupSize is exactly equal to BackupSize, not NULL, so a smaller value really does mean compression happened.
Second, and more useful: the comparison answers a different question than the one you asked. It tells you whether compression helped, not whether it was requested. Three percent is a rounding error, and a query that returns IsCompressed = 1 gives you no hint that the setting bought you almost nothing. A real example: in a 477 GiB backup I looked at recently, 294 GiB of table data compressed down to roughly 58 GiB while a 183 GiB FILESTREAM filegroup did not shrink at all. Same backup, same setting, wildly different value. compression_algorithm plus the ratio tells you both things; the comparison on its own tells you neither.
Clean up afterwards, including the history rows the test wrote:
DROP DATABASE CompressDemo;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'CompressDemo';TDE and MAXTRANSFERSIZE: the old advice is dated
The classic warning is that backup compression on a TDE encrypted database silently achieves nothing unless you raise MAXTRANSFERSIZE above 64 KB. I tested it on the same database with TDE switched on (AES_256), and on SQL Server 2022 that is no longer the case:
| Backup | Compressed | BackupSize | CompressedBackupSize |
|---|---|---|---|
COMPRESSION, default MAXTRANSFERSIZE | 1 | 211,886,080 | 205,098,292 |
COMPRESSION, MAXTRANSFERSIZE = 131072 | 1 | 210,182,144 | 203,422,780 |
Both compressed, and the explicit transfer size gained almost nothing. Modern versions raise it for you. If you are on an older version, or you find Compressed = 0 on a TDE database where the job clearly says WITH COMPRESSION, this is still the first thing to check.
Striped backups: name every file
Every example here reads a single file. A striped backup is one media set spread across several files, and RESTORE HEADERONLY wants all of them. Point it at a single member and it refuses, because the media family is incomplete:
RESTORE HEADERONLY FROM DISK = N'D:\Backup\db_1.bak',
DISK = N'D:\Backup\db_2.bak',
DISK = N'D:\Backup\db_3.bak';Edition limits cut only one way
Express and Web cannot create compressed backups. They can restore them: reading a compressed backup has worked on every edition since SQL Server 2008 R2. Useful when you are handed a backup and are wondering whether the target instance can take it.
Bonus: the same header covers encryption
The header that answers the compression question also answers the encryption one, and you do not need the certificate or the key to read it:
RESTORE HEADERONLY FROM DISK = N'D:\Backup\db.bak';
-- KeyAlgorithm, EncryptorType, EncryptorThumbprintAn empty EncryptorType means the backup is not encrypted. A populated EncryptorThumbprint is the certificate you are going to need on the restore target, which is a much friendlier thing to find out now than halfway through a restore.
Key Takeaways
- You can determine if SQL Server backup is compressed using a PowerShell function.
- This function returns 1 when a backup is compressed, requiring the server name and backup file path.
- Additional resources include a Stored Procedure for checking if a backup file is initialized for compression.
But in short: use Read-DbaBackupHeader if dbatools is an option, otherwise Invoke-Sqlcmd with RESTORE HEADERONLY.
This article needs to be technically correct and is quite some years old, therefor I used Claude to proofread it for me and extend on some SQL Server 2022+ issues.