Bad results from RESTORE FILELISTONLY

• 
  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.

  Brian Donahue March 09, 2006 13:21

  - 0 +
• 
  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
  

  viacoboni March 09, 2006 13:37

  - 0 +
• 
  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
  

  aaronka April 02, 2006 23:46

  - 0 +
• 
  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.

  viacoboni April 03, 2006 07:53

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

  petey May 03, 2006 11:45

  - 0 +

Add comment

Please sign in to leave a comment.