use master
go


if exists (select * from dbo.sysobjects
  where id = object_id(N'[dbo].[uspEvoCreateRestoreStatement]')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[uspEvoCreateRestoreStatement]
GO



create proc uspEvoCreateRestoreStatement(
  @filename varchar( 100),
  @targetDatabase varchar( 100),
  @AddBackupStatememt bit = 1
) as

set nocount on

declare @targetDatabaseId int
declare @cmd varchar( 1000)
declare @targetDatabaseDataName varchar( 100)
declare @targetDatabaseLogName varchar( 100)
declare @targetDatabaseDataPath varchar( 200)
declare @targetDatabaseLogPath varchar( 200)
declare @sourceLogicalNameData varchar( 100)
declare @sourceLogicalNameLog varchar( 100)

select
  @targetDatabaseId = database_id
from
  master.sys.databases
where
  name = @targetDatabase

select
  @targetDatabaseDataName = name,
  @targetDatabaseDataPath = physical_name
from
  master.sys.master_files
where
  database_id = @targetDatabaseId and type=0

select
  @targetDatabaseLogName = name,
  @targetDatabaseLogPath = physical_name
from
  master.sys.master_files
where
  database_id = @targetDatabaseId
  and type=1

set @cmd = 'restore filelistonly from disk = ''' + @filename + ''''

CREATE TABLE #RestoreFileListOnly (
  LogicalName nvarchar(128),
  PhysicalName nvarchar(260),
  Type char(1),
  FileGroupName nvarchar(128),
  Size numeric(20,0),
  MaxSize numeric(20,0),
  FileID bigint,
  CreateLSN numeric(25,0),
  DropLSN numeric(25,0) NULL,
  UniqueID uniqueidentifier,
  ReadOnlyLSN numeric(25,0) NULL,
  ReadWriteLSN numeric(25,0) NULL,
  BackupSizeInBytes bigint,
  SourceBlockSize int,
  FileGroupID int,
  LogGroupGUID uniqueidentifier NULL,
  DifferentialBaseLSN numeric(25,0) NULL,
  DifferentialBaseGUID uniqueidentifier,
  IsReadOnly bit,
  IsPresent bit )

INSERT INTO #RestoreFileListOnly
EXEC(@cmd)


select
  @sourceLogicalNameData = LogicalName
from
  #RestoreFileListOnly
where
  Type = 'D'

select
  @sourceLogicalNameLog = LogicalName
from
  #RestoreFileListOnly
where
  Type = 'L'


DROP TABLE #RestoreFileListOnly

set nocount off

print '------------------------ Restore: ' + @targetDatabase
print 'use master'
print 'GO'
print ''
if @AddBackupStatememt = 1 begin
  print 'if @@Servername = ''' + @@Servername + ''' BEGIN'
  print '  exec master..uspEvoBackup ''' + @targetDatabase + ''''
  print 'END'
  print 'GO'
  print ''
end
print 'if @@Servername = ''' + @@Servername + ''' BEGIN'
print '  ALTER DATABASE ' + @targetDatabase
print '  SET OFFLINE'
print '  WITH ROLLBACK IMMEDIATE'
print 'END'
print 'GO'
print ''
print 'if @@Servername = ''' + @@Servername + ''' BEGIN'
print '  ALTER DATABASE ' + @targetDatabase
print '  SET ONLINE'
print '  WITH ROLLBACK IMMEDIATE'
print 'END'
print 'GO'
print ''
print 'if @@Servername = ''' + @@Servername + ''' BEGIN'
print '  RESTORE database ' + @targetDatabase
print '  FROM DISK = ''' + @filename + ''''
print '  WITH MOVE ''' + @sourceLogicalNameData + ''' TO ''' + @targetDatabaseDataPath + ''','
print '  MOVE ''' + @sourceLogicalNameLog + ''' TO ''' + @targetDatabaseLogPath + ''','
print '  REPLACE'
print 'END'
print 'GO'
print ''
if @sourceLogicalNameData <> @targetDatabaseDataName begin
  print 'if @@Servername = ''' + @@Servername + ''' BEGIN'
  print '  alter database ' + @targetDatabase
  print '  MODIFY FILE ('
  print '  NAME = ''' + @sourceLogicalNameData + ''','
  print '  NEWNAME = ''' + @targetDatabaseDataName + ''')'
  print 'END'
  print 'GO'
  print ''
end
if @sourceLogicalNameLog <> @targetDatabaseLogName begin
  print 'if @@Servername = ''' + @@Servername + ''' BEGIN'
  print '  alter database ' + @targetDatabase
  print '  MODIFY FILE ('
  print '  NAME = ''' + @sourceLogicalNameLog + ''','
  print '  NEWNAME = ''' + @targetDatabaseLogName + ''')'
  print 'END'
  print 'GO'
end


go