Blog Post

Backup History

,

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating