What are the challenges you face when working across database platforms? Take the survey
Options

RESTORE FILELISTONLY data types

matt.stanfordmatt.stanford Posts: 11
edited November 29, 2007 1:03PM in SQL Backup Previous Versions
I've been having trouble guessing the data types for the result set of the RESTORE FILELISTONLY result set. Can anyone shed some light on this?

I'm not using all of the columns, so I defaulted to VARCHAR(100) when I wasn't sure. Here are my guesses:
CREATE TABLE #files (
	LogicalName				VARCHAR	(256)
	,PhysicalName			VARCHAR	(512)
	,Type					VARCHAR	(10)
	,FileGroupName			VARCHAR	(100)
	,Size					VARCHAR(100)
	,MaxSize				VARCHAR(100)
	,FileID					BIGINT
	,CreateLSN				VARCHAR(100)
	,DropLSN				VARCHAR(100)
	,UniqueID				VARCHAR(100)
	,ReadOnlyLSN			VARCHAR(100)
	,ReadWriteLSN			VARCHAR(100)
	,BackupSizeInBytes		VARCHAR(100)
	,SourceBlockSize		VARCHAR(100)
	,FileGroupID			VARCHAR(100)
	,LogGroupGUID			VARCHAR(100)
	,DifferentialBaseLSN	VARCHAR(1000)
	,DifferentialBaseGUID	VARCHAR(100)
	,IsReadOnly				TINYINT
	,IsPresent				TINYINT
)

SET @SQL = N'-SQL "RESTORE FILELISTONLY FROM DISK = [C:\db.sqb]" '

INSERT INTO #files
EXECUTE MASTER.dbo.sqlbackup @SQL
Thanks,
- Matt

Comments

  • Options
    peteypetey Posts: 2,358 New member
    The data types are identical to that returned by the native RESTORE FILELISTONLY command (see here), except for the following:

    - UniqueID, LogGroupID and DifferentialBaseGUID are varchar(36) instead of GUIDs

    - IsReadOnly and IsPresent are integers instead of bits

    The reason for the discrepancy is because the SQL Server extended stored procedure library does not support GUID and bit types.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Very nice. Thank you.

    -Matt
Sign In or Register to comment.