Determine if BACKUP COMPRESSION is available

DECLARE @backup_path nvarchar(255);
DECLARE @dbname sysname;
DECLARE @recovery_model varchar(30);
DECLARE @sql nvarchar(4000);
DECLARE @backupfile nvarchar(1000);


SET @backup_path = 'E:\BACKUPS\'  -- Adjust for your environment (Include trailing backslash)


-- Determine if BACKUP COMPESSION is available based on SQL Server Version and Edition

DECLARE @CompressYN CHAR(1);
DECLARE @verinfoTbl TABLE
(
Verinfo sql_variant,
SPinfo sql_variant,
EditionInfo sql_variant
)
INSERT INTO @verinfoTbl (Verinfo, SPinfo, EditionInfo)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition');

IF (
-- Check for SQL 2012
-- Editions that support backup compression:
-- Enterprise, Business Intelligence, Standard
((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '11.%') AND 
(
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Business%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%')
)
) 
OR 
(
-- Check for SQL 2008 R2
-- Editions that support backup compression:
-- DataCenter, Enterprise, Standard
((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '10.50%') AND 
(
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Data%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%') OR
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Standard%')
)
) 
OR 
(
-- Check for SQL 2008
-- Editions that support backup compression:
-- Enterprise
((SELECT CONVERT(VARCHAR(16), Verinfo) FROM @verinfoTbl) LIKE '10.00%') AND 
(
((SELECT CONVERT(VARCHAR(16), EditionInfo) FROM @verinfoTbl) LIKE 'Enterprise%')
)
) 

BEGIN
SET @CompressYN = 'Y'
END
ELSE
---- BACKUP COMPRESSION not available
BEGIN
SET @CompressYN = 'N'
END 


-- Get the names of all qualifying databases

DECLARE c CURSOR FOR 

SELECT name
FROM sys.databases 
WHERE state_desc = 'ONLINE'
AND is_read_only <> 1
AND name NOT IN ('tempdb')
ORDER BY name;

OPEN c

FETCH NEXT FROM c INTO @dbname


-- Backup each database

WHILE (@@FETCH_STATUS <> -1)

BEGIN

       -- Create the backup output file name

    SET @backupfile = 

        -- Backup path
        @backup_path

        -- Add the db name
        +  @dbname

        -- Add the date and time to the file name
        + CONVERT (varchar, GETDATE(), 112) + '_'   -- date
        + REPLACE (LEFT (CONVERT (varchar, GETDATE(), 108), 5), ':', '_')   -- time

       BEGIN
              IF @CompressYN = 'Y'
              BEGIN
              SET @sql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @backupfile + '.BAK'' WITH INIT, COMPRESSION;'
              --PRINT @sql
              EXEC (@sql)
       END
       ELSE
       BEGIN
              SET @sql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @backupfile + '.BAK'' WITH INIT;'
              --PRINT @sql
              EXEC (@sql)
        END
END
              
    -- Move on to the next database

    FETCH NEXT FROM c INTO @dbname

END

CLOSE c
DEALLOCATE c

GO