Return RESTORE FILELISTONLY Data to temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Return RESTORE FILELISTONLY Data to temp table

Author

Topic

ccundy
Starting Member

2 Posts

Posted - 2003-03-07 : 15:28:03

Is it possible to return the data that is return from a statement like this to a temp table?

RESTORE FILELISTONLY FROM DISK='C:\Data Files\MSSQL7\Backup\comp_db_db_200302190210.BAK'

I have tried this with system stored procedures and it seems to work fine. For example,

****************************************
Create Table #MyTempTable (
name varchar(200),
db_size varchar(200),
owner varchar(200),
dbid int,
created varchar(200),
status varchar (500))

insert #MyTempTable exec sp_helpdb

Select * From #MyTempTable

Drop Table #MyTempTable
****************************************
This works fine.

But when I try
****************************************
Create Table #MyTempTable (
LogicalName varchar(200),
PhysicalName varchar(200),
Type varchar(20),
FileGroupName varchar(100),
size int,
MaxSize float)

insert #MyTempTable
RESTORE FILELISTONLY FROM DISK='C:\Data Files\MSSQL7\Backup\comp_db_db_200302190210.BAK'

Select * From #MyTempTable

Drop Table #MyTempTable
****************************************

this doesn't work.

I understand that I am not doing the exact same thing since the one is a stored procedure and the other is a command.

Is there another way though?

I would like to be able to get the Logical Name from a file and store it in a variable. I figured the first step would be to put it in a temp table and select it out.

Does anyone have any suggestions?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-07 : 15:31:19

You almost had it:

insert #MyTempTable
EXECUTE('RESTORE FILELISTONLY FROM DISK=''C:\Data Files\MSSQL7\Backup\comp_db_db_200302190210.BAK'')')

If you execute the RESTORE as a string, it should work. Look in Books Online for more details on INSERT...EXECUTE.

ccundy
Starting Member

2 Posts

Posted - 2003-03-07 : 15:35:19

Hey robvolk, thanks a lot, that work exactly how I want it to. You did have an extra ) at the end but you solved my problem.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources