Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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)
		,@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.

Comments

Leave a comment on the original post [jasonbrimhall.info, opens in a new window]

Loading comments...