Odd Insert question...

Last Post 25 Jul 2008 11:53 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
leeland
New Member
New Member

--
25 Jul 2008 09: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
BEGIN
SELECT @RestoreFileName = MIN(DBName)
FROM DBName
WHERE DBName > @RestoreFileName

IF @RestoreFileName IS NOT NULL
BEGIN
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 + '''')
END
END

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.

Thanks,

Lee
SQLUSA
New Member
New Member

--
25 Jul 2008 11:53 PM
Lee,

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/
You are not authorized to post a reply.

Acceptable Use Policy