sp_AllNightLog_Setup.sql

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

IF OBJECT_ID('dbo.sp_AllNightLog_Setup') IS NULL
  EXEC ('CREATE PROCEDURE dbo.sp_AllNightLog_Setup AS RETURN 0;');
GO


ALTER PROCEDURE dbo.sp_AllNightLog_Setup
				@RPOSeconds BIGINT = 30,
				@RTOSeconds BIGINT = 30,
				@BackupPath NVARCHAR(MAX) = NULL,
				@RestorePath NVARCHAR(MAX) = NULL,
				@Jobs TINYINT = 10,
				@RunSetup BIT = 0,
				@UpdateSetup BIT = 0,
                @EnableBackupJobs INT = NULL,
                @EnableRestoreJobs INT = NULL,
				@Debug BIT = 0,
				@FirstFullBackup BIT = 0,
				@FirstDiffBackup BIT = 0,
				@Help BIT = 0,
				@VersionDate DATETIME = NULL OUTPUT
WITH RECOMPILE
AS
SET NOCOUNT ON;

BEGIN;

DECLARE @Version VARCHAR(30);
SET @Version = '2.3';
SET @VersionDate = '20180301';;


IF @Help = 1

BEGIN

	PRINT '		
		/*


		sp_AllNightLog_Setup from http://FirstResponderKit.org
		
		This script sets up a database, tables, rows, and jobs for sp_AllNightLog, including:

		* Creates a database
			* Right now it''s hard-coded to use msdbCentral, that might change later
	
		* Creates tables in that database!
			* dbo.backup_configuration
				* Hold variables used by stored proc to make runtime decisions
					* RPO: Seconds, how often we look for databases that need log backups
					* Backup Path: The path we feed to Ola H''s backup proc
			* dbo.backup_worker
				* Holds list of databases and some information that helps our Agent jobs figure out if they need to take another log backup
		
		* Creates tables in msdb
			* dbo.restore_configuration
				* Holds variables used by stored proc to make runtime decisions
					* RTO: Seconds, how often to look for log backups to restore
					* Restore Path: The path we feed to sp_DatabaseRestore 
			* dbo.restore_worker
				* Holds list of databases and some information that helps our Agent jobs figure out if they need to look for files to restore
	
		 * Creates agent jobs
			* 1 job that polls sys.databases for new entries
			* 10 jobs that run to take log backups
			 * Based on a queue table
			 * Requires Ola Hallengren''s Database Backup stored proc
	
		To learn more, visit http://FirstResponderKit.org where you can download new
		versions for free, watch training videos on how it works, get more info on
		the findings, contribute your own code, and more.
	
		Known limitations of this version:
		 - Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000! And really, maybe not even anything less than 2016. Heh.
		 - The repository database name is hard-coded to msdbCentral.
	
		Unknown limitations of this version:
		 - None.  (If we knew them, they would be known. Duh.)
	
	     Changes - for the full list of improvements and fixes in this version, see:
	     https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/
	
	
		Parameter explanations:
	
		  @RunSetup	BIT, defaults to 0. When this is set to 1, it will run the setup portion to create database, tables, and worker jobs.
		  @UpdateSetup BIT, defaults to 0. When set to 1, will update existing configs for RPO/RTO and database backup/restore paths.
		  @RPOSeconds BIGINT, defaults to 30. Value in seconds you want to use to determine if a new log backup needs to be taken.
		  @BackupPath NVARCHAR(MAX), defaults to = ''D:\Backup''. You 99.99999% will need to change this path to something else. This tells Ola''s job where to put backups.
		  @Debug BIT, defaults to 0. Whent this is set to 1, it prints out dynamic SQL commands
	
	    Sample call:
		EXEC dbo.sp_AllNightLog_Setup
			@RunSetup = 1,
			@RPOSeconds = 30,
			@BackupPath = N''M:\MSSQL\Backup'',
			@Debug = 1


		For more documentation: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/
	
	    MIT License
		
		Copyright (c) 2017 Brent Ozar Unlimited
	
		Permission is hereby granted, free of charge, to any person obtaining a copy
		of this software and associated documentation files (the "Software"), to deal
		in the Software without restriction, including without limitation the rights
		to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
		copies of the Software, and to permit persons to whom the Software is
		furnished to do so, subject to the following conditions:
	
		The above copyright notice and this permission notice shall be included in all
		copies or substantial portions of the Software.
	
		THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
		IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
		FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
		AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
		LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
		OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
		SOFTWARE.


		*/';

RETURN;
END; /* IF @Help = 1 */

DECLARE	@database NVARCHAR(128) = NULL; --Holds the database that's currently being processed
DECLARE @error_number INT = NULL; --Used for TRY/CATCH
DECLARE @error_severity INT; --Used for TRY/CATCH
DECLARE @error_state INT; --Used for TRY/CATCH
DECLARE @msg NVARCHAR(4000) = N''; --Used for RAISERROR
DECLARE @rpo INT; --Used to hold the RPO value in our configuration table
DECLARE @backup_path NVARCHAR(MAX); --Used to hold the backup path in our configuration table
DECLARE @db_sql NVARCHAR(MAX) = N''; --Used to hold the dynamic SQL to create msdbCentral
DECLARE @tbl_sql NVARCHAR(MAX) = N''; --Used to hold the dynamic SQL that creates tables in msdbCentral
DECLARE @database_name NVARCHAR(256) = N'msdbCentral'; --Used to hold the name of the database we create to centralize data
													   --Right now it's hardcoded to msdbCentral, but I made it dynamic in case that changes down the line


/*These variables control the loop to create/modify jobs*/
DECLARE @job_sql NVARCHAR(MAX) = N''; --Used to hold the dynamic SQL that creates Agent jobs
DECLARE @counter INT = 0; --For looping to create 10 Agent jobs
DECLARE @job_category NVARCHAR(MAX) = N'''Database Maintenance'''; --Job category
DECLARE @job_owner NVARCHAR(128) = QUOTENAME(SUSER_SNAME(0x01), ''''); -- Admin user/owner
DECLARE @jobs_to_change TABLE(name SYSNAME); -- list of jobs we need to enable or disable
DECLARE @current_job_name SYSNAME; -- While looping through Agent jobs to enable or disable
DECLARE @active_start_date INT = (CONVERT(INT, CONVERT(VARCHAR(10), GETDATE(), 112)));
DECLARE @started_waiting_for_jobs DATETIME; --We need to wait for a while when disabling jobs

/*Specifically for Backups*/
DECLARE @job_name_backups NVARCHAR(MAX) = N'''sp_AllNightLog_Backup_Job_'''; --Name of log backup job
DECLARE @job_description_backups NVARCHAR(MAX) = N'''This is a worker for the purposes of taking log backups from msdbCentral.dbo.backup_worker queue table.'''; --Job description
DECLARE @job_command_backups NVARCHAR(MAX) = N'''EXEC sp_AllNightLog @Backup = 1'''; --Command the Agent job will run

/*Specifically for Restores*/
DECLARE @job_name_restores NVARCHAR(MAX) = N'''sp_AllNightLog_Restore_Job_'''; --Name of log backup job
DECLARE @job_description_restores NVARCHAR(MAX) = N'''This is a worker for the purposes of restoring log backups from msdb.dbo.restore_worker queue table.'''; --Job description
DECLARE @job_command_restores NVARCHAR(MAX) = N'''EXEC sp_AllNightLog @Restore = 1'''; --Command the Agent job will run


/*

Sanity check some variables

*/



IF ((@RunSetup = 0 OR @RunSetup IS NULL) AND (@UpdateSetup = 0 OR @UpdateSetup IS NULL))

	BEGIN

		RAISERROR('You have to either run setup or update setup. You can''t not do neither nor, if you follow. Or not.', 0, 1) WITH NOWAIT;

		RETURN;

	END;


/*

Should be a positive number

*/

IF (@RPOSeconds < 0)

		BEGIN
			RAISERROR('Please choose a positive number for @RPOSeconds', 0, 1) WITH NOWAIT;

			RETURN;
		END;


/*

Probably shouldn't be more than 20

*/

IF (@Jobs > 20) OR (@Jobs < 1)

		BEGIN
			RAISERROR('We advise sticking with 1-20 jobs.', 0, 1) WITH NOWAIT;

			RETURN;
		END;

/*

Probably shouldn't be more than 4 hours

*/

IF (@RPOSeconds >= 14400)
		BEGIN

			RAISERROR('If your RPO is really 4 hours, perhaps you''d be interested in a more modest recovery model, like SIMPLE?', 0, 1) WITH NOWAIT;

			RETURN;
		END;


/*

Can't enable both the backup and restore jobs at the same time

*/

IF @EnableBackupJobs = 1 AND @EnableRestoreJobs = 1
		BEGIN

			RAISERROR('You are not allowed to enable both the backup and restore jobs at the same time. Pick one, bucko.', 0, 1) WITH NOWAIT;

			RETURN;
		END;

/*
Make sure xp_cmdshell is enabled
*/
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell' AND value_in_use = 1)
		BEGIN 		
			RAISERROR('xp_cmdshell must be enabled so we can get directory contents to check for new databases to restore.', 0, 1) WITH NOWAIT
			
			RETURN;
		END 

/*
Make sure Ola Hallengren's scripts are installed in master
*/
IF 2 <> (SELECT COUNT(*) FROM master.sys.procedures WHERE name IN('CommandExecute', 'DatabaseBackup'))
		BEGIN 		
			RAISERROR('Ola Hallengren''s CommandExecute and DatabaseBackup must be installed in the master database. More info: http://ola.hallengren.com', 0, 1) WITH NOWAIT
			
			RETURN;
		END 

/*
Make sure sp_DatabaseRestore is installed in master
*/
IF NOT EXISTS (SELECT * FROM master.sys.procedures WHERE name = 'sp_DatabaseRestore')
		BEGIN 		
			RAISERROR('sp_DatabaseRestore must be installed in master. To get it: http://FirstResponderKit.org', 0, 1) WITH NOWAIT
			
			RETURN;
		END 

/*

Basic path sanity checks

*/

IF  (@BackupPath NOT LIKE '[c-zC-Z]:\%') --Local path, don't think anyone has A or B drives
AND (@BackupPath NOT LIKE '\\[a-zA-Z0-9]%\%') --UNC path
	
		BEGIN 		
				RAISERROR('Are you sure that''s a real path?', 0, 1) WITH NOWAIT;
				
				RETURN;
		END; 

/*

If you want to update the table, one of these has to not be NULL

*/

IF @UpdateSetup = 1
	AND (	 @RPOSeconds IS NULL 
		 AND @BackupPath IS NULL 
		 AND @RPOSeconds IS NULL 
		 AND @RestorePath IS NULL
         AND @EnableBackupJobs IS NULL
         AND @EnableRestoreJobs IS NULL
		)

		BEGIN

			RAISERROR('If you want to update configuration settings, they can''t be NULL. Please Make sure @RPOSeconds / @RTOSeconds or @BackupPath / @RestorePath has a value', 0, 1) WITH NOWAIT;

			RETURN;

		END;


IF @UpdateSetup = 1
	GOTO UpdateConfigs;

IF @RunSetup = 1
BEGIN
		BEGIN TRY

			BEGIN 
			

				/*
				
				First check to see if Agent is running -- we'll get errors if it's not
				
				*/
				
				
				IF ( SELECT 1
						FROM sys.all_objects
						WHERE name = 'dm_server_services' ) IS NOT NULL 

				BEGIN

				IF EXISTS (
							SELECT 1
							FROM sys.dm_server_services
							WHERE servicename LIKE 'SQL Server Agent%'
							AND status_desc = 'Stopped'		
						  )
					
					BEGIN
		
						RAISERROR('SQL Server Agent is not currently running -- it needs to be enabled to add backup worker jobs and the new database polling job', 0, 1) WITH NOWAIT;
						
						RETURN;
		
					END;
				
				END 
				

				BEGIN


						/*
						
						Check to see if the database exists

						*/
 
						RAISERROR('Checking for msdbCentral', 0, 1) WITH NOWAIT;

						SET @db_sql += N'

							IF DATABASEPROPERTYEX(' + QUOTENAME(@database_name, '''') + ', ''Status'') IS NULL

								BEGIN

									RAISERROR(''Creating msdbCentral'', 0, 1) WITH NOWAIT;

									CREATE DATABASE ' + QUOTENAME(@database_name) + ';
									
									ALTER DATABASE ' + QUOTENAME(@database_name) + ' SET RECOVERY FULL;
								
								END

							';


							IF @Debug = 1
								BEGIN 
									RAISERROR(@db_sql, 0, 1) WITH NOWAIT;
								END; 


							IF @db_sql IS NULL
								BEGIN
									RAISERROR('@db_sql is NULL for some reason', 0, 1) WITH NOWAIT;
								END; 


							EXEC sp_executesql @db_sql; 


						/*
						
						Check for tables and stuff

						*/

						
						RAISERROR('Checking for tables in msdbCentral', 0, 1) WITH NOWAIT;

							SET @tbl_sql += N'
							
									USE ' + QUOTENAME(@database_name) + '
									
									
									IF OBJECT_ID(''' + QUOTENAME(@database_name) + '.dbo.backup_configuration'') IS NULL
									
										BEGIN
										
										RAISERROR(''Creating table dbo.backup_configuration'', 0, 1) WITH NOWAIT;
											
											CREATE TABLE dbo.backup_configuration (
																			database_name NVARCHAR(256), 
																			configuration_name NVARCHAR(512), 
																			configuration_description NVARCHAR(512), 
																			configuration_setting NVARCHAR(MAX)
																			);
											
										END
										
									ELSE 
										
										BEGIN
											
											
											RAISERROR(''Backup configuration table exists, truncating'', 0, 1) WITH NOWAIT;
										
											
											TRUNCATE TABLE dbo.backup_configuration

										
										END


											RAISERROR(''Inserting configuration values'', 0, 1) WITH NOWAIT;

											
											INSERT dbo.backup_configuration (database_name, configuration_name, configuration_description, configuration_setting) 
															  VALUES (''all'', ''log backup frequency'', ''The length of time in second between Log Backups.'', ''' + CONVERT(NVARCHAR(10), @RPOSeconds) + ''');
											
											INSERT dbo.backup_configuration (database_name, configuration_name, configuration_description, configuration_setting) 
															  VALUES (''all'', ''log backup path'', ''The path to which Log Backups should go.'', ''' + @BackupPath + ''');									
									
											INSERT dbo.backup_configuration (database_name, configuration_name, configuration_description, configuration_setting) 
															  VALUES (''all'', ''change backup type'', ''For Ola Hallengren DatabaseBackup @ChangeBackupType param: Y = escalate to fulls, MSDB = escalate by checking msdb backup history.'', ''MSDB'');									
									
											INSERT dbo.backup_configuration (database_name, configuration_name, configuration_description, configuration_setting) 
															  VALUES (''all'', ''encrypt'', ''For Ola Hallengren DatabaseBackup: Y = encrypt the backup. N (default) = do not encrypt.'', NULL);									
									
											INSERT dbo.backup_configuration (database_name, configuration_name, configuration_description, configuration_setting) 
															  VALUES (''all'', ''encryptionalgorithm'', ''For Ola Hallengren DatabaseBackup: native 2014 choices include TRIPLE_DES_3KEY, AES_128, AES_192, AES_256.'', NULL);									
									
											INSERT dbo.backup_configuration (database_name, configuration_name, configuration_description, configuration_setting) 
															  VALUES (''all'', ''servercertificate'', ''For Ola Hallengren DatabaseBackup: server certificate that is used to encrypt the backup.'', NULL);									
									
																		
									IF OBJECT_ID(''' + QUOTENAME(@database_name) + '.dbo.backup_worker'') IS NULL
										
										BEGIN
										
										
											RAISERROR(''Creating table dbo.backup_worker'', 0, 1) WITH NOWAIT;
											
												CREATE TABLE dbo.backup_worker (
																				id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, 
																				database_name NVARCHAR(256), 
																				last_log_backup_start_time DATETIME DEFAULT ''19000101'', 
																				last_log_backup_finish_time DATETIME DEFAULT ''99991231'', 
																				is_started BIT DEFAULT 0, 
																				is_completed BIT DEFAULT 0, 
																				error_number INT DEFAULT NULL, 
																				last_error_date DATETIME DEFAULT NULL,
																				ignore_database BIT DEFAULT 0,
																				full_backup_required BIT DEFAULT ' + CASE WHEN @FirstFullBackup = 0 THEN N'0,' ELSE N'1,' END + CHAR(10) +
																			  N'diff_backup_required BIT DEFAULT ' + CASE WHEN @FirstDiffBackup = 0 THEN N'0' ELSE N'1' END + CHAR(10) +
																			  N');
											
										END;
									
									ELSE

										BEGIN


											RAISERROR(''Backup worker table exists, truncating'', 0, 1) WITH NOWAIT;
										
											
											TRUNCATE TABLE dbo.backup_worker


										END

											
											RAISERROR(''Inserting databases for backups'', 0, 1) WITH NOWAIT;
									
											INSERT ' + QUOTENAME(@database_name) + '.dbo.backup_worker (database_name) 
											SELECT d.name
											FROM sys.databases d
											WHERE NOT EXISTS (
												SELECT * 
												FROM msdbCentral.dbo.backup_worker bw
												WHERE bw.database_name = d.name
															)
											AND d.database_id > 4;
									
									';

							
							IF @Debug = 1
								BEGIN 
									SET @msg = SUBSTRING(@tbl_sql, 0, 2044)
									RAISERROR(@msg, 0, 1) WITH NOWAIT;
									SET @msg = SUBSTRING(@tbl_sql, 2044, 4088)
									RAISERROR(@msg, 0, 1) WITH NOWAIT;
									SET @msg = SUBSTRING(@tbl_sql, 4088, 6132)
									RAISERROR(@msg, 0, 1) WITH NOWAIT;
									SET @msg = SUBSTRING(@tbl_sql, 6132, 8176)
									RAISERROR(@msg, 0, 1) WITH NOWAIT;
								END; 

							
							IF @tbl_sql IS NULL
								BEGIN
									RAISERROR('@tbl_sql is NULL for some reason', 0, 1) WITH NOWAIT;
								END; 


							EXEC sp_executesql @tbl_sql;

						
						/*
						
						This section creates tables for restore workers to work off of
						
						*/

						
						/* 
						
						In search of msdb 
						
						*/
						
						RAISERROR('Checking for msdb. Yeah, I know...', 0, 1) WITH NOWAIT;
						
						IF DATABASEPROPERTYEX('msdb', 'Status') IS NULL

							BEGIN

									RAISERROR('YOU HAVE NO MSDB WHY?!', 0, 1) WITH NOWAIT;

							RETURN;

							END;

						
						/* In search of restore_configuration */

						RAISERROR('Checking for Restore Worker tables in msdb', 0, 1) WITH NOWAIT;

						IF OBJECT_ID('msdb.dbo.restore_configuration') IS NULL

							BEGIN

								RAISERROR('Creating restore_configuration table in msdb', 0, 1) WITH NOWAIT;

								CREATE TABLE msdb.dbo.restore_configuration (
																			database_name NVARCHAR(256), 
																			configuration_name NVARCHAR(512), 
																			configuration_description NVARCHAR(512), 
																			configuration_setting NVARCHAR(MAX)
																			);

							END;


						ELSE


							BEGIN

								RAISERROR('Restore configuration table exists, truncating', 0, 1) WITH NOWAIT;

								TRUNCATE TABLE msdb.dbo.restore_configuration;
						
							END;


								RAISERROR('Inserting configuration values to msdb.dbo.restore_configuration', 0, 1) WITH NOWAIT;
								
								INSERT msdb.dbo.restore_configuration (database_name, configuration_name, configuration_description, configuration_setting) 
												  VALUES ('all', 'log restore frequency', 'The length of time in second between Log Restores.', @RTOSeconds);
								
								INSERT msdb.dbo.restore_configuration (database_name, configuration_name, configuration_description, configuration_setting) 
												  VALUES ('all', 'log restore path', 'The path to which Log Restores come from.', @RestorePath);	


						IF OBJECT_ID('msdb.dbo.restore_worker') IS NULL
							
							BEGIN
							
							
								RAISERROR('Creating table msdb.dbo.restore_worker', 0, 1) WITH NOWAIT;
								
								CREATE TABLE msdb.dbo.restore_worker (
																		 id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, 
																		 database_name NVARCHAR(256), 
																		 last_log_restore_start_time DATETIME DEFAULT '19000101', 
																		 last_log_restore_finish_time DATETIME DEFAULT '99991231', 
																		 is_started BIT DEFAULT 0, 
																		 is_completed BIT DEFAULT 0, 
																		 error_number INT DEFAULT NULL, 
																		 last_error_date DATETIME DEFAULT NULL,
																		 ignore_database BIT DEFAULT 0,
																		 full_backup_required BIT DEFAULT 0,
																	     diff_backup_required BIT DEFAULT 0
																	     );

								
								RAISERROR('Inserting databases for restores', 0, 1) WITH NOWAIT;
						
								INSERT msdb.dbo.restore_worker (database_name) 
								SELECT d.name
								FROM sys.databases d
								WHERE NOT EXISTS (
									SELECT * 
									FROM msdb.dbo.restore_worker bw
									WHERE bw.database_name = d.name
												)
								AND d.database_id > 4;
							
							
							END;


		
		/*
		
		Add Jobs
		
		*/
		

		
		/*
		
		Look for our ten second schedule -- all jobs use this to restart themselves if they fail

		Fun fact: you can add the same schedule name multiple times, so we don't want to just stick it in there
		
		*/


		RAISERROR('Checking for ten second schedule', 0, 1) WITH NOWAIT;

			IF NOT EXISTS (
							SELECT 1 
							FROM msdb.dbo.sysschedules 
							WHERE name = 'ten_seconds'
						  )
			
				BEGIN
					
					
					RAISERROR('Creating ten second schedule', 0, 1) WITH NOWAIT;

					
					EXEC msdb.dbo.sp_add_schedule    @schedule_name= ten_seconds, 
													 @enabled = 1, 
													 @freq_type = 4, 
													 @freq_interval = 1, 
													 @freq_subday_type = 2,  
													 @freq_subday_interval = 10, 
													 @freq_relative_interval = 0, 
													 @freq_recurrence_factor = 0, 
													 @active_start_date = @active_start_date, 
													 @active_end_date = 99991231, 
													 @active_start_time = 0, 
													 @active_end_time = 235959;
				
				END;
		
			
			/*
			
			Look for Backup Pollster job -- this job sets up our watcher for new databases to back up
			
			*/

			
			RAISERROR('Checking for pollster job', 0, 1) WITH NOWAIT;

			
			IF NOT EXISTS (
							SELECT 1 
							FROM msdb.dbo.sysjobs 
							WHERE name = 'sp_AllNightLog_PollForNewDatabases'
						  )
		
				
				BEGIN
					
					
					RAISERROR('Creating pollster job', 0, 1) WITH NOWAIT;

						IF @EnableBackupJobs = 1
                            BEGIN
						    EXEC msdb.dbo.sp_add_job @job_name = sp_AllNightLog_PollForNewDatabases, 
												     @description = 'This is a worker for the purposes of polling sys.databases for new entries to insert to the worker queue table.', 
												     @category_name = 'Database Maintenance', 
												     @owner_login_name = 'sa',
												     @enabled = 1;
                            END		
                        ELSE			
                            BEGIN
						    EXEC msdb.dbo.sp_add_job @job_name = sp_AllNightLog_PollForNewDatabases, 
												     @description = 'This is a worker for the purposes of polling sys.databases for new entries to insert to the worker queue table.', 
												     @category_name = 'Database Maintenance', 
												     @owner_login_name = 'sa',
												     @enabled = 0;
                            END		
					
					
					RAISERROR('Adding job step', 0, 1) WITH NOWAIT;

						
						EXEC msdb.dbo.sp_add_jobstep @job_name = sp_AllNightLog_PollForNewDatabases, 
													 @step_name = sp_AllNightLog_PollForNewDatabases, 
													 @subsystem = 'TSQL', 
													 @command = 'EXEC sp_AllNightLog @PollForNewDatabases = 1';
					
					
					
					RAISERROR('Adding job server', 0, 1) WITH NOWAIT;

						
						EXEC msdb.dbo.sp_add_jobserver @job_name = sp_AllNightLog_PollForNewDatabases;

					
									
					RAISERROR('Attaching schedule', 0, 1) WITH NOWAIT;
		
						
						EXEC msdb.dbo.sp_attach_schedule @job_name = sp_AllNightLog_PollForNewDatabases, 
														 @schedule_name = ten_seconds;
		
				
				END;	
				


			/*
			
			Look for Restore Pollster job -- this job sets up our watcher for new databases to back up
			
			*/

			
			RAISERROR('Checking for restore pollster job', 0, 1) WITH NOWAIT;

			
			IF NOT EXISTS (
							SELECT 1 
							FROM msdb.dbo.sysjobs 
							WHERE name = 'sp_AllNightLog_PollDiskForNewDatabases'
						  )
		
				
				BEGIN
					
					
					RAISERROR('Creating restore pollster job', 0, 1) WITH NOWAIT;

						
						IF @EnableRestoreJobs = 1
                            BEGIN
						    EXEC msdb.dbo.sp_add_job @job_name = sp_AllNightLog_PollDiskForNewDatabases, 
												     @description = 'This is a worker for the purposes of polling your restore path for new entries to insert to the worker queue table.', 
												     @category_name = 'Database Maintenance', 
												     @owner_login_name = 'sa',
												     @enabled = 1;
                            END
                        ELSE
                            BEGIN
						    EXEC msdb.dbo.sp_add_job @job_name = sp_AllNightLog_PollDiskForNewDatabases, 
												     @description = 'This is a worker for the purposes of polling your restore path for new entries to insert to the worker queue table.', 
												     @category_name = 'Database Maintenance', 
												     @owner_login_name = 'sa',
												     @enabled = 0;
                            END
					
					
					
					RAISERROR('Adding restore job step', 0, 1) WITH NOWAIT;

						
						EXEC msdb.dbo.sp_add_jobstep @job_name = sp_AllNightLog_PollDiskForNewDatabases, 
													 @step_name = sp_AllNightLog_PollDiskForNewDatabases, 
													 @subsystem = 'TSQL', 
													 @command = 'EXEC sp_AllNightLog @PollDiskForNewDatabases = 1';
					
					
					
					RAISERROR('Adding restore job server', 0, 1) WITH NOWAIT;

						
						EXEC msdb.dbo.sp_add_jobserver @job_name = sp_AllNightLog_PollDiskForNewDatabases;

					
									
					RAISERROR('Attaching schedule', 0, 1) WITH NOWAIT;
		
						
						EXEC msdb.dbo.sp_attach_schedule @job_name = sp_AllNightLog_PollDiskForNewDatabases, 
														 @schedule_name = ten_seconds;
		
				
				END;	



				/*
				
				This section creates @Jobs (quantity) of worker jobs to take log backups with

				They work in a queue

				It's queuete
				
				*/


				RAISERROR('Checking for sp_AllNightLog backup jobs', 0, 1) WITH NOWAIT;
				
					
					SELECT @counter = COUNT(*) + 1 
					FROM msdb.dbo.sysjobs 
					WHERE name LIKE 'sp[_]AllNightLog[_]Backup[_]%';

					SET @msg = 'Found ' + CONVERT(NVARCHAR(10), (@counter - 1)) + ' backup jobs -- ' +  CASE WHEN @counter < @Jobs THEN + 'starting loop!'
																											 WHEN @counter >= @Jobs THEN 'skipping loop!'
																											 ELSE 'Oh woah something weird happened!'
																										END;	

					RAISERROR(@msg, 0, 1) WITH NOWAIT;

					
							WHILE @counter <= @Jobs

							
								BEGIN

									
										RAISERROR('Setting job name', 0, 1) WITH NOWAIT;

											SET @job_name_backups = N'sp_AllNightLog_Backup_' + CASE WHEN @counter < 10 THEN N'0' + CONVERT(NVARCHAR(10), @counter)
																									 WHEN @counter >= 10 THEN CONVERT(NVARCHAR(10), @counter)
																								END; 
							
										
										RAISERROR('Setting @job_sql', 0, 1) WITH NOWAIT;

										
											SET @job_sql = N'
							
											EXEC msdb.dbo.sp_add_job @job_name = ' + @job_name_backups + ', 
																	 @description = ' + @job_description_backups + ', 
																	 @category_name = ' + @job_category + ', 
																	 @owner_login_name = ' + @job_owner + ',';
                                            IF @EnableBackupJobs = 1
                                                BEGIN
                                                SET @job_sql = @job_sql + ' @enabled = 1; ';
                                                END
                                            ELSE
                                                BEGIN
                                                SET @job_sql = @job_sql + ' @enabled = 0; ';
                                                END
								  
											
                                            SET @job_sql = @job_sql + '
											EXEC msdb.dbo.sp_add_jobstep @job_name = ' + @job_name_backups + ', 
																		 @step_name = ' + @job_name_backups + ', 
																		 @subsystem = ''TSQL'', 
																		 @command = ' + @job_command_backups + ';
								  
											
											EXEC msdb.dbo.sp_add_jobserver @job_name = ' + @job_name_backups + ';
											
											
											EXEC msdb.dbo.sp_attach_schedule  @job_name = ' + @job_name_backups + ', 
																			  @schedule_name = ten_seconds;
											
											';
							
										
										SET @counter += 1;

										
											IF @Debug = 1
												BEGIN 
													RAISERROR(@job_sql, 0, 1) WITH NOWAIT;
												END; 		

		
											IF @job_sql IS NULL
											BEGIN
												RAISERROR('@job_sql is NULL for some reason', 0, 1) WITH NOWAIT;
											END; 


										EXEC sp_executesql @job_sql;

							
								END;		



				/*
				
				This section creates @Jobs (quantity) of worker jobs to restore logs with

				They too work in a queue

				Like a queue-t 3.14
				
				*/


				RAISERROR('Checking for sp_AllNightLog Restore jobs', 0, 1) WITH NOWAIT;
				
					
					SELECT @counter = COUNT(*) + 1 
					FROM msdb.dbo.sysjobs 
					WHERE name LIKE 'sp[_]AllNightLog[_]Restore[_]%';

					SET @msg = 'Found ' + CONVERT(NVARCHAR(10), (@counter - 1)) + ' restore jobs -- ' +  CASE WHEN @counter < @Jobs THEN + 'starting loop!'
																											  WHEN @counter >= @Jobs THEN 'skipping loop!'
																											  ELSE 'Oh woah something weird happened!'
																										 END;	

					RAISERROR(@msg, 0, 1) WITH NOWAIT;

					
							WHILE @counter <= @Jobs

							
								BEGIN

									
										RAISERROR('Setting job name', 0, 1) WITH NOWAIT;

											SET @job_name_restores = N'sp_AllNightLog_Restore_' + CASE WHEN @counter < 10 THEN N'0' + CONVERT(NVARCHAR(10), @counter)
																									   WHEN @counter >= 10 THEN CONVERT(NVARCHAR(10), @counter)
																								  END; 
							
										
										RAISERROR('Setting @job_sql', 0, 1) WITH NOWAIT;

										
											SET @job_sql = N'
							
											EXEC msdb.dbo.sp_add_job @job_name = ' + @job_name_restores + ', 
																	 @description = ' + @job_description_restores + ', 
																	 @category_name = ' + @job_category + ', 
																	 @owner_login_name = ' + @job_owner + ',';
                                            IF @EnableRestoreJobs = 1
                                                BEGIN
                                                SET @job_sql = @job_sql + ' @enabled = 1; ';
                                                END
                                            ELSE
                                                BEGIN
                                                SET @job_sql = @job_sql + ' @enabled = 0; ';
                                                END
								  
											
                                            SET @job_sql = @job_sql + '
											
											EXEC msdb.dbo.sp_add_jobstep @job_name = ' + @job_name_restores + ', 
																		 @step_name = ' + @job_name_restores + ', 
																		 @subsystem = ''TSQL'', 
																		 @command = ' + @job_command_restores + ';
								  
											
											EXEC msdb.dbo.sp_add_jobserver @job_name = ' + @job_name_restores + ';
											
											
											EXEC msdb.dbo.sp_attach_schedule  @job_name = ' + @job_name_restores + ', 
																			  @schedule_name = ten_seconds;
											
											';
							
										
										SET @counter += 1;

										
											IF @Debug = 1
												BEGIN 
													RAISERROR(@job_sql, 0, 1) WITH NOWAIT;
												END; 		

		
											IF @job_sql IS NULL
											BEGIN
												RAISERROR('@job_sql is NULL for some reason', 0, 1) WITH NOWAIT;
											END; 


										EXEC sp_executesql @job_sql;

							
								END;		


		RAISERROR('Setup complete!', 0, 1) WITH NOWAIT;
		
			END; --End for the Agent job creation

		END;--End for Database and Table creation

	END TRY

	BEGIN CATCH


		SELECT @msg = N'Error occurred during setup: ' + CONVERT(NVARCHAR(10), ERROR_NUMBER()) + ', error message is ' + ERROR_MESSAGE(), 
			   @error_severity = ERROR_SEVERITY(), 
			   @error_state = ERROR_STATE();
		
		RAISERROR(@msg, @error_severity, @error_state) WITH NOWAIT;


		WHILE @@TRANCOUNT > 0
			ROLLBACK;

	END CATCH;

END;  /* IF @RunSetup = 1 */

RETURN;


UpdateConfigs:

IF @UpdateSetup = 1
	
	BEGIN

        /* If we're enabling backup jobs, we may need to run restore with recovery on msdbCentral to bring it online: */
        IF @EnableBackupJobs = 1 AND EXISTS (SELECT * FROM sys.databases WHERE name = 'msdbCentral' AND state = 1)
            BEGIN 
				RAISERROR('msdbCentral exists, but is in restoring state. Running restore with recovery...', 0, 1) WITH NOWAIT;

                BEGIN TRY
                    RESTORE DATABASE [msdbCentral] WITH RECOVERY;
                END TRY

				BEGIN CATCH

					SELECT @error_number = ERROR_NUMBER(), 
							@error_severity = ERROR_SEVERITY(), 
							@error_state = ERROR_STATE();

					SELECT @msg = N'Error running restore with recovery on msdbCentral, error number is ' + CONVERT(NVARCHAR(10), ERROR_NUMBER()) + ', error message is ' + ERROR_MESSAGE(), 
							@error_severity = ERROR_SEVERITY(), 
							@error_state = ERROR_STATE();
						
					RAISERROR(@msg, @error_severity, @error_state) WITH NOWAIT;

				END CATCH;

            END

            /* Only check for this after trying to restore msdbCentral: */
            IF @EnableBackupJobs = 1 AND NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'msdbCentral' AND state = 0)
                    BEGIN
        				RAISERROR('msdbCentral is not online. Repair that first, then try to enable backup jobs.', 0, 1) WITH NOWAIT;
                        RETURN
                    END


			IF OBJECT_ID('msdbCentral.dbo.backup_configuration') IS NOT NULL

				RAISERROR('Found backup config, checking variables...', 0, 1) WITH NOWAIT;
	
				BEGIN

					BEGIN TRY

						
						IF @RPOSeconds IS NOT NULL


							BEGIN

								RAISERROR('Attempting to update RPO setting', 0, 1) WITH NOWAIT;

								UPDATE c
										SET c.configuration_setting = CONVERT(NVARCHAR(10), @RPOSeconds)
								FROM msdbCentral.dbo.backup_configuration AS c
								WHERE c.configuration_name = N'log backup frequency';

							END;

						
						IF @BackupPath IS NOT NULL

							BEGIN
								
								RAISERROR('Attempting to update Backup Path setting', 0, 1) WITH NOWAIT;

								UPDATE c
										SET c.configuration_setting = @BackupPath
								FROM msdbCentral.dbo.backup_configuration AS c
								WHERE c.configuration_name = N'log backup path';


							END;

					END TRY


					BEGIN CATCH


						SELECT @error_number = ERROR_NUMBER(), 
							   @error_severity = ERROR_SEVERITY(), 
							   @error_state = ERROR_STATE();

						SELECT @msg = N'Error updating backup configuration setting, error number is ' + CONVERT(NVARCHAR(10), ERROR_NUMBER()) + ', error message is ' + ERROR_MESSAGE(), 
							   @error_severity = ERROR_SEVERITY(), 
							   @error_state = ERROR_STATE();
						
						RAISERROR(@msg, @error_severity, @error_state) WITH NOWAIT;


					END CATCH;

			END;


			IF OBJECT_ID('msdb.dbo.restore_configuration') IS NOT NULL

				RAISERROR('Found restore config, checking variables...', 0, 1) WITH NOWAIT;

				BEGIN

					BEGIN TRY

                        EXEC msdb.dbo.sp_update_schedule @name = ten_seconds, @active_start_date = @active_start_date, @active_start_time = 000000;

                        IF @EnableRestoreJobs IS NOT NULL
                            BEGIN
            				RAISERROR('Changing restore job status based on @EnableBackupJobs parameter...', 0, 1) WITH NOWAIT;
                            INSERT INTO @jobs_to_change(name)
                                SELECT name 
                                FROM msdb.dbo.sysjobs 
                                WHERE name LIKE 'sp_AllNightLog_Restore%' OR name = 'sp_AllNightLog_PollDiskForNewDatabases';
                            DECLARE jobs_cursor CURSOR FOR  
                                SELECT name 
                                FROM @jobs_to_change

                            OPEN jobs_cursor   
                            FETCH NEXT FROM jobs_cursor INTO @current_job_name   

                            WHILE @@FETCH_STATUS = 0   
                            BEGIN   
            				       RAISERROR(@current_job_name, 0, 1) WITH NOWAIT;
                                   EXEC msdb.dbo.sp_update_job @job_name=@current_job_name,@enabled = @EnableRestoreJobs;
                                   FETCH NEXT FROM jobs_cursor INTO @current_job_name   
                            END   

                            CLOSE jobs_cursor   
                            DEALLOCATE jobs_cursor
                            DELETE @jobs_to_change;
                            END;

                        /* If they wanted to turn off restore jobs, wait to make sure that finishes before we start enabling the backup jobs */
                        IF @EnableRestoreJobs = 0
                            BEGIN
                            SET @started_waiting_for_jobs = GETDATE();
                            SELECT  @counter = COUNT(*)
		                            FROM    [msdb].[dbo].[sysjobactivity] [ja]
		                            INNER JOIN [msdb].[dbo].[sysjobs] [j]
			                            ON [ja].[job_id] = [j].[job_id]
		                            WHERE    [ja].[session_id] = (
										                            SELECT    TOP 1 [session_id]
										                            FROM    [msdb].[dbo].[syssessions]
										                            ORDER BY [agent_start_date] DESC
									                            )
				                            AND [start_execution_date] IS NOT NULL
				                            AND [stop_execution_date] IS NULL
				                            AND [j].[name] LIKE 'sp_AllNightLog_Restore%';

                            WHILE @counter > 0
                                BEGIN
                                    IF DATEADD(SS, 120, @started_waiting_for_jobs) < GETDATE()
                                        BEGIN
                                        RAISERROR('OH NOES! We waited 2 minutes and restore jobs are still running. We are stopping here - get a meatbag involved to figure out if restore jobs need to be killed, and the backup jobs will need to be enabled manually.', 16, 1) WITH NOWAIT;
                                        RETURN
                                        END
                                    SET @msg = N'Waiting for ' + CAST(@counter AS NVARCHAR(100)) + N' sp_AllNightLog_Restore job(s) to finish.'
                                    RAISERROR(@msg, 0, 1) WITH NOWAIT;
                                    WAITFOR DELAY '0:00:01'; -- Wait until the restore jobs are fully stopped
	
	                                SELECT  @counter = COUNT(*)
		                                FROM    [msdb].[dbo].[sysjobactivity] [ja]
		                                INNER JOIN [msdb].[dbo].[sysjobs] [j]
			                                ON [ja].[job_id] = [j].[job_id]
		                                WHERE    [ja].[session_id] = (
										                                SELECT    TOP 1 [session_id]
										                                FROM    [msdb].[dbo].[syssessions]
										                                ORDER BY [agent_start_date] DESC
									                                )
				                                AND [start_execution_date] IS NOT NULL
				                                AND [stop_execution_date] IS NULL
				                                AND [j].[name] LIKE 'sp_AllNightLog_Restore%';
                                END
                            END /* IF @EnableRestoreJobs = 0 */


                        IF @EnableBackupJobs IS NOT NULL
                            BEGIN
            				RAISERROR('Changing backup job status based on @EnableBackupJobs parameter...', 0, 1) WITH NOWAIT;
                            INSERT INTO @jobs_to_change(name)
                                SELECT name 
                                FROM msdb.dbo.sysjobs 
                                WHERE name LIKE 'sp_AllNightLog_Backup%' OR name = 'sp_AllNightLog_PollForNewDatabases';
                            DECLARE jobs_cursor CURSOR FOR  
                                SELECT name 
                                FROM @jobs_to_change

                            OPEN jobs_cursor   
                            FETCH NEXT FROM jobs_cursor INTO @current_job_name   

                            WHILE @@FETCH_STATUS = 0   
                            BEGIN   
            				       RAISERROR(@current_job_name, 0, 1) WITH NOWAIT;
                                   EXEC msdb.dbo.sp_update_job @job_name=@current_job_name,@enabled = @EnableBackupJobs;
                                   FETCH NEXT FROM jobs_cursor INTO @current_job_name   
                            END   

                            CLOSE jobs_cursor   
                            DEALLOCATE jobs_cursor
                            DELETE @jobs_to_change;
                            END;


						
						IF @RTOSeconds IS NOT NULL

							BEGIN

								RAISERROR('Attempting to update RTO setting', 0, 1) WITH NOWAIT;

								UPDATE c
										SET c.configuration_setting = CONVERT(NVARCHAR(10), @RTOSeconds)
								FROM msdb.dbo.restore_configuration AS c
								WHERE c.configuration_name = N'log restore frequency';

							END;

						
						IF @RestorePath IS NOT NULL

							BEGIN
								
								RAISERROR('Attempting to update Restore Path setting', 0, 1) WITH NOWAIT;

								UPDATE c
										SET c.configuration_setting = @RestorePath
								FROM msdb.dbo.restore_configuration AS c
								WHERE c.configuration_name = N'log restore path';


							END;

					END TRY


					BEGIN CATCH


						SELECT @error_number = ERROR_NUMBER(), 
							   @error_severity = ERROR_SEVERITY(), 
							   @error_state = ERROR_STATE();

						SELECT @msg = N'Error updating restore configuration setting, error number is ' + CONVERT(NVARCHAR(10), ERROR_NUMBER()) + ', error message is ' + ERROR_MESSAGE(), 
							   @error_severity = ERROR_SEVERITY(), 
							   @error_state = ERROR_STATE();
						
						RAISERROR(@msg, @error_severity, @error_state) WITH NOWAIT;


					END CATCH;

				END;

				RAISERROR('Update complete!', 0, 1) WITH NOWAIT;

			RETURN;

	END; --End updates to configuration table


END; -- Final END for stored proc
GO