PMDB.Restore_Primavera_Backup.sql

/*---------------------------------------------------------------------------------------------------------------------+
| Purpose:	Restore a backup of a database
| Example:  EXEC admin.Restore_Primavera_Backup 'C:\Temp\PMDB1_20170519_073701_firstname.lastname.bak', 'PMDB1_TEST'
+----------------------------------------------------------------------------------------------------------------------*/

:setvar _server "Server1"
:setvar _user "***username***"
:setvar _password "***password***"
:setvar _database "master"
:connect $(_server) -U $(_user) -P $(_password)

USE [$(_database)];
GO


CREATE PROCEDURE [admin].[Restore_Primavera_Backup]
(
  @FileName VARCHAR(255)
, @DatabaseName VARCHAR(50)
)
AS 
BEGIN

PRINT '====================================================================='
PRINT 'check if the database exists first...'
PRINT '====================================================================='

IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @DatabaseName)) 
BEGIN

	PRINT '====================================================================='
	PRINT 'set the name and path for the restore...'
	PRINT '====================================================================='
	DECLARE @DataFile VARCHAR(200)

	PRINT '====================================================================='
	PRINT 'set the data path for sql server...'
	PRINT '====================================================================='

	SELECT @DataFile = 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

	PRINT '====================================================================='
	PRINT 'get the logical names from the backup file...'
	PRINT '====================================================================='
	DECLARE @Table TABLE (
						  [LogicalName] varchar(128)
						, [PhysicalName] varchar(128)
						, [Type] varchar
						, [FileGroupName] varchar(128)
						, [Size] varchar(128)
						, [MaxSize] varchar(128)
						, [FileId]varchar(128)
						, [CreateLSN]varchar(128)
						, [DropLSN]varchar(128)
						, [UniqueId]varchar(128)
						, [ReadOnlyLSN]varchar(128)
						, [ReadWriteLSN]varchar(128)
						, [BackupSizeInBytes]varchar(128)
						, [SourceBlockSize]varchar(128)
						, [FileGroupId]varchar(128)
						, [LogGroupGUID]varchar(128)
						, [DifferentialBaseLSN]varchar(128)
						, [DifferentialBaseGUID]varchar(128)
						, [IsReadOnly]varchar(128)
						, [IsPresent]varchar(128)
						, [TDEThumbprint]varchar(128)
						)
						
	DECLARE @LogicalNameData varchar(128)
	DECLARE @LogicalNameLog varchar(128)
	INSERT INTO @table EXEC('RESTORE FILELISTONLY FROM DISK=''' + @FileName + '''')

	   SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
	   SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')

	PRINT '====================================================================='
	PRINT 'Restore the Database starting with a file from a Full Backup...'
	PRINT '====================================================================='
	BEGIN
		  DECLARE @RESTORE_SQL VARCHAR(MAX)
		  SET @RESTORE_SQL =
			'RESTORE DATABASE ' + @DatabaseName + '
			FROM DISK = ''' + @FileName + '''
			WITH
			  RECOVERY
			, STATS = 10
		, MOVE ''' + @LogicalNameData + ''' TO ''' + @DataFile + @DatabaseName + '.mdf''
		, MOVE ''' + @LogicalNameLog + ''' TO ''' + @DataFile + @DatabaseName + '.ldf'''
		  PRINT @RESTORE_SQL
		  EXEC (@RESTORE_SQL)
	END

	PRINT '====================================================================='
	PRINT 'Update owner of the database to standard...'
	PRINT '====================================================================='
	BEGIN
		  DECLARE @OWNER_SQL VARCHAR(MAX)
		  SET @OWNER_SQL = 'ALTER AUTHORIZATION ON DATABASE::' + @DatabaseName + ' TO sa;'
		  PRINT @OWNER_SQL
		  EXEC (@OWNER_SQL)
	END  

	PRINT '====================================================================='
	PRINT 'Restore system user logins...'
	PRINT '====================================================================='

	DECLARE @sql NVARCHAR(255);
	SET @sql = 'USE ' + @DatabaseName + '; EXEC dbo.sp_change_users_login ''Update_One'', ''privuser'', ''privuser''; ';

	PRINT @sql
	EXEC (@sql)

	SET @sql = 'USE ' + @DatabaseName + '; EXEC dbo.sp_change_users_login ''Update_One'', ''pubuser'', ''pubuser''; ';

	PRINT @sql
	EXEC (@sql)

	PRINT '====================================================================='
	PRINT 'Restore system jobs if they havent already been setup...'
	PRINT '====================================================================='

	SET @sql = 'USE ' + @DatabaseName + '; EXEC initialize_background_procs; ';

	PRINT @sql
	EXEC (@sql)

	SET @sql = 'USE ' + @DatabaseName + '; EXEC system_monitor; ';

	PRINT @sql
	EXEC (@sql)

	SET @sql = 'USE ' + @DatabaseName + '; EXEC data_monitor; ';

	PRINT @sql
	EXEC (@sql)

	PRINT '====================================================================='
	PRINT 'Finished!'
	PRINT '====================================================================='

	END

END;

GO