Check if SQL Server supports CompressionOption in PowerShell

2015/11/11

You have a PowerShell script that does a database backup using SMO API.
You want to have a compressed backup, in order to save space.
You use

$smoBackup = New-Object (“Microsoft.SqlServer.Management.Smo.Backup”)
$smoBackup.Action = “Database”
$smoBackup.BackupSetDescription = “Full Backup of ” + $dbName
$smoBackup.BackupSetName = $dbName + ” Backup”
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = “Disk”
$smoBackup.Devices.AddDevice($targetPath, “File”)
$smoBackup.CompressionOption = 1

Cool, right ?
Wrong !

You might get an error like
BACKUP DATABASE WITH COMPRESSION is not supported on Express Edition (64-bit).
BACKUP DATABASE is terminating abnormally.

This is because several SQL Server versions / editions do not support the CompressionOption, among which SQL Server Express is one of them.
In order to check if the SQL Server supports the backup compression option, use the following script:

$compressOpt = -1 # Means that CompressionOption is not present
try {
  # For the line below, you could also use -DisableNameChecking,
  # but in my case, inside Chef using PowerShell script, was crashing
  Import-Module ‘sqlps’ -EA 0 -WA 0 > $null
  # Check to see if we have indeed backup CompressionOption
  $compressOpt = (Invoke-Sqlcmd -Query “SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = ‘backup compression default’),-1) As CompressionOption;” -ServerInstance $serverName).CompressionOption
}
catch {
}

if ($compressOpt -gt -1) { # Means that we can use backup comppresion
  $smoBackup.CompressionOption = “1”
}

Note 1: Credits go to StackOverflow, IT blogs and alike, sites that helped me with the code when searching for solutions.
Note 2: Be aware that when copy-pasting, the quotes and possibly other characters get messed up by WordPress, you’ll have to replace them.


%d bloggers like this: