Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
@JonSeigel Thanks, that worked out great. Can you put that in an answer so I can check it off? –  Craig Efrein Sep 3 '13 at 13:26
    
left(right(x.device_name, 21),17) as Backup_Device_Date does not look right to me as it is not a date .. should not it be something like Backup_Device_name + the precision is also wrong .. as it truncates the name as well ... –  Kin Sep 3 '13 at 13:49
    
@Kin Hi, yes using the string functions was a best effort attempt. Then there is also the fact that some of the dates are in US Style MM/DD/YY and others are in European style DD/MM/YY. That's why I added the x.backup_finish_date. –  Craig Efrein Sep 3 '13 at 13:56
add comment

1 Answer

up vote 1 down vote accepted

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);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.