Bad results from RESTORE FILELISTONLY

viacoboni

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

Brian Donahue

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.

viacoboni

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

aaronka

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

viacoboni

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.

petey

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