SSMS 如何获取备份中的文件列表？

这是一个可能对您有所帮助的简单脚本。

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

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

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

也看看这些其他答案：

如何通过 RESTORE FILELISTONLY 使用变量

SQL Server“RESTORE FILELISTONLY”结果集