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.
Thank you very much! <3 ❤️
Hi, my name is Jan. I am not a hacker, coder, developer or guru. I am merely an application manager / systems administrator, doing my daily thing at Embrace – The Human Cloud. In the past I worked at CLDIN (CLouDINfra) and Vevida. With over 20 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization. I blog at https://www.saotn.org.