Restore database script to another database (name), LogicalName lookup included.

-- (1) Our configuration
DECLARE @BackupFile nvarchar(max), @RestoreDB nvarchar(max);
SET @BackupFile = N'C:\manual_backups\MyDatabase1.bak';
SET @RestoreDB = N'MyDatabase2';

-- (2) Get the backup content LogicalNames
DECLARE @FileList TABLE (
	LogicalName nvarchar(128) NOT NULL,
	PhysicalName nvarchar(260) NOT NULL,
	Type char(1) NOT NULL,
	FileGroupName nvarchar(120) NULL,
	Size numeric(20, 0) NOT NULL,
	MaxSize numeric(20, 0) NOT NULL,
	FileID bigint NULL,
	CreateLSN numeric(25,0) NULL,
	DropLSN numeric(25,0) NULL,
	UniqueID uniqueidentifier NULL,
	ReadOnlyLSN numeric(25,0) NULL ,
	ReadWriteLSN numeric(25,0) NULL,
	BackupSizeInBytes bigint NULL,
	SourceBlockSize int NULL,
	FileGroupID int NULL,
	LogGroupGUID uniqueidentifier NULL,
	DifferentialBaseLSN numeric(25,0)NULL,
	DifferentialBaseGUID uniqueidentifier NULL,
	IsReadOnly bit NULL,
	IsPresent bit NULL,
	TDEThumbprint varbinary(32) NULL
);

DECLARE @RestoreStatement nvarchar(max);
SET @RestoreStatement =  N'RESTORE FILELISTONLY FROM DISK=N''' + @BackupFile + ''''
INSERT INTO @FileList EXEC(@RestoreStatement);

DECLARE @logical_data nvarchar(max), @logical_log nvarchar(max);
SET @logical_data = (SELECT LogicalName FROM @FileList WHERE Type = 'D' and FileID = 1)
SET @logical_log = (SELECT LogicalName FROM @FileList WHERE Type = 'L' and FileID = 2)

-- (3) Get the data directory of master.mdf (usually is the default data dir)
DECLARE @DataDir nvarchar(max), @NewDBFile nvarchar(max), @NewDBLogFile nvarchar(max);
SELECT @DataDir = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1

-- Otherwise just specify the directory, with: SET @DataDir = 'C:\MsSqlData\';
				  
-- (4) Construct the new database filenames
SET @NewDBFile = @DataDir + '' + @RestoreDB + '.mdf'; 
SET @NewDBLogFile = @DataDir + '' + @RestoreDB +  '_log.ldf';

-- Optional: Print the values for the restore command (comment the restore command below to preview the values)
PRINT @logical_data
PRINT @logical_log
PRINT @NewDBFile
PRINT @NewDBLogFile

-- (4) Restore the database to the new filename
RESTORE DATABASE @RestoreDB FROM DISK = @BackupFile WITH 
	MOVE @logical_data TO @NewDBFile,
	MOVE @logical_log TO @NewDBLogFile;