Hi
My requirement is as follows.
I have a backup file (full backup of SQL Server 2005 database), say TestDatabase.bak
I execute the following command to find the list of logical file names in my .bak file.
restore filelistonly from disk='H:\SSSOL001\Backup\TestDatabase.bak'
I would like to build a restore script from the results displayed from the above command , meaning have something like below :- In other words how can I select the LogicalName, PhysicalName from the restore filelist only command.
restore database TestDatabase1
from disk = 'H:\SSSOL001\Backup\TestDatabase.bak'
with
move
'Solumina_5100_4000_Data' to 'H:\SSSOL002\DATA\TestDatabase_Data.mdf',move
'sfadmin_data' to 'H:\SSSOL002\DATA\sfadmin_data.ndf',move
'Solumina_5100_4000_Log' to 'H:\SSSOL002\DATA\TestDatabase_Log.ldf',replace
Build dynamic restore script by executing restore filelistonly
Marcos Tito
Matt Lin
The only way I know how is to create a temp table, insert/exec, select
For example:
drop table backup_filelist
create table backup_filelist
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128) null,
Size numeric(20, 0),
MaxSize numeric(20, 0),
FileId int null,
-- Flags int null,
CreateLSN numeric(25,0) null,
DropLSN numeric(25,0) null,
UniqueFileId uniqueidentifier null,
readonlyLSN numeric(25,0) null,
readwriteLSN numeric(25,0) null,
BackupSizeInBytes bigint null,
SourceBlkSize int null,
FileGroupId int null,
LogGroupGuid uniqueidentifier null,
DifferentialBaseLsn numeric(25,0) null,
DifferentialBaseGuid uniqueidentifier null,
IsReadOnly bit null,
IsPresent bit null
)
go
declare @cmdstr varchar(255)
truncate table backup_filelist
select @cmdstr = 'restore filelistonly from disk=''s:\x.bak'''
insert into backup_filelist
exec (@cmdstr)
select * from backup_filelist
Hope that helps.