Como o SSMS obtém uma lista de arquivos em um backup?

Aqui está um script simples que pode ajudá-lo.

Funciona para mim no SQL Server 2008 sp2

select @@version
--Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) 
--Sep 16 2010 19:43:16 
--Copyright (c) 1988-2008 Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

Parte 1:

--------------------------------------------------------------
-- first get the list of all backups in your server
--------------------------------------------------------------

declare @backup_type CHAR(1) = 'D' ;    --'D' full, 'L' log

with Radhe as (
    SELECT  @@Servername as [Server_Name],
        B.name as Database_Name, 
        ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 
               'NEVER') 
            as DaysSinceLastBackup,
        ISNULL(Convert(char(11), MAX(backup_finish_date), 113)
                   + ' ' 
                   + CONVERT(VARCHAR(8), MAX(backup_finish_date), 108), 
               'NEVER') 
            as LastBackupDate
       ,BackupSize_GB = CAST(COALESCE(MAX(A.BACKUP_SIZE), 0)
                                 /1024.00/1024.00/1024.00 
                             AS NUMERIC(18,2))
       ,BackupSize_MB = CAST(COALESCE(MAX(A.BACKUP_SIZE), 0)
                                 /1024.00/1024.00 
                             AS NUMERIC(18,2))
       ,media_set_id = MAX(A.media_set_id)
       ,[AVG Backup Duration] = AVG(CAST(DATEDIFF(s, A.backup_start_date, 
                                                    A.backup_finish_date) 
                                         AS int))
       ,[Longest Backup Duration] = MAX(CAST(DATEDIFF(s, A.backup_start_date, 
                                                         A.backup_finish_date)
                                             AS int))
       ,A.type
   FROM sys.databases B  
       LEFT OUTER JOIN msdb.dbo.backupset A 
           ON A.database_name = B.name 
           AND ( @backup_type IS NULL 
                 OR A.type = @backup_type )
   GROUP BY B.Name, A.type
)          
SELECT r.[Server_Name]
    ,r.Database_Name
    ,[Backup Type] = r.type 
    ,r.DaysSinceLastBackup
    ,r.LastBackupDate
    ,r.BackupSize_GB
    ,r.BackupSize_MB
    ,F.physical_device_name
    ,r.[AVG Backup Duration]
    ,r.[Longest Backup Duration]
FROM Radhe r
    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
        ON R.media_set_id = F.media_set_id
ORDER BY r.Server_Name, r.Database_Name ;

Parte 2:

--------------------------------------------------------------
-- now with any of the backup files listed above
-- you can add them in the place below and select from @fileListTable 
--------------------------------------------------------------

declare @fileListTable table
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
    FileID               bigint,
    CreateLSN            numeric(25,0),
    DropLSN              numeric(25,0),
    UniqueID             uniqueidentifier,
    ReadOnlyLSN          numeric(25,0),
    ReadWriteLSN         numeric(25,0),
    BackupSizeInBytes    bigint,
    SourceBlockSize      int,
    FileGroupID          int,
    LogGroupGUID         uniqueidentifier,
    DifferentialBaseLSN  numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32) -- remove this column if using SQL 2005
) ;

insert into @fileListTable 
    exec('restore filelistonly from disk = ''YourBackupFile.bak''') ;

select * from @fileListTable ;

Dê uma olhada nessas outras respostas também:

Como usar variáveis ​​com RESTORE FILELISTONLY

Conjunto de resultados “RESTORE FILELISTONLY” do SQL Server