View backup file details with TSQL

In order to automate testing of backups it’s useful to be able to query backup files to access various bits of meta-data. We can do this with the RESTORE FILELISTONLY TSQL command. In the simplest format the command is as follows;

RESTORE FILELISTONLY
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\AdventureWorks_20110320.bak';

This doesn’t help us much with automation. We need to get this resultset into a table so we can make use of the information it provides in our stored procedures. We can do this with a bit of trickery with the EXECUTE command.

-- Define a table variable to hold our resultset
-- Will only be accessible in the current batch.
-- Consider using a temp table for other needs
DECLARE @filelist TABLE
(
	LogicalName NVARCHAR(128) NOT NULL PRIMARY KEY CLUSTERED,
	PhysicalName NVARCHAR(260) NOT NULL,
	[Type] CHAR(1) NOT NULL,
	FileGroupName NVARCHAR(128) NULL,
	Size NUMERIC(20,0) NOT NULL,
	MaxSize NUMERIC(20,0) NOT NULL,
	FileId BIGINT NOT NULL,
	CreateLSN NUMERIC(25,0) NOT NULL,
	DropLSN NUMERIC(25,0) NULL,
	UniqueId UNIQUEIDENTIFIER NOT NULL,
	ReadOnlyLSN NUMERIC(25,0) NULL,
	ReadWriteLSN NUMERIC(25,0) NULL,
	BackupSizeInBytes BIGINT NOT NULL,
	SourceBlockSize INT NOT NULL,
	FileGroupId INT NOT NULL,
	LogGroupGUID UNIQUEIDENTIFIER NULL,
	DifferentialBaseLSN NUMERIC(25,0) NULL,
	DifferentialBaseGUID UNIQUEIDENTIFIER NOT NULL,
	IsReadOnly BIT NOT NULL,
	IsPresent BIT NOT NULL,
	TDEThumbprint VARBINARY(32) NULL
);

-- Insert the data from the RESTORE FILELISTONLY command
-- into our table variable
INSERT INTO @filelist
EXECUTE
(
	'RESTORE FILELISTONLY
	FROM DISK = ''c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\AdventureWorks_20110320.bak'''
);

-- Show that the table variable contains the data
SELECT *
FROM @filelist;