DECLARE @IsPci BIT, @FullBackupRetention nvarchar(1), @LogBackupRetention nvarchar(1), @BackupTSQL nvarchar(4000), @DefaultBackupDirectory nvarchar(256), @BackupFileName nvarchar(256), @Jobid binary(16), @Jobid2 binary(16), @MaxServerMemoryMB int, @MinServerMemoryMB int, @PhysicalMemoryInstalledMB int, @Warning nvarchar(4000), @Error nvarchar(4000);

/***********Configuration Section; Please set the values BELOW per the SQL Install build requirements***********/

SELECT 
	@MinServerMemoryMB = 0	--MinServerMemory should stay 0 unless you 100% know that it should be a different value and always be less than Max Server Memory
,	@MaxServerMemoryMB = 0	--MaxServerMemory should be less than Physical memory installed. 

,	@FullBackupRetention = 3	--Default is 3; Set to different value if needed.
,	@LogBackupRetention = @FullBackupRetention + 1	--Will always be 1 more day of log available on server to apply the oldest locally retained FULL backup.
,	@DefaultBackupDirectory = ''	--If not specified will use default from installation. Example: E:\MSSQL\Backup
,   	@BackupFileName = '<AUTO>' --Keep as is unless you know 100% what value it should be.
,	@IsPci = 0	--If this is a PCI server set this value to 1, ELSE keep it 0.






/***********Configuration Section; Please set the values ABOVE per the SQL Install build requirements***********/











/***********Setup ***********/
exec SP_CONFIGURE 'show advanced options', 1; Reconfigure;
PRINT ''
PRINT ''

--Start the report generation
PRINT '/* ---------------------------------- Datapipe Post-install Configuration Report ---------------------------------- */'
PRINT ('SQL Instance Name: ' + @@servername)
PRINT cast(serverproperty('Edition') as nvarchar(100)) 
PRINT 'Build:  ' + cast(serverproperty('productversion') as nvarchar(100)) + CHAR(13)

SET @Error = '';
SET @Warning = '';

/*********** --START Server Memory Setup-- ***********/
DECLARE @MemTbl TABLE ([Index] int, [Name] sysname, [Internal_value] int, [Character_Value] nvarchar(100));
	INSERT @MemTbl	EXEC [master]..[xp_msver] PhysicalMemory;
	SELECT @PhysicalMemoryInstalledMB = [Internal_Value] FROM @MemTbl;
	
	IF (@MaxServerMemoryMB = 0)
	BEGIN 
		PRINT 'Memory Configuration: Max Server Memory setting is 0. Setting max memory to 1048576'
		SET @MaxServerMemoryMB = 1048576;
		IF (@MaxServerMemoryMB < @MinServerMemoryMB)  
		BEGIN 
			SET @MinServerMemoryMB = 0; 
			PRINT 'Memory Configuration: Min Server memory must below max server memory. Set to 0'; 
		END
		SET @Warning = @Warning + 'Max Memory specified is 0 setting memory to 1048576' + char(13);
	END
	ELSE IF (@MaxServerMemoryMB between 1 and 128)
	BEGIN
		SET @Warning = @Warning + 'Max Server Memory specified ' + cast(@MaxServerMemoryMB as nvarchar(100)) + ' is between 1 and 128 which is invalid setting. Setting max memory to 1048576' + char(13);
		SET @MaxServerMemoryMB = 1048576;
		IF (@MaxServerMemoryMB < @MinServerMemoryMB)  
		BEGIN 
			SET @MinServerMemoryMB = 0; 
			PRINT 'Memory Configuration: Min Server memory must below max server memory. Set to 0'; 
		END
		PRINT 'Memory Configuration: Max Server Memory specified is invalid. Setting to 1048576';
		
	END
	ELSE
	BEGIN 
		IF (@MaxServerMemoryMB < @MinServerMemoryMB)
		BEGIN 
			SET @MinServerMemoryMB = 0; 
			PRINT 'Memory Configuration: Min Server memory must below max server memory. Set to 0'; 
		END
		PRINT 'Memory Configuration: Set memory to; Max: ' + cast(@MaxServerMemoryMB as nvarchar(100)) + ' Min: ' + cast(@MinServerMemoryMB as nvarchar(100)) + '.';
	END
	
	IF (@MaxServerMemoryMB > @PhysicalMemoryInstalledMB)
	BEGIN 
		SET @Warning = @Warning + 'Physical memory is less than Max Server memory specified.' + char(13); 
	END

