Odd Insert question...

Last Post 26 Jul 2008 12:53 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

25 Jul 2008 10:37 AM
I have an odd request and am curious if anyone can tell me if it is possible or not.

I want to extract the metadata from RESTORE FILELISTONLY FROM DISK = 'servername\folder\backup.back' and enter it into a temp table...

I have that part figured out...

However I also wanted to have a column JUST for the db name and I can't figure out if it is possible to pull it off in one INSERT statement or not

So this code will loop through a directory and extract the backup files metadata...

-- gets backup file metadata
DECLARE @RestoreFileName VARCHAR(50)
SET @RestoreFileName = ''

WHILE @RestoreFileName IS NOT NULL
SELECT @RestoreFileName = MIN(DBName)
WHERE DBName > @RestoreFileName

IF @RestoreFileName IS NOT NULL
SELECT @sql_cmd = '' + @BackupRestoreNetPath + RTRIM(@RestoreFileName)
FROM DirectoryListing
WHERE LOWER(DirectoryInfo) LIKE '%.bak'
INSERT INTO DB_Restore_MetaData EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @sql_cmd + '''')

SELECT * FROM DB_Restore_MetaData

however I wanted to add a column "DB_NAME" to house just the DB_name and I can't figure out if I can get it to work in a single insert statement...

Can this statement be modified to allow for an additional variable to be added holding the DB_NAME?

INSERT INTO DB_Restore_MetaData EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @sql_cmd + '''')

If there is anyone who can comment I would greatly appreciate it.


New Member
New Member

26 Jul 2008 12:53 AM

There is no way to get more action out of INSERT...EXEC...

As Andy said, you can just update the database name. Question, how do you get it from this info?

LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint
AdventureWorks2008_Data F:\SQL2008\data\AdventureWorks2008.mdf D PRIMARY 204210176 3.51844E+13 1 0 0 B1B1B5DA-F8C5-41BE-A6D5-9113AB17B0AB 0 0 189202432 512 1 NULL 4.6E+16 16B2E624-FF76-44ED-A3B8-ED36AA50D261 0 1 NULL
AdventureWorks2008_Log F:\SQL2008\log\AdventureWorks2008_log.ldf L NULL 2097152 2.19902E+12 2 0 0 45117637-748A-4D57-B07D-42D4B9AD8061 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL
FileStreamDocuments F:\SQL2008\data\Documents S DocumentFileStreamGroup 288198 0 65537 1.7E+16 0 35B25C7B-A7CB-49CA-B729-DBA50A1FDBE8 0 0 327680 512 2 NULL 4.6E+16 16B2E624-FF76-44ED-A3B8-ED36AA50D261 0 1 NULL

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/

Acceptable Use Policy