/* 1. stored procedure to fill msdb..restorelist_files */

USE [msdb]

IF object_id('ps_restore_filelist') IS NOT NULL
    drop procedure ps_restore_filelist
    go

CREATE PROCEDURE dbo.ps_restore_filelist
  @backup_path NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    RESTORE FILELISTONLY FROM DISK = @backup_path;
END
GO

/* 2. create table to store filelistpaths */
    IF OBJECT_ID('msdb..restorelist_files') IS NOT NULL DROP TABLE msdb..restorelist_files
    create table msdb..restorelist_files(
    LogicalName varchar(255),
    PhysicalName varchar(255),
    Type varchar(255),
    FileGroupName varchar(255),
    Size varchar(255),
    MaxSize varchar(255),
    FileId varchar(255),
    CreateLSN varchar(255),
    DropLSN varchar(255),
    UniqueId varchar(255),
    ReadOnlyLSN varchar(255),
    ReadWriteLSN varchar(255),
    BackupSizeInBytes varchar(255),
    SourceBlockSize varchar(255),
    FileGroupId varchar(255),
    LogGroupGUID varchar(255),
    DifferentialBaseLSN varchar(255),
    DifferentialBaseGUID varchar(255),
    IsReadOnly varchar(255),
    IsPresent varchar(255),
    TDEThumbprint varchar(255))

/* 3. fill table with files and their paths */
TRUNCATE TABLE msdb..restorelist_files
GO

DECLARE @BACKUP_PATH as nvarchar(max) = N'Z:BACKUPSMY_DATABASE_BACKUP.bak'

/* execute stored procedure that will recover list of files for this backup */
insert into msdb..restorelist_files
exec ps_restore_filelist @BACKUP_PATH

/* create the MOVE TO portion of the restore database statement using the new path name based on the service/instance name */
DECLARE @instance_name as varchar(255) = @@SERVICENAME
DECLARE @path_name as varchar(255) = 'MSSQL10_50.' + @instance_name + 'DATA'
DECLARE @DB as varchar(50) = 'STAGING_DB'
DECLARE @SQL_RESTORE as nvarchar(max)

SELECT  'MOVE N''' + LogicalName +  '''TO N''' + LEFT(physicalname, CHARINDEX('.', REVERSE(physicalname)) - 1)  +
             @path_name +
       REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('', REVERSE(physicalname), 1) - 1))  + ''','
from msdb..restorelist_files

MOVE N'MY_DATABASE'TO N'D:MSSQL10_50.INSTANCEDATAMY_DATABASE.mdf',
MOVE N'MY_DATABASE_1'TO N'E:MSSQL10_50.INSTANCEDATAMY_DATABASE_1.ndf',
MOVE N'MY_DATABASE_2'TO N'F:MSSQL10_50.INSTANCEDATAMY_DATABASE_2.ndf',
MOVE N'MY_DATABASE_3'TO N'G:MSSQL10_50.INSTANCEDATAMY_DATABASE_3.ndf',
MOVE N'MY_DATABASE_4'TO N'H:MSSQL10_50.INSTANCEDATAMY_DATABASE_4.ndf',
MOVE N'MY_DATABASE_5'TO N'I:MSSQL10_50.INSTANCEDATAMY_DATABASE_5.ndf',
MOVE N'MY_DATABASE_6'TO N'J:MSSQL10_50.INSTANCEDATAMY_DATABASE_6.ndf',
MOVE N'MY_DATABASE_7'TO N'K:MSSQL10_50.INSTANCEDATAMY_DATABASE_7.ndf',
MOVE N'MY_DATABASE_log'TO N'D:MSSQL10_50.INSTANCEDATAMY_DATABASE_8.ldf',
MOVE N'MY_DATABASE_log_1'TO N'E:MSSQL10_50.INSTANCEDATAMY_DATABASE_9.ldf',
MOVE N'MY_DATABASE_log_2'TO N'F:MSSQL10_50.INSTANCEDATAMY_DATABASE_10.ldf',
MOVE N'MY_DATABASE_log_3'TO N'G:MSSQL10_50.INSTANCEDATAMY_DATABASE_11.ldf',
MOVE N'MY_DATABASE_log_4'TO N'H:MSSQL10_50.INSTANCEDATAMY_DATABASE_12.ldf',
MOVE N'MY_DATABASE_log_5'TO N'I:MSSQL10_50.INSTANCEDATAMY_DATABASE_13.ldf',
MOVE N'MY_DATABASE_log_6'TO N'J:MSSQL10_50.INSTANCEDATAMY_DATABASE_14.ldf',
MOVE N'MY_DATABASE_log_7'TO N'K:MSSQL10_50.INSTANCEDATAMY_DATABASE_15.ldf',