Bad results from RESTORE FILELISTONLY

Comments

• Brian Donahue Posts: 6,590 Bronze 1

  March 9, 2006 1:21PM

  Hello Vince,
  
  Are you sure this is version 4? AFAIK the NULLs are taken out of the output now that used to be present in the SQL Backup v3 output.

  0 · Share on Twitter
• viacoboni Posts: 13

  March 9, 2006 1:37PM edited August 1, 2017 2:45PM

  Run the following code, substituting your filename in the exec statement.
  
  

  CREATE TABLE #FileListOutputSQB (LogicalName nvarchar(128), 
                                   PhysicalName nvarchar(260),
                                   Type char(1),
                                   FileGroupName nvarchar(128) NULL, 
                                   Size varchar(20) NULL,
                                   MaxSize varchar(20) NULL,
                                   RowNum int identity(1,1))          
  
  insert #FileListOutputSQB
  exec master.dbo.sqlbackup '-I -E -SQL "RESTORE FILELISTONLY FROM DISK=''e:\mssql\backup\admin\Admin_db_200603070200.SQB''"'
  
  select size from #FileListOutputSQB where rownum = 1
  
  declare @i int
  set @I = 1
  while @I <= 20
  begin
    select 'pos = ' + str(@i,2) + ', ascii = ' + str(ascii(substring(size,@i,1)),3) from #filelistoutputsqb where rownum = 1
    set @I = @I + 1
  end
  

  0 · Share on Twitter
• aaronka Posts: 1

  April 2, 2006 11:46PM edited August 1, 2017 2:45PM

  Taking your example of how to show that the FILELISTONLY is returning garbage, I was able to create a workaround. I was concerned with getting the logical file name, so that is what my example returns. This is very frustrating that I have to clean up a practically unusable resultset. :x
  

  CREATE TABLE #FileListOutputSQB (LogicalName nvarchar(128), 
  PhysicalName nvarchar(260), 
  Type char(1), 
  FileGroupName nvarchar(128) NULL, 
  Size varchar(20) NULL, 
  MaxSize varchar(20) NULL, 
  RowNum int identity(1,1)) 
  
  insert #FileListOutputSQB 
  exec master.dbo.sqlbackup '-I -E -SQL "RESTORE FILELISTONLY FROM DISK=''e:\mssql\backup\admin\Admin_db_200603070200.SQB''"' 
  
  select '"' + LogicalName + '"' from #FileListOutputSQB where Type = 'D'
  
  declare @i int
  declare @ascii int
  declare @DataLogicalName varchar(128)
  set @DataLogicalName = ''
  set @I = 1 
  while @I <= 128 
  begin 	
  	select @ascii = ascii(substring(logicalname,@i,1)) from #FileListOutputSQB where Type = 'D'
  	if @ascii > 0		
  		select @DataLogicalName = @DataLogicalName + substring(logicalname,@i,1) from #FileListOutputSQB where Type = 'D'
  	
  set @I = @I + 1 
  end
  
  select '"' +  @DataLogicalName + '"' 
  
  drop table #FileListOutputSQB
  

  0 · Share on Twitter
• viacoboni Posts: 13

  April 3, 2006 7:53AM edited August 1, 2017 2:46PM

  That looks like it would work. Slightly quicker:
  

  select @logicalname = substring(logicalname, 1, charindex(char(0), logicalname)-1) from #FileListOutputSQB where Type = 'D'
  

  
  That code assumes there is always a char(0) on the logicalname col.

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  May 3, 2006 11:45AM

  Version 4.2 outputs the Size and MaxSize columns as numeric(20,0) types, so you might need to adjust your script a little.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter