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)
,@NumDays SMALLINT
,@SQL VARCHAR(1024)
,@WhereClause VARCHAR(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.
Thanks for the update Jason. Shame there’s no way of doing it without resorting to dynamic SQL. Maybe future versions will do late binding down to the statement level
I know what you mean. I was reluctant to go the dynamic sql route but sometimes it is ok to use.
A snap of the output would be useful.
Good point. I will post a snap to the original blog.
Thank you. This is very clean and clear.