How to retrieve database backup, log backup file information.

Problem: How to see all database Files, log files from a backup set.

Solution: RESTORE FILELISTONLY (Transact-SQL)

Helps us to retrieve information of all the database, log files inside a backup set. This can be useful to restore when backing up only the files you want to from a comprehensive backup.

When databases are backed up using NOFORMAT option and specify the same path to backup or use the same file to backup for multiple databases, this will be very useful.

NOTE:

An optional WITH FILE is issued when you want a specific backup set number, if nothing is specified it gives the first backup set on the file.

Example:

In this article I am going to show you how we can retrieve database, logs information of backup set on a disk. 

I have backed up a database few times both differential and full to show you the difference between the WITH FILE and when nothing is specified.

Example 1:

-- Results of a backup

RESTORE FILELISTONLY FROM DISK = 'C:\Temp\Db.bak'

Example 2:

--Results using a backup_set_file_number

RESTORE FILELISTONLY FROM DISK = 'C:\Temp\Db.bak'

WITH FILE =3

OUTPUT:

IF you can see the output the differential GUIDs are different for the filelists.

Featured Script:

DECLARE @BackupLocation VARCHAR(512) = 'C:\Temp\Db.bak', -- your backup location here.

                @SqlServerVer DECIMAL(10, 2),

                @TableSQL VARCHAR(4000)

SET @SqlServerVer = (

                                SELECT CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 1, CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))) + 1) AS DECIMAL(10, 2))

                                )

--using Global temptable to create and access the table inside the query.

IF OBJECT_ID('tempdb..##FILELISTONLY') IS NOT NULL

                DROP TABLE ##FILELISTONLY

SET @TableSQL = + 'CREATE TABLE ##FILELISTONLY ('

SET @TableSQL = @TableSQL + ' LogicalName VARCHAR(64),'

SET @TableSQL = @TableSQL + ' PhysicalName VARCHAR(130),'

SET @TableSQL = @TableSQL + ' [Type] VARCHAR(1),'

SET @TableSQL = @TableSQL + ' FileGroupName VARCHAR(64),'

SET @TableSQL = @TableSQL + ' Size DECIMAL(20, 0),'

SET @TableSQL = @TableSQL + ' MaxSize DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' FileID bigint,'

SET @TableSQL = @TableSQL + ' CreateLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' DropLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' UniqueID UNIQUEIDENTIFIER,'

SET @TableSQL = @TableSQL + ' ReadOnlyLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' ReadWriteLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' BackupSizeInBytes DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' SourceBlockSize INT,'

SET @TableSQL = @TableSQL + ' filegroupid INT,'

SET @TableSQL = @TableSQL + ' loggroupguid UNIQUEIDENTIFIER,'

SET @TableSQL = @TableSQL + ' differentialbaseLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' differentialbaseGUID UNIQUEIDENTIFIER,'

SET @TableSQL = @TableSQL + ' isreadonly BIT,'

SET @TableSQL = @TableSQL + ' ispresent BIT'

IF @SqlServerVer > 10

                AND @SqlServerVer < 13

BEGIN

                SET @TableSQL = @TableSQL + ', TDEThumbprint VARBINARY(32)' -- This column is introduced for versions later than SQL server 2008 R2

                PRINT @SqlServerVer

END

IF @SqlServerVer > = 13

BEGIN

                SET @TableSQL = @TableSQL + ', SNAPSHOTURL NVARCHAR(36)' -- This column is introduced for versions later than SQL Server 2012

                PRINT @SqlServerVer

END

SET @TableSQL = @TableSQL + '  )'

EXEC (@TableSQL)

INSERT INTO ##FILELISTONLY

--WITH File=1 is specified when you want the First Backup Set.

EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupLocation + '''' + 'WITH FILE=1')

SELECT *

FROM ##FILELISTONLY

In the next article I will be showing, how to know when, where and who has backed up this database.