July 25, 2008 at 11:49 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 perm "temp" table...
I have that part figured out and working...
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
Here is the code I have that creates the tables and inserts the data regarding the .bak files
-- DBName table holds all the metadata about the backups
--------------------------------------------------------
IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'[DBName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE DBName
CREATE TABLE DBName(
Database_IDINT IDENTITY(1,1),
DBNameVARCHAR(30),
StartTimeDATETIME,
EndTimeDATETIME,
TotalTimeInSecINT
)
-- DirectoryListing table houses the backup file names used for the restore process
-----------------------------------------------------------------------------------
IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'[DirectoryListing]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE DirectoryListing
CREATE TABLE DirectoryListing
(
DirectoryInfo VARCHAR(2000)
)
-- Restore File medatadata
--------------------------
IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'[DB_Restore_MetaData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE DB_Restore_MetaData
CREATE TABLE DB_Restore_MetaData
(
LogicalName nvarchar(128), -- Logical name of the file
PhysicalName nvarchar(260), -- Physical or operating-system name of the file
Type char(1), -- Data file (D) or a log file (L)
FileGroupName nvarchar(128), -- Name of the filegroup that contains the file
Size numeric(20,0), -- Current size in bytes
MaxSize numeric(20,0) -- Maximum allowed size in bytes
)
DECLARE @RestoreFileName VARCHAR(50)
DECLARE @BackupRestoreNetPathVARCHAR(100)
SET @BackupRestoreNetPath = '\\ServerName\FolderName\'
SET@RestoreFileName = ''
-- gets file listing of the source db restore directory and loads the backup file names into a table
----------------------------------------------------------------------------------------------------
SELECT @sql_cmd = 'DIR /B ' + @BackupRestoreNetPath
INSERT INTO DirectoryListing EXEC master.dbo.xp_cmdshell @sql_cmd
-- inserts the backup file names into the DBName table
------------------------------------------------------
INSERT INTO DBName(DBName)
SELECTDirectoryInfo
FROM DirectoryListing
WHERELOWER(DirectoryInfo) LIKE '%.bak'
-- gets backup file metadata
----------------------------
WHILE@RestoreFileName IS NOT NULL
BEGIN
SELECT @RestoreFileName = MIN(DBName)
FROMDBName
WHEREDBName > @RestoreFileName
IF@RestoreFileName IS NOT NULL
BEGIN
SELECT@sql_cmd = '' + @BackupRestoreNetPath + RTRIM(@RestoreFileName)
FROM DirectoryListing
WHERELOWER(DirectoryInfo) LIKE '%.bak'
INSERT INTO DB_Restore_MetaData EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @sql_cmd + '''')
END
END
SELECT * FROM DB_Restore_MetaData
I want to add a column "DB_NAME" to the DB_Restore_MetaData table 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 + '''')
so that it would look something like this?
INSERT INTO DB_Restore_MetaData (db_name, LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize)
VALUES (@db_name, (EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @sql_cmd + '''')))
If there is anyone who can comment I would greatly appreciate it.
Thanks,
Lee
July 25, 2008 at 12:49 pm
You might be able to achieve that with the OpenRowset function wrapped around the exec command. Check that out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply