Check for column in backupset table

Question

Created a script to show last backup locations, sizes and dates

SELECT DatabaseName = x.database_name,
        LastBackupDate = x.backup_finish_date,
        BackupSizeKB = x.backup_size,
        LastBackupFileName = x.device_name,
        left(right(x.device_name, 21),17) as Backup_Device_Date,
        type = x.type
        FROM (  SELECT  bs.database_name, bs.type,
                case 
                    when exists(
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
                    )
                    then cast(round(max(bs.compressed_backup_size/1024),0) as numeric)
                    else cast(round(max(bs.backup_size/1024),0) as numeric)
                end as backup_size,
                max(bs.backup_finish_date) as backup_finish_date,
                max(bmf.physical_device_name) as device_name
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.is_copy_only = 0
          GROUP BY bs.type, bs.database_name) x
WHERE x.database_name not in ('master', 'model', 'msdb')
AND x.type IN ('D')
ORDER BY DatabaseName;

The only problem, is that I get the following error when its executed on a SQL Server 2005 Enterprise 9.00.3042 64bit

Msg 207, Level 16, State 1, Line 12 Invalid column name 'compressed_backup_size'.

If I change the then/else to

case 
when exists(
 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
)
then 1
else 0
end as backup_size

I get 1 or 0 for backup_size depending on whether *compressed_backup_size* exists in backupset.

database_name   type    backup_size backup_finish_date  device_name
model   I   0   2013-09-02 18:30:19.000 Data Protector_(DEFAULT)_model_163022971_0
msdb    I   0   2013-09-02 18:30:22.000 Data Protector_(DEFAULT)_msdb_163022971_0

So in spite of using CASE WHEN THEN ELSE to check for the existence of *compressed_backup_size*, I'm still getting an error. Any thoughts on why the THEN argument is being parsed when the column *compressed_backup_size* clearly doesn't exist ?

Thanks in advance

Answer 1

You have to use dynamic SQL to hide that column from the parser in 2005. All columns are checked to make sure they exist before the query runs.

DECLARE @sql nvarchar(MAX);

SET @sql = N'
SELECT DatabaseName = x.database_name,
        LastBackupDate = x.backup_finish_date,
        BackupSizeKB = x.backup_size,
        LastBackupFileName = x.device_name,
        left(right(x.device_name, 21),17) as Backup_Device_Date,
        type = x.type
        FROM (  SELECT  bs.database_name, bs.type,' +
                case 
                    when exists(
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
                    )
                    then N'cast(round(max(bs.compressed_backup_size/1024),0) as numeric)'
                    else N'cast(round(max(bs.backup_size/1024),0) as numeric)'
                end + N' as backup_size,
                max(bs.backup_finish_date) as backup_finish_date,
                max(bmf.physical_device_name) as device_name
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.is_copy_only = 0
          GROUP BY bs.type, bs.database_name) x
WHERE x.database_name not in (''master'', ''model'', ''msdb'')
AND x.type IN (''D'')
ORDER BY DatabaseName;';

EXEC(@sql);