Query to Determine if SQL Server Supports Backup Compression

Backup compression is a cool new feature included in SQL Server 2008 Enterprise (or later versions). The following query can allow a script to determine whether a given SQL instance supports backup compression:

SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = 'backup compression default'),-1)

The above query will return 0 or 1 if the SQL server supports backup compression and -1 if it does not. To determine if a linked server supports backup compression, use the following:

DECLARE @statement NVARCHAR(1000)
SELECT @statement = N'SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = ''backup compression default''),-1)'
EXEC [LinkedServer].master.dbo.sp_executesql @statement
EXEC [LinkedServerNamedInstance].master.dbo.sp_executesql @statement

This can be enhanced even further to allow for some simple decision making:

DECLARE @statement NVARCHAR(1000)
DECLARE @ReturnStatus INT

SELECT @statement = N'SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = ''backup compression default''),-1)'

EXEC @ReturnStatus = [SQLNVT06].master.dbo.sp_executesql @statement
IF @ReturnStatus = -1
BEGIN
PRINT 'Not supported'
END
IF @ReturnStatus = 0
BEGIN
PRINT 'Supported but disabled by default'
END
IF @ReturnStatus = 1
BEGIN
PRINT 'Supported and enabled by default'
END

One thought on “Query to Determine if SQL Server Supports Backup Compression

  1. Pingback: To check if a SQL Server support compression backup | ProdHelp

Leave a Reply

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