-- Create Restore Database Command from DB Backup File

set nocount on
go

declare @backup_path nvarchar(500)
select @backup_path =
-- Path to Backup file 
'\\SERVERNAME\SHARE_NAME\MY_DB_BACKUP_FILENAME.BAK'


create table #header (
BackupName		nvarchar(128)	null,
BackupDescription	nvarchar(128)	null,
BackupType		int		not null,
ExpirationDate		datetime	null,
Compressed		int		not null,
Position		int		not null,
DeviceType		int		not null,
UserName		nvarchar(128)	not null,
ServerName		nvarchar(128)	not null,
DatabaseName		nvarchar(128)	not null,
DatabaseVersion		int		not null,
DatabaseCreationDate	datetime	not null,
BackupSize		decimal(28,0)	not null,
FirstLsn		decimal(28,0)	not null,
LastLsn			decimal(28,0)	not null,
CheckpointLsn		decimal(28,0)	not null,
DatabaseBackupLsn	decimal(28,0)	not null,
BackupStartDate		datetime	not null,
BackupFinishDate	datetime	not null,
SortOrder		int		not null,
CodePage		int		not null,
UnicodeLocaleId		int		not null,
UnicodeComparisonStyle	int		not null,
CompatibilityLevel	int		not null,
SoftwareVendorId	int		null,
SoftwareVersionMajor	int		null,
SoftwareVersionMinor	int		null,
SoftwareVersionBuild	int		null,
MachineName		nvarchar(128)	not null,
Flags			int		null,
BindingID		uniqueidentifier null,
RecoveryForkID		uniqueidentifier null,
Collation		nvarchar(128)	null,
Seq			int		not null
	identity(1,1),
)

create table #filelist (
LogicalName	nvarchar(128)	not null,
PhysicalName	nvarchar(128)	not null,
Type		nvarchar(10)	not null,
FileGroupName	nvarchar(128)	null,
Size		decimal(28,0)	not null,
MaxSize		decimal(28,0)	not null,
Seq		int		not null
	identity(1,1),
)

insert into #header
exec ('restore HeaderOnly from disk = '''+@backup_path+''' ')

insert into #filelist
exec ('restore FilelistOnly from disk = '''+@backup_path+'''')

declare @tab varchar(1), @cr varchar(2)
select @tab = char(9), @cr = char(13)+Char(10)

select
	[--Restore--]	=
	case
	when a.Seq = 1
	then
	@cr+
	@cr+'restore database '+c.DatabaseName+
	@cr+'from disk ='+@cr+@tab+''''+
	@backup_path+''''+@cr+'with'+@cr
	else ''
	end+
	@tab+'move '''+a.LogicalName+
	'''	to '''+a.PhysicalName+''' ,'+
	case
	when a.Seq = b.Seq
	then
	@cr+@tab+'replace, stats = 5 , recovery'
	else ''
	end
from
	#filelist a
	cross join
	( select Seq = max(b1.Seq) from #filelist b1 ) b
	cross join
	( select DatabaseName = max(c1.DatabaseName) 
	  from #header c1 ) c
order by
	a.Seq
go
drop table #header
drop table #filelist

--Restore--
--------------------------------------------------------------------

restore database MY_DB
from disk =
	'\\SERVERNAME\SHARE_NAME\MY_DB_BACKUP_FILENAME.BAK'
with
	move 'PRIMARY'	to 'D:\MSSQL\DATA\MY_DB_PRIMARY.MDF' ,
	move 'DATA1'	to 'D:\MSSQL\DATA\MY_DB_DATA1.NDF' ,
	move 'DATA2'	to 'D:\MSSQL\DATA\MY_DB_DATA2.NDF' ,
	move 'LOG'	to 'D:\MSSQL\DATA\MY_DB_LOG.LDF' ,
	replace, stats = 5 , recovery