This is a quick rewrite of the script I just posted a few days ago. The script had a few flaws if trying to run it on a SQL 2005 box. I have updated it to run against SQL 2005 and up.
This version is done using Dynamic SQL to help achieve some of the functionality that I wanted but while allowing the flexibility of the different SQL Versions.
You can see the old script here, shown as a part of investigating why differential backups were failing to restore.
DECLARE @SQLVer SQL_VARIANT
,@DBName VARCHAR(128)
,@NumDaysSMALLINT
,@SQLvarchar(1024)
,@WhereClauseVARCHAR(256)
SET @DBName = Null
;
SET @NumDays = 14
;
SET @SQLVer = CONVERT(INTEGER, PARSENAME(CONVERT(VARCHAR(20),SERVERPROPERTY('ProductVersion')),4));
SET @WhereClause = 'WHERE a.type IN (''D'',''I'')
And a.backup_start_date > GETDATE()- ' + Cast(@NumDays as varchar)+''
IF @DBName IS NOT NULL
Begin
SET @WhereClause = @WhereClause + '
AND a.database_name = '''+ @DBName +''''
END
Set @SQL = '
SELECT a.database_name,a.backup_start_date
,b.physical_device_name AS BackupPath
,a.position
,a.type
,a.backup_size/1024/1024 AS BackupSizeMB
,' + CASE
WHEN @SQLVer < 10
THEN '0'
ELSE 'a.compressed_backup_size/1024/1024'
END + ' AS CompressedBackMB
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
' + @WhereClause + '
ORDER BY a.database_name,a.backup_start_date;'
--PRINT @SQL
Execute (@SQL);
Running the above code, I get the following for one of my SQL instances.