/*********** --END Server Memory Setup-- ***********/

/*********** --START Backup Job Setup-- ***********/
IF EXISTS(SELECT name FROM msdb..sysjobs WHERE name in ('DP_RedGateFullBackup', 'DP_RedGateLogBackup'))
	BEGIN 
		SET @Warning = @Warning + 'Either DP_RedGateFullBackup or DP_RedGateLogBackup existed already, please check that the backup jobs are setup' + CHAR(13);
		Print 'Backup Job Configuration: No jobs setup because jobs exist.'
	END	
ELSE 
BEGIN
	BEGIN TRY
		SET @BackupTSQL = '';
		SET @Jobid = NULL; 
		--If No backup Directory specified used installation default
		IF LEN(@DefaultBackupDirectory) = 0
		BEGIN 
			EXEC master.dbo.xp_instance_regread
			'HKEY_LOCAL_MACHINE',
			'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer',
			'backupDirectory', 
			@DefaultBackupDirectory OUTPUT;
		END;

		SET @BackupTSQL = N'DECLARE @exitcode INT , @sqlerrorcode INT;
EXECUTE master..sqlbackup ''-SQL "BACKUP DATABASES [*] 
TO DISK = ''''' + @DefaultBackupDirectory + '\<TYPE>\<DATABASE>\' + @BackupFileName + '''''
WITH NAME = ''''<AUTO>''''
, DESCRIPTION = ''''<AUTO>''''
, DISKRETRYINTERVAL = 10
, DISKRETRYCOUNT = 3
, COMPRESSION = 4
, ERASEFILES = ' + @FullBackupRetention + '
, CHECKSUM
, VERIFY"'', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR (''SQL Backup job failed with exitcode: %d SQL error code: %d''
, 16, 1, @exitcode, @sqlerrorcode)
END';

		EXEC msdb.dbo.sp_add_job @job_name=N'DP_RedGateFullBackup', 
			@enabled=1, 
			@notify_level_eventlog=2, 
			@notify_level_email=0, 
			@notify_level_netsend=0, 
			@notify_level_page=0, 
			@delete_level=0, 
			@description=N'Using RedGate SQLBackup to perform Full backup of applicable databases.', 
			@category_name=N'Database Maintenance', 
			@owner_login_name=N'sa', 
			@job_id = @Jobid OUTPUT;
		EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1: Full Backup of All Databases', 
			@step_id=1, 
			@cmdexec_success_code=0, 
			@on_success_action=1, 
			@on_success_step_id=0, 
			@on_fail_action=2, 
			@on_fail_step_id=0, 
			@retry_attempts=0, 
			@retry_interval=0, 
			@os_run_priority=0, 
			@subsystem=N'TSQL', 
			@command= @BackupTSQL, 
			@database_name=N'master', 
			@flags=0;
		EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;
		EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily: 10PM', 
			@enabled=1, 
			@freq_type=4, 
			@freq_interval=1, 
			@freq_subday_type=1, 
			@freq_subday_interval=0, 
			@freq_relative_interval=0, 
			@freq_recurrence_factor=0, 
			@active_start_date=20131112, 
			@active_end_date=99991231, 
			@active_start_time=220000, 
			@active_end_time=235959, 
			@schedule_id=1;
		EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
		EXEC msdb.dbo.sp_add_job @job_name=N'DP_RedGateLogBackup', 
			@enabled=1, 
			@notify_level_eventlog=0, 
			@notify_level_email=0, 
			@notify_level_netsend=0, 
			@notify_level_page=0, 
			@delete_level=0, 
			@description=N'No description available.', 
			@category_name=N'Database Maintenance', 
			@owner_login_name=N'sa', 
			@job_id = @Jobid2 OUTPUT;
		EXEC msdb.dbo.sp_add_jobserver @job_id = @Jobid2 , @server_name = N'(local)';

		SET @BackupTSQL = N'DECLARE @exitcode INT , @sqlerrorcode INT
