How to save resultset of RESTORE FILELISTONLY in temp table in SQL Server - SQL Server Tutorial


I got this task to read the backup files form a folder then then restore to new location. While was working on this task, needed to read the file names from .bak file. I need to move them to new location.

I can use RESTORE FILELISTONLY to get the information. As I need to save the information into variables and build restore query, I had to read this into a temp table.

Below query can be used to save the result set of RESTORE FILELISTONLY to temp table.

--Drop temp table if exits
IF OBJECT_ID('tempdb..#BackupFiles') IS NOT NULL
    DROP TABLE #BackupFiles

Declare @BackupFilePath VARCHAR(500)
SET @BackupFilePath='c:\Backup\ReportServer1_20160413_111603.bak'

--Create Temp Table
Create table #BackupFiles(
LogicalName NVARCHAR(128),
PhysicalName NVARCHAR(260),
Type CHAR(1),
FileGroupName NVARCHAR(128),
Size numeric(20,0),
MaxSize numeric(20,0),
Field 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,
IsReadOnly bit,
IsPresent bit,
TDEThumprint varbinary(32))
--Insert data from RESTORE FILELISTONLY insert into #BackupFiles ([LogicalName] ,[PhysicalName] ,[Type] ,[FileGroupName] ,[Size] ,[MaxSize] ,[Field] ,[CreateLSN] ,[DropLSN] ,[UniqueId] ,[ReadonlyLSN] ,[ReadWriteLSN] ,[BackupSizeInBytes] ,[SourceBlockSize] ,[FileGroupId] ,[LogGroupGUID] ,[DifferentialBaseLSN] ,[DifferentialBaseGUID] ,[IsReadOnly] ,[IsPresent] ,[TDEThumprint]) EXEC('RESTORE FILELISTONLY FROM DISK = '''+@BackupFilePath+'''') --Check the temp table if data is inserted Select * From #BackupFiles

I executed above query on different backup files and works great to get me the result set into temp table from RESTORE FILELISTONLY.

How to Insert SQL Server RESTORE FILELISTONLY Result set into Temp table - SQL Server Tutorial