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
Pingback: To check if a SQL Server support compression backup | ProdHelp