EXECUTE master..sqlbackup ''-SQL "BACKUP LOGS [*] 
TO DISK = ''''' + @DefaultBackupDirectory + '\<TYPE>\<DATABASE>\' + @BackupFileName + '''''
WITH NAME = ''''<AUTO>''''
, DESCRIPTION = ''''<AUTO>''''
, DISKRETRYINTERVAL = 10
, DISKRETRYCOUNT = 3
, COMPRESSION = 4
, ERASEFILES = ' + @LogBackupRetention + '
, CHECKSUM
, VERIFY"'', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR (''SQL Backup job failed with exitcode: %d SQL error code: %d''
, 16, 1, @exitcode, @sqlerrorcode)
END';
		EXEC msdb.dbo.sp_add_jobstep @job_id=@Jobid2 , @step_name=N'Step 1: Log Backup of All Databases', 
			@step_id=1, 
			@cmdexec_success_code=0, 
			@on_success_action=1, 
			@on_success_step_id=0, 
			@on_fail_action=2, 
			@on_fail_step_id=0, 
			@retry_attempts=0, 
			@retry_interval=0, 
			@os_run_priority=0, @subsystem=N'TSQL', 
			@command= @BackupTSQL, 
			@database_name=N'master', 
			@flags=0;
		EXEC msdb.dbo.sp_update_job @job_id = @Jobid2, @start_step_id = 1;
		EXEC msdb.dbo.sp_add_jobschedule @job_id=@Jobid2, @name=N'Hourly', 
			@enabled=1, 
			@freq_type=4, 
			@freq_interval=1, 
			@freq_subday_type=8, 
			@freq_subday_interval=1, 
			@freq_relative_interval=0, 
			@freq_recurrence_factor=0, 
			@active_start_date=20131112, 
			@active_end_date=99991231, 
			@active_start_time=3000, 
			@active_end_time=235959, 
			@schedule_id=2;


	DECLARE @Job_schedule table (schedule_id	int,schedule_uid	uniqueidentifier,schedule_name	sysname,enabled	int,freq_type	int,freq_interval	int,freq_subday_type	int,freq_subday_interval	int,freq_relative_interval	int,freq_recurrence_factor	int,active_start_date	int,active_end_date	int,active_start_time	int,active_end_time	int,date_created	datetime ,schedule_description	nvarchar(4000),job_count	int)
	DECLARE @JobScheduleid int, @FullBackupConfStmt nvarchar(4000), @LogBackupConfStmt nvarchar(4000)
	INSERT INTO @Job_schedule EXEC msdb..sp_help_schedule;

	--The Full Backup should be setup daily, so I'm only handling that use case.
	SELECT 	@FullBackupConfStmt = 
		(CASE WHEN name = 'DP_RedGateFullBackup' THEN 'FULL Backup Job Configuration: ' + name + char(13) + ' Dump Location: ' + @DefaultBackupDirectory + '\<TYPE>\<DATABASE>\' + @BackupFileName + CHAR(13) + ' Local Backup Retention: ' + @FullBackupRetention + ' Days' + char(13) + ' Scheduled: ' 
			+ (CASE WHEN [freq_type] = 1  THEN 'Once' WHEN [freq_type] = 4  THEN 'Every Day' WHEN [freq_type] = 8  THEN 'Weekly' 				WHEN [freq_type] = 16 THEN 'Monthly' 				WHEN [freq_type] = 32 THEN 'Monthly, relative to freq_interval' WHEN [freq_type] = 64 THEN 'Run when SQLServerAgent service starts' END) 	
			+ ', ' 
			+ (CASE WHEN [freq_subday_type] = 1 THEN 'At the specified time: ' + cast([active_start_time] as nvarchar(100))
					WHEN [freq_subday_type] = 2 THEN 'Every ' + cast([freq_subday_interval] as nvarchar(100)) + ' Second(s) ' + 'between ' + cast([active_start_time] as nvarchar(100)) + ' and ' + cast([active_end_time] as nvarchar(100))
					WHEN [freq_subday_type] = 4 THEN 'Every ' + cast([freq_subday_interval] as nvarchar(100)) + ' Minute(s) ' + 'between ' + cast([active_start_time] as nvarchar(100)) + ' and ' + cast([active_end_time] as nvarchar(100))
					WHEN [freq_subday_type] = 8 THEN 'Every ' + cast([freq_subday_interval] as nvarchar(100)) + ' Hour(s) '	+ 'between '   + cast([active_start_time] as nvarchar(100)) + ' and ' + cast([active_end_time] as nvarchar(100)) 
				END)
			END)
	FROM msdb..sysjobs sj
	 JOIN msdb..sysjobschedules sjs
	  ON sj.job_id = sjs.job_id
	 Join @Job_schedule js 
	  ON js.schedule_id = sjs.schedule_id
	WHERE name = ('DP_RedGateFULLBackup')

	SELECT 	@LogBackupConfStmt = 
		(CASE	WHEN name = 'DP_RedGateLogBackup' THEN 'LOG Backup Job Configuration: ' + name + CHAR(13) + ' Dump Location: ' + @DefaultBackupDirectory + '\<TYPE>\<DATABASE>\' + @BackupFileName + char(13) + ' Local Backup Retention: ' + @LogBackupRetention + ' Days' + char(13) + ' Scheduled: ' 
		
			+ (CASE	WHEN [freq_type] = 1  THEN 'Once' WHEN [freq_type] = 4  THEN 'Daily' WHEN [freq_type] = 8  THEN 'Weekly' WHEN [freq_type] = 16 THEN 'Monthly' WHEN [freq_type] = 32 THEN 'Monthly, relative to freq_interval' WHEN [freq_type] = 64 THEN 'Run when SQLServerAgent service starts' END) 	+ ', ' 
			+ (CASE WHEN [freq_subday_type] = 1 THEN 'At the specified time: ' + left(right('0' + cast([active_start_time] as varchar), 6), 2) + ':' 			+ substring(right('0' + cast([active_start_time] as varchar), 6), 3,2) + ':' + right(cast([active_start_time] as varchar), 2) 
					WHEN [freq_subday_type] = 2 THEN 'Every ' + cast([freq_subday_interval] as nvarchar(100)) + ' Second(s) ' + 'between ' + cast([active_start_time] as nvarchar(100)) + ' and ' + cast([active_end_time] as nvarchar(100))
					WHEN [freq_subday_type] = 4 THEN 'Every ' + cast([freq_subday_interval] as nvarchar(100)) + ' Minute(s) ' + 'between ' + cast([active_start_time] as nvarchar(100)) + ' and ' + cast([active_end_time] as nvarchar(100))
					WHEN [freq_subday_type] = 8 THEN 'Every ' + cast([freq_subday_interval] as nvarchar(100)) + ' Hour(s) '	+ 'between '   + cast([active_start_time] as nvarchar(100)) + ' and ' + cast([active_end_time] as nvarchar(100)) 
				END)
			END)

	FROM msdb..sysjobs sj
	 JOIN msdb..sysjobschedules sjs
	  ON sj.job_id = sjs.job_id
	 Join @Job_schedule js 
	  on js.schedule_id = sjs.schedule_id
	WHERE name = ('DP_RedGateLogBackup')
 
		Print (@FullBackupConfStmt + CHar(13) + @LogBackupConfStmt);
			
	END TRY
	BEGIN CATCH
		SET @Error = @Error + 'Creation of Backup jobs had error in configuration attempt: ' + ERROR_MESSAGE();
	END CATCH
