Backup History

Categories: Corner, News, Professional, Scripts, SSC
Comments: 5 Comments
Published on: November 30, 2012

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.

5 Comments - Leave a comment
  1. James Lean says:

    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 ;-)

  2. CodePro says:

    A snap of the output would be useful.

  3. AZJim says:

    Thank you. This is very clean and clear.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
November 2012
M T W T F S S
« Oct   Dec »
 1234
567891011
12131415161718
19202122232425
2627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, April 20, 2014