Hi Sue,

FYI  here is my script quick and dirty  ..put it in your toolbox..didn't need it,  but did the work anyway

--============================================================================

-- Restore file list from from bak file to different paths

-- table to hold file list

DECLARE @tmpFileList TABLE

(

LogicalName NVARCHAR(128)

, PhysicalName NVARCHAR(260)

, Type CHAR(1)

, FileGroupName NVARCHAR(128)

, Size NUMERIC(20, 0)

, MaxSize NUMERIC(20, 0)

, Fileid TINYINT

, CreateLSN NUMERIC(25, 0)

, DropLSN NUMERIC(25, 0)

, UniqueID UNIQUEIDENTIFIER

, ReadOnlyLSN NUMERIC(25, 0)

, ReadWriteLSN NUMERIC(25, 0)

, BackupSizeInBytes BIGINT

, SourceBlockSize INT

, FileGroupId INT

, LogGroupGUID UNIQUEIDENTIFIER

, DifferentialBaseLSN NUMERIC(25, 0)

, DifferentialBaseGUID UNIQUEIDENTIFIER

, IsReadOnly BIT

, IsPresent BIT

, TDEThumbptint VARBINARY

)

DECLARE @debug int = 0

DECLARE @restoreFromFileList varchar(8000)

DECLARE @quote varchar(10) = ''''

DECLARE @bakFilePath varchar(4000) = 'C:\RobinWork\Backups\dbBackup\EDW.bak'

set @restoreFromFileList = 'RESTORE FILELISTONLY FROM DISK = '+ @quote + @bakFilePath + + @quote

IF @debug =1

print @restoreFromFileList

-- insert into temp table

INSERT INTO @tmpFileList EXEC (@restoreFromFileList)

IF @debug =1

select * from @tmpFileList

DECLARE @dbName varchar(255) = 'mydb'

DECLARE @restoredb varchar(8000)

DECLARE @mdfLogicalName varchar(255)

DECLARE @mdfPhysicalname varchar(255) = 'C:\temp\' + @dbName + '.mdf'

DECLARE @ldfLogicalName varchar(255)

DECLARE @ldfPhysicalname varchar(255) = 'C:\temp\' + @dbName + '_log.ldf'

SELECT @mdfLogicalName = logicalname

FROM @tmpFileList

WHERE physicalname LIKE '%.mdf'

SELECT @ldfLogicalName = logicalname

FROM @tmpFileList

WHERE physicalname LIKE '%.ldf'

if @debug =1

BEGIN

print @mdfLogicalName

print @mdfPhysicalname

print @ldfLogicalName

print @ldfPhysicalname

END

SET @restoredb = ''

SET @restoredb = @restoredb + ' RESTORE DATABASE ['+ @dbName + ']'

SET @restoredb = @restoredb + ' FROM DISK = '+ @quote + @bakFilePath + @quote

SET @restoredb = @restoredb + ' WITH MOVE '+ @quote + @mdfLogicalName + @quote + ' TO '+ @quote + @mdfPhysicalname + @quote + ','

SET @restoredb = @restoredb + ' MOVE '+ @quote + @ldfLogicalName + @quote + ' TO '+ @quote + @ldfPhysicalname + @quote + ','

if @debug=1

print @restoredb

-- table to hold ndf files

DECLARE @tmpNDFFiles TABLE

(

LogicalName varchar(255)

, Physicalname varchar(255)

, rowid int identity

)

INSERT @tmpNDFFiles(LogicalName,Physicalname)

SELECT LogicalName , PhysicalName

FROM @tmpFileList

WHERE physicalname LIKE '%.ndf'

if @debug=1

select * from @tmpNDFFiles

DECLARE @restoreNDF varchar(8000) = ''

DECLARE @counter int

DECLARE @ndfLogicalName varchar(255)

DECLARE @ndfPhysicalPath varchar(255) = 'c:\temp'

SET @counter = 0

WHILE @counter < (SELECT COUNT(*) from @tmpNDFFiles)

BEGIN

SET @counter = @counter + 1

SELECT @ndfLogicalName = logicalname

FROM @tmpNDFFiles

WHERE rowid = @counter

SET @restoreNDF = @restoreNDF + ' MOVE '+ @quote + @ndfLogicalName + @quote + ' TO '+ @quote + @ndfPhysicalPath + '\' + @ndfLogicalName + '.ndf' + @quote + ',' + CHAR(13) + CHAR(10)

END

-- Add the tail

SET @restoreNDF = @restoreNDF + ' REPLACE, STATS = 20 '

if @debug= 1

print @restoreNDF

DECLARE @totalRestore varchar(max) = @restoredb + @restoreNDF

print @totalRestore

exec(@totalRestore)