END
/*********** --END Backup Job Setup-- ***********/

/*********** --START History Cleanup Setup-- ***********/


IF EXISTS(select name from msdb..sysjobs where name = 'DP_HistoryCleanup')
BEGIN 
	SET @Warning = @Warning + 'Job ''DP_HistoryCleanup'' exists already' + CHAR(13); 
	PRINT 'History Cleanup Configuration: Job ''DP_HistoryCleanup'' exists already'; 
END
ELSE
BEGIN
	BEGIN TRY
		SET @Jobid = NULL;
		EXEC  msdb.dbo.sp_add_job @job_name=N'DP_HistoryCleanup', 
				@enabled=1, 
				@notify_level_eventlog=0, 
				@notify_level_email=2, 
				@notify_level_netsend=2, 
				@notify_level_page=2, 
				@delete_level=0, 
				@category_name=N'Database Maintenance', 
				@owner_login_name=N'sa', @job_id = @Jobid OUTPUT
		EXEC msdb.dbo.sp_add_jobserver @job_name=N'DP_HistoryCleanup', @server_name = N'(Local)'
		EXEC msdb.dbo.sp_add_jobstep @job_name=N'DP_HistoryCleanup', @step_name=N'1: Remove records older than 90 days in jobhistory, Maint plan history, BackupAndRestoreHistory', 
				@step_id=1, 
				@cmdexec_success_code=0, 
				@on_success_action=1, 
				@on_fail_action=2, 
				@retry_attempts=0, 
				@retry_interval=0, 
				@os_run_priority=0, @subsystem=N'TSQL', 
				@command=N'SET NOCOUNT ON;
		DECLARE @RetentionDate datetime;
		SELECT @RetentionDate = dateadd(day, -90, getdate());

		EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=@RetentionDate 
		EXEC msdb..sp_maintplan_delete_log null,null,@RetentionDate 
		EXEC msdb.dbo.sp_delete_backuphistory @RetentionDate', 
				@database_name=N'master', 
				@flags=0
		EXEC msdb.dbo.sp_update_job @job_name=N'DP_HistoryCleanup', 
				@enabled=1, 
				@start_step_id=1, 
				@notify_level_eventlog=0, 
				@notify_level_email=2, 
				@notify_level_netsend=2, 
				@notify_level_page=2, 
				@delete_level=0, 
				@description=N'', 
				@category_name=N'Database Maintenance', 
				@owner_login_name=N'sa', 
				@notify_email_operator_name=N'', 
				@notify_netsend_operator_name=N'', 
				@notify_page_operator_name=N''
		DECLARE @schedule_id int
		EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DP_HistoryCleanup', @name=N'Daily', 
				@enabled=1, 
				@freq_type=4, 
				@freq_interval=1, 
				@freq_subday_type=1, 
				@freq_subday_interval=0, 
				@freq_relative_interval=0, 
				@freq_recurrence_factor=1, 
				@active_start_date=20131113, 
				@active_end_date=99991231, 
				@active_start_time=10, 
				@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
		PRINT 'History Cleanup Configuration: Job created ''DP_HistoryCleanup'' with default 90 days retention';
	END TRY
	BEGIN CATCH
		PRINT 'History Cleanup Configuration: SQL Agent Job ''DP_HistoryCleanup'' failed to be created.';
		SET @Error = @Error + 'Set History Cleanup Job had error in configuration attempt: ' + ERROR_MESSAGE();
	END CATCH
