Twitter Updates

    follow me on Twitter

    Tuesday, May 13, 2008

    SQL Server 2008 - calculating backup ratios

    Backup compression is one of the coolest new features in SQL Server 2008. This nice little query gives you an overview of the average compression ratios. I used another T-SQL enhancement (GROUPING SETS) to show the ratio per database, backup type, ...

    DatabaseName =ISNULL(database_name,'(All Databases)'),
    BackupType =ISNULL(CAST(type AS VARCHAR(15)), '(All Types)'),
    CompressionRatio = AVG( CAST(backup_size/ compressed_backup_size AS DECIMAL(5,2)))
    from msdb..backupset
    WHERE (backup_size <> compressed_backup_size)
    GROUP BY GROUPING SETS((database_name,type), (database_name), (type), ())
    ORDER BY 1,2

    Check out these excelent blogposts for more info on backup compression:

    Paul Randal - SQL Server 2008: Backup Compression CPU Cost

    SQLCAT - Tuning the Performance of Backup Compression in SQL Server 2008<

    No comments: