Need Return Valre from <<restore filelistonly from backupdevice>>

[DLu]

When moving around 800 databases from 40 MSDE servers to on STD SQL2k server,
I need the logicalfile name info in order to run the script to restore from backup device. The only place I could get from the target server is the backup device(which are copied from 40 MSDE to STD). It works fine and retuns value from "restore filelistonly from backup_Testing".

But fails when I tried to put the result into a table variable or a temp #Table.

declare @tableFileList
table (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FileGroupName nvarchar(128), Size numeric(20,0), MaxSize numeric(20,0))

insert into @tableFileList
restore filelistonly from backup_Testing

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'restore'.

thanks
David

[skhanal]

Try

insert into #tableFileList
exec ("restore filelistonly from backup_Testing")

I think table variable does not work with exec (not sure, I don't have access to a server to try now)

[MAK]

you get "incorrect syntax" because of your quoted identifier.

do this

set quoted_identifier off


Create table #tableFileList
(LogicalName nvarchar(128) NULL,
PhysicalName nvarchar(260) NULL,
Type char(1) NULL,
FileGroupName nvarchar(128) NULL,
Size numeric(20,0) NULL,
MaxSize numeric(20,0) NULL)

insert #tableFileList
exec ("restore filelistonly from disk = 'd:\snar.bak'")

select * from #tableFilelist

drop table #tableFileList

[skhanal]

Or put restore .. command in a variable and run

insert into #tableFileList
exec (@cmdstr)

[DLu]

the table variable did not work, as expected:
[Server: Msg 197, Level 15, State 1, Line 17
EXECUTE cannot be used as a source when inserting into a table variable.]
--====================================
declare @backupDevice varchar(30),
@sqlString500 nvarchar(500),
@returnCode int
select @backupDevice='backup_Testing'
create table #tableFileList
(LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0))

select @sqlString500 = 'restore filelistonly from ' + @backupDevice
insert into #tableFileList
exec @returnCode= sp_executesql @sqlString500
print '@returnCode in sp_executesql @sqlString500--' + cast(@returnCode as varchar(5))
select * from #tableFileList
drop table #tableFileList

So, the lesson to me is always use exec sp_executesql (with nvarchar()), when I want to save the returning value from system proc/fun.

Thanks for MAK and SKhanal. Guess you might work or came from M.S. SQL development team before? just kidding...

David

[MAK]

u r welcome.

BTW. for function you dont need to use sp_executesql, function always returns value.
nvarchar is used only for unicode values.

[DLu]

related issue:
since it is a temp table #Test, is there a way to do something like:
--================================
if exists(select * from sysobjects where name = '#test')
begin
drop table #Test
end

thanks
David

[MAK]

create the whole code in a procedure so you dont have to delete it. soon after the procedure gets executed it deletes the table automatically. temptable stays in tempdb until that session is completed. it will be deleted automatically. Check BOL for "temporary tables". detail explanation of temp tables are there.

or

you can check and delete in tempdb for similar files. (Dangerous)

select * from tempdb..sysobjects where name like '#x%'

[MAK]

create table #x (id int)

select object_id(N'tempdb.dbo.[#x]')


if exists(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.[#x]'))
print 'exist'