END
/*********** --END History Cleanup Setup-- ***********/

/*********** --START ErrorLog Rotation Setup-- ***********/

BEGIN TRY
	EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99;
	PRINT 'Error Log Configuration: Max Error Logs have been set to 99';
END TRY
BEGIN CATCH
	SET @Error = @Error + 'Set Max Error Logs had error in configuration attempt: ' + ERROR_MESSAGE();
END CATCH

IF EXISTS(select name from msdb..sysjobs where name = 'DP_Cycle_ErrorLog')
BEGIN 
	SET @Warning = @Warning + 'Job ''DP_Cycle_ErrorLog'' exists already' + CHAR(13); 
	PRINT 'Cycle Error log: Job ''DP_Cycle_ErrorLog'' exists already'; 
END
ELSE
BEGIN
	BEGIN TRY
		SET @Jobid = NULL;
		EXEC  msdb.dbo.sp_add_job @job_name=N'DP_Cycle_ErrorLog', 
				@enabled=1, 
				@notify_level_eventlog=0, 
				@notify_level_email=2, 
				@notify_level_netsend=2, 
				@notify_level_page=2, 
				@delete_level=0, 
				@category_name=N'Database Maintenance', 
				@owner_login_name=N'sa', @job_id = @Jobid OUTPUT;
		EXEC msdb.dbo.sp_add_jobserver @job_name=N'DP_Cycle_ErrorLog', @server_name = N'(local)';
		EXEC msdb.dbo.sp_add_jobstep @job_name=N'DP_Cycle_ErrorLog', @step_name=N'1: CycleErrorLog', 
				@step_id=1, 
				@cmdexec_success_code=0, 
				@on_success_action=1, 
				@on_fail_action=2, 
				@retry_attempts=0, 
				@retry_interval=0, 
				@os_run_priority=0, @subsystem=N'TSQL', 
				@command=N'EXEC master..sp_Cycle_ErrorLog', 
				@database_name=N'master', 
				@flags=0;
		EXEC msdb.dbo.sp_update_job @job_name=N'DP_Cycle_ErrorLog', 
				@enabled=1, 
				@start_step_id=1, 
				@notify_level_eventlog=0, 
				@notify_level_email=2, 
				@notify_level_netsend=2, 
				@notify_level_page=2, 
				@delete_level=0, 
				@description=N'', 
				@category_name=N'Database Maintenance', 
				@owner_login_name=N'sa', 
				@notify_email_operator_name=N'', 
				@notify_netsend_operator_name=N'', 
				@notify_page_operator_name=N'';

		SET @schedule_id = 0;
		EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DP_Cycle_ErrorLog', @name=N'Daily : 12AM', 
				@enabled=1, 
				@freq_type=4, 
				@freq_interval=1, 
				@freq_subday_type=1, 
				@freq_subday_interval=0, 
				@freq_relative_interval=0, 
				@freq_recurrence_factor=1, 
				@active_start_date=20131113, 
				@active_end_date=99991231, 
				@active_start_time=0, 
				@active_end_time=235959, @schedule_id = @schedule_id OUTPUT;
		PRINT 'Error Log Configuration: Job ''DP_Cycle_ErrorLog'' has been created'
	END TRY
	BEGIN CATCH
		SET @Error = @Error + 'Creation of job ''DP_Cycle_ErrorLog had error in configuration attempt: ' + ERROR_MESSAGE();
	END CATCH
END

/*********** --END ErrorLog Rotation Setup-- ***********/
/*********** --END PCI AuditLevel Setup Section-- ***********/
DECLARE @IsPci_Regread table ([Value] nvarchar (10), [data] tinyint);
DECLARE @PCIPrintStmt nvarchar(1000)
SET @PCIPrintStmt = 'SQL Audit Logging Level Configuration:';

IF (@IsPci = 1) 
BEGIN 
	BEGIN TRY
		EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer', N'AuditLevel', REG_DWORD, 3	
		PRINT (@PCIPrintStmt + ' isPCI Flag was set to 1; Changing logging to ''Both Failed and Success logins''')
	END TRY
	BEGIN CATCH
		SET @Error = @Error + 'Setup of Audit Level failed on configuration: ' + ERROR_MESSAGE();
	END CATCH
END
ELSE
BEGIN
	BEGIN TRY
	INSERT INTO @IsPci_Regread
		EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer', N'AuditLevel';

	SELECT @PCIPrintStmt = @PCIPrintStmt + ' isPCI flag was set to ' + cast(@IsPci as nvarchar(1)) + ', no change needed. ' + Char(13) + ' Current login Auditing: ''' 
		, @PCIPrintStmt = @PCIPrintStmt 
		+ Case
			WHEN data = 0 THEN 'No logging'''
			WHEN data = 1 THEN 'Sucess logins only'''
			WHEN data = 2 THEN 'Failed logins only'''
			WHEN data = 3 THEN 'Both Failed and Sucess logins'''
		  END
	FROM @IsPci_regread
	PRINT @pciprintstmt
	END TRY
	BEGIN CATCH
		SET @Error = @Error + 'Read of Registry on Report generation failed with : ' + ERROR_MESSAGE();
	END CATCH 
END;

/*********** --END PCI AuditLevel Setup Section-- ***********/

/*********** --START SQL Agent Alerts Sev 19-25 and message ids (823,824,825)-- ***********/
IF exists(select * from msdb..sysalerts where severity between 19 and 25 or message_id in (823,824,825))
BEGIN 
	SET @Warning = @Warning + 'Please check SQL Agent Alerts some/all already setup for Severity 19 thru 25 and message ids (823,824,825).'
END
ELSE 
	BEGIN TRY
		EXEC msdb.dbo.sp_add_alert @name=N'ErrorSeverity_19', 
				@message_id=0, 
				@severity=19, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0, 
				@job_id=N'00000000-0000-0000-0000-000000000000';
		EXEC msdb.dbo.sp_add_alert @name=N'ErrorSeverity_20', 
				@message_id=0, 
				@severity=20, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0, 
				@job_id=N'00000000-0000-0000-0000-000000000000';
		EXEC msdb.dbo.sp_add_alert @name=N'ErrorSeverity_21', 
				@message_id=0, 
				@severity=21, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0, 
				@job_id=N'00000000-0000-0000-0000-000000000000';
		EXEC msdb.dbo.sp_add_alert @name=N'ErrorSeverity_22', 
				@message_id=0, 
				@severity=22, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0, 
				@job_id=N'00000000-0000-0000-0000-000000000000';
		EXEC msdb.dbo.sp_add_alert @name=N'ErrorSeverity_23', 
				@message_id=0, 
				@severity=23, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0, 
				@job_id=N'00000000-0000-0000-0000-000000000000';
		EXEC msdb.dbo.sp_add_alert @name=N'ErrorSeverity_24', 
				@message_id=0, 
				@severity=24, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0, 
				@job_id=N'00000000-0000-0000-0000-000000000000';
		EXEC msdb.dbo.sp_add_alert @name=N'ErrorSeverity_25', 
				@message_id=0, 
				@severity=25, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0, 
				@job_id=N'00000000-0000-0000-0000-000000000000';
		EXEC msdb.dbo.sp_add_alert @name=N'Error_823', 
				@message_id=823, 
				@severity=0, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0;
		EXEC msdb.dbo.sp_add_alert @name=N'Error_824', 
				@message_id=824, 
				@severity=0, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0;
		EXEC msdb.dbo.sp_add_alert @name=N'Error_825', 
				@message_id=825, 
				@severity=0, 
				@enabled=1, 
				@delay_between_responses=0, 
				@include_event_description_in=0;
	END TRY
	BEGIN CATCH
		SET @Error = @Error + 'Setup of SQL Agent Alerts error occurred: ' + ERROR_MESSAGE();
	END CATCH
	IF ((select count(*) from msdb..sysalerts where severity between 19 and 25 or (message_id in (823,824,825)) and enabled = 1) = 10)
	BEGIN 
		PRINT 'SQL Agent Alerts Configuration: Severity 19 thru 25 and message ids (823,824,825) are setup and are enabled'; 
	END 
/*********** --END SQL Agent Error Sev 19-25 and message ids (823,824,825)-- ***********/

PRINT ''
IF (len(@Error) > 0)
	PRINT '!!!Errors!!!' + CHAR(13) + @Error;
IF (len(@Warning) > 0) 
		PRINT '!!!Warnings!!!' + CHAR(13) + @Warning;

PRINT '/* ---------------------------------- Datapipe Post-install Configuration Report ---------------------------------- */' + CHAR(13) + CHAR(13);
/*********** --Start Setup Native SQL Backup Compression-- ***********/
DECLARE @SqlVersion NUMERIC(4,2), @SQLString nvarchar(500); 
SELECT @SqlVersion = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR), 4)
SET @SQLString = 'EXEC master.sys.sp_configure N''backup compression default'', N''1'';'
IF(@SqlVersion >= 10)
BEGIN
	EXEC SP_EXECUTESQL @statement = @SQLString; RECONFIGURE WITH OVERRIDE;	
END

/*********** --End Setup Native SQL Backup Compression-- ***********/
EXEC SP_CONFIGURE 'min server memory (MB)' , @MinServerMemoryMB; RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE 'max server memory (MB)' , @MaxServerMemoryMB; RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE 'show advanced options', 0; Reconfigure;

/***********Setup ***********/