Bad results from RESTORE FILELISTONLY

viacoboniviacoboni Posts: 13
edited May 3, 2006 11:45AM in SQL Backup Previous Versions
The Size and MaxSize columns should be numerics, not chars. AND they should definately not contain embedded null characters as they currently do. This has caused me no end of debugging woes...

Vince

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • viacoboniviacoboni Posts: 13
    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
    
  • aaronkaaaronka Posts: 1
    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
    
  • viacoboniviacoboni Posts: 13
    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.
  • peteypetey Posts: 2,358 New member
    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
Sign In or Register to comment.