4

When restoring a database that has multiple files, SSMS gives you the option to restore as other paths and file names. How does it retrieve the list of files from the backup? Can I do that in a SQL script? C#? PS?

4

Here is a simple script that might help you.

Works for me on 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)

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

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

Have a look at these other answers too:

How to use variables with RESTORE FILELISTONLY

SQL Server “RESTORE FILELISTONLY” Resultset

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.