Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get RESTORE FILELISTONLY resultset into a table? Expand / Collapse
Author
Message
Posted Thursday, February 14, 2008 2:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 11, 2013 1:13 PM
Points: 27, Visits: 57
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
Post #455993
Posted Thursday, February 14, 2008 2:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 11:13 AM
Points: 1,295, Visits: 796
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),
Fileid tinyint,
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
Post #456005
Posted Thursday, February 14, 2008 2:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:52 AM
Points: 89, Visits: 995
create your temporary table..
INSERT #yourtemptable exec('restore filelistonly from disk=''....xl.bak''')


_____________
Donn Policarpio
Post #456011
Posted Thursday, February 14, 2008 3:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 11, 2013 1:13 PM
Points: 27, Visits: 57
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
Post #456019
Posted Thursday, January 19, 2012 12:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 24, 2012 10:50 PM
Points: 1, Visits: 8
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)

Post #1238465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse