How to get RESTORE FILELISTONLY resultset into a table?

Question

How to get RESTORE FILELISTONLY resultset into a table?

Leo Nosovsky

SSChasing Mays

Points: 633
More actions
February 14, 2008 at 2:07 pm

#120701

Does anyone know how to populate a table with the results of RESTORE FILELISTONLY for subsequent querying? Specifically, I need logical file names and their types from a db backup file.
Thanks,
Leo

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Leo Nosovsky (2/14/2008)
Does anyone know how to populate a table with the results of RESTORE FILELISTONLY for subsequent querying? Specifically, I need logical file names and their types from a db backup file.
Thanks,
Leo

Hello Leo,

Here is the logic. You can format as you like.

declare @path varchar(25)

create table #tmp

(

LogicalName nvarchar(128)

,PhysicalName nvarchar(260)

,Type char(1)

,FileGroupName nvarchar(128)

,Size numeric(20,0)

,MaxSize numeric(20,0),

Fileidtinyint,

CreateLSN numeric(25,0),

DropLSN numeric(25, 0),

UniqueID uniqueidentifier,

ReadOnlyLSN numeric(25,0),

ReadWriteLSN numeric(25,0),

BackupSizeInBytes bigint,

SourceBlocSize int,

FileGroupId int,

LogGroupGUID uniqueidentifier,

DifferentialBaseLSN numeric(25,0),

DifferentialBaseGUID uniqueidentifier,

IsReadOnly bit,

IsPresent bit

)

set @path = 'C:\Northwind.BAK'

insert #tmp

EXEC ('restore filelistonly from disk = ''' + @path + '''')

select * from #tmp

go

drop table #tmp

Lucky

Donn Polix Ten Centuries Points: 1393 More actions · Answer 2

create your temporary table..

INSERT #yourtemptable exec('restore filelistonly from disk=''....xl.bak''')

_____________
Donn Policarpio

Leo Nosovsky SSChasing Mays Points: 633 More actions · Answer 3

I found the same info here:

http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/32003/

I was hoping for something easier, but this will work. At least I didn't have to figure out column data types of the temp table myself 🙂

Thanks!

Leo

jdvanway Valued Member Points: 55 More actions · Answer 4

Thanks, Lucky!

In case anyone is copy-pasting after 16+ hours of chasing this monster, I wanted to let people know of a typo in the list.

SourceBlocSize int

is missing a 'k'. It should be

SourceBlockSize int

One more variable is missing:

TDEThumbprint varbinary(32)

So the script is (with a little green comment so that slow people like me can catch it) 😀

declare @path varchar(25)

create table #tmp

(

LogicalName nvarchar(128)

,PhysicalName nvarchar(260)

,Type char(1)

,FileGroupName nvarchar(128)

,Size numeric(20,0)

,MaxSize numeric(20,0),

FileId tinyint,

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,

TDEThumbprint varbinary(32)

)

set @path = 'C:\Northwind.BAK' -- File path @@@@@@

insert #tmp

EXEC ('restore filelistonly from disk = ''' + @path + '''')

select * from #tmp

go

drop table #tmp

The FILELISTONLY headers, should you spend an absurd amount of time chasing those as I did, are as follows:

BackupName nvarchar(128),

BackupDescription nvarchar(255),

BackupType smallint,

ExpirationDate datetime,

Compressed tinyint,

Position smallint,

DeviceType tinyint,

UserName nvarchar(128),

ServerName nvarchar(128),

DatabaseName nvarchar(128),

DatabaseVersion int,

DatabaseCreationDate datetime,

BackupSize numeric(20,0),

FirstLSN numeric(25,0),

LastLSN numeric(25,0),

CheckpointLSN numeric(25,0),

DatabaseBackupLSN numeric(25,0),

BackupStartDate datetime,

BackupFinishDate datetime,

SortOrder smallint,

CodePage smallint,

UnicodeLocaleId int,

UnicodeComparisonStyle int,

CompatibilityLevel tinyint,

SoftwareVendorId int,

SoftwareVersionMajor int,

SoftwareVersionMinor int,

SoftwareVersionBuild int,

MachineName nvarchar(128),

Flags int,

BindingID uniqueidentifier,

RecoveryForkID uniqueidentifier,

Collation nvarchar(128)

Brain2000 Old Hand Points: 363 More actions · Answer 5

This method seems absurd, yet it may be the only way.

The RESTORE FILELISTONLY columns change periodically, thus breaking scripts.

Is there a way to do a SELECT INTO?

mwest32 Valued Member Points: 73 More actions · Answer 6

for 2008 change the BackupSizeInBytes to bigint to get the older scripts to work.