sp_BlitzBackups.sql

IF OBJECT_ID('dbo.sp_BlitzBackups') IS NULL
  EXEC ('CREATE PROCEDURE dbo.sp_BlitzBackups AS RETURN 0;');
GO
ALTER PROCEDURE [dbo].[sp_BlitzBackups]
    @Help TINYINT = 0 ,
	@HoursBack INT = 168,
	@MSDBName NVARCHAR(256) = 'msdb',
	@AGName NVARCHAR(256) = NULL,
	@RestoreSpeedFullMBps INT = NULL,
	@RestoreSpeedDiffMBps INT = NULL,
	@RestoreSpeedLogMBps INT = NULL,
	@Debug TINYINT = 0,
	@PushBackupHistoryToListener BIT = 0,
	@WriteBackupsToListenerName NVARCHAR(256) = NULL,
    @WriteBackupsToDatabaseName NVARCHAR(256) = NULL,
    @WriteBackupsLastHours INT = 168,
    @Version     VARCHAR(30) = NULL OUTPUT,
	@VersionDate DATETIME = NULL OUTPUT,
    @VersionCheckMode BIT = 0
WITH RECOMPILE
AS
	BEGIN
    SET NOCOUNT ON;
	SET STATISTICS XML OFF;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	
	SELECT @Version = '8.19', @VersionDate = '20240222';
	
	IF(@VersionCheckMode = 1)
	BEGIN
		RETURN;
	END;

	IF @Help = 1 PRINT '
	/*
	sp_BlitzBackups from http://FirstResponderKit.org
	
	This script checks your backups to see how much data you might lose when
	this server fails, and how long it might take to recover.

	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.

	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:

	@HoursBack INT = 168		How many hours of history to examine, back from now.
								You can check just the last 24 hours of backups, for example.
	@MSDBName NVARCHAR(255) 	You can restore MSDB from different servers and check them
								centrally. Also useful if you create a DBA utility database
								and merge data from several servers in an AG into one DB.
	@RestoreSpeedFullMBps INT	By default, we use the backup speed from MSDB to guesstimate
								how fast your restores will go. If you have done performance
								tuning and testing of your backups (or if they horribly go even
								slower in your DR environment, and you want to account for
								that), then you can pass in different numbers here.
	@RestoreSpeedDiffMBps INT	See above.
	@RestoreSpeedLogMBps INT	See above.

	For more documentation: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/

    MIT License
	
	Copyright (c) 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.




	*/';
ELSE
BEGIN
DECLARE @StringToExecute NVARCHAR(MAX) = N'', 
		@InnerStringToExecute NVARCHAR(MAX) = N'',
		@ProductVersion NVARCHAR(128), 
		@ProductVersionMajor DECIMAL(10, 2),
        @ProductVersionMinor DECIMAL(10, 2), 
		@StartTime DATETIME2, @ResultText NVARCHAR(MAX), 
		@crlf NVARCHAR(2),
        @MoreInfoHeader NVARCHAR(100), 
		@MoreInfoFooter NVARCHAR(100);

IF @HoursBack > 0
    SET @HoursBack = @HoursBack * -1;

IF	@WriteBackupsLastHours > 0
    SET @WriteBackupsLastHours = @WriteBackupsLastHours * -1;

SELECT  @crlf = NCHAR(13) + NCHAR(10), 
		@StartTime = DATEADD(hh, @HoursBack, GETDATE()),
        @MoreInfoHeader = N'<?ClickToSeeDetails -- ' + @crlf, @MoreInfoFooter = @crlf + N' -- ?>';

SET @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @ProductVersionMajor = SUBSTRING(@ProductVersion, 1, CHARINDEX('.', @ProductVersion) + 1),
       @ProductVersionMinor = PARSENAME(CONVERT(VARCHAR(32), @ProductVersion), 2);

CREATE TABLE #Backups
(
    id INT IDENTITY(1, 1),
    database_name NVARCHAR(128),
    database_guid UNIQUEIDENTIFIER,
    RPOWorstCaseMinutes DECIMAL(18, 1),
    RTOWorstCaseMinutes DECIMAL(18, 1),
    RPOWorstCaseBackupSetID INT,
    RPOWorstCaseBackupSetFinishTime DATETIME,
    RPOWorstCaseBackupSetIDPrior INT,
    RPOWorstCaseBackupSetPriorFinishTime DATETIME,
    RPOWorstCaseMoreInfoQuery XML,
    RTOWorstCaseBackupFileSizeMB DECIMAL(18, 2),
    RTOWorstCaseMoreInfoQuery XML,
    FullMBpsAvg DECIMAL(18, 2),
    FullMBpsMin DECIMAL(18, 2),
    FullMBpsMax DECIMAL(18, 2),
    FullSizeMBAvg DECIMAL(18, 2),
    FullSizeMBMin DECIMAL(18, 2),
    FullSizeMBMax DECIMAL(18, 2),
    FullCompressedSizeMBAvg DECIMAL(18, 2),
    FullCompressedSizeMBMin DECIMAL(18, 2),
    FullCompressedSizeMBMax DECIMAL(18, 2),
    DiffMBpsAvg DECIMAL(18, 2),
    DiffMBpsMin DECIMAL(18, 2),
    DiffMBpsMax DECIMAL(18, 2),
    DiffSizeMBAvg DECIMAL(18, 2),
    DiffSizeMBMin DECIMAL(18, 2),
    DiffSizeMBMax DECIMAL(18, 2),
    DiffCompressedSizeMBAvg DECIMAL(18, 2),
    DiffCompressedSizeMBMin DECIMAL(18, 2),
    DiffCompressedSizeMBMax DECIMAL(18, 2),
    LogMBpsAvg DECIMAL(18, 2),
    LogMBpsMin DECIMAL(18, 2),
    LogMBpsMax DECIMAL(18, 2),
    LogSizeMBAvg DECIMAL(18, 2),
    LogSizeMBMin DECIMAL(18, 2),
    LogSizeMBMax DECIMAL(18, 2),
    LogCompressedSizeMBAvg DECIMAL(18, 2),
    LogCompressedSizeMBMin DECIMAL(18, 2),
    LogCompressedSizeMBMax DECIMAL(18, 2)
);

CREATE TABLE #RTORecoveryPoints
(
    id INT IDENTITY(1, 1),
    database_name NVARCHAR(128),
    database_guid UNIQUEIDENTIFIER,
    rto_worst_case_size_mb AS
        ( COALESCE(log_file_size_mb, 0) + COALESCE(diff_file_size_mb, 0) + COALESCE(full_file_size_mb, 0)),
    rto_worst_case_time_seconds AS
        ( COALESCE(log_time_seconds, 0) + COALESCE(diff_time_seconds, 0) + COALESCE(full_time_seconds, 0)),
    full_backup_set_id INT,
    full_last_lsn NUMERIC(25, 0),
    full_backup_set_uuid UNIQUEIDENTIFIER,
    full_time_seconds BIGINT,
    full_file_size_mb DECIMAL(18, 2),
    diff_backup_set_id INT,
    diff_last_lsn NUMERIC(25, 0),
    diff_time_seconds BIGINT,
    diff_file_size_mb DECIMAL(18, 2),
    log_backup_set_id INT,
    log_last_lsn NUMERIC(25, 0),
    log_time_seconds BIGINT,
    log_file_size_mb DECIMAL(18, 2),
    log_backups INT
);

CREATE TABLE #Recoverability
	(
		Id INT IDENTITY ,
		DatabaseName NVARCHAR(128),
		DatabaseGUID UNIQUEIDENTIFIER,
		LastBackupRecoveryModel NVARCHAR(60),
		FirstFullBackupSizeMB DECIMAL (18,2),
		FirstFullBackupDate DATETIME,
		LastFullBackupSizeMB DECIMAL (18,2),
		LastFullBackupDate DATETIME,
		AvgFullBackupThroughputMB DECIMAL (18,2),
		AvgFullBackupDurationSeconds INT,
		AvgDiffBackupThroughputMB DECIMAL (18,2),
		AvgDiffBackupDurationSeconds INT,
		AvgLogBackupThroughputMB DECIMAL (18,2),
		AvgLogBackupDurationSeconds INT,
		AvgFullSizeMB DECIMAL (18,2),
		AvgDiffSizeMB DECIMAL (18,2),
		AvgLogSizeMB DECIMAL (18,2),
		IsBigDiff AS CASE WHEN (AvgFullSizeMB > 10240. AND ((AvgDiffSizeMB * 100.) / AvgFullSizeMB >= 40.))  THEN 1 ELSE 0 END,
		IsBigLog AS CASE WHEN (AvgFullSizeMB > 10240. AND ((AvgLogSizeMB * 100.) / AvgFullSizeMB >= 20.)) THEN 1 ELSE 0 END
	);

CREATE TABLE #Trending
(
    DatabaseName NVARCHAR(128),
	DatabaseGUID UNIQUEIDENTIFIER,
    [0] DECIMAL(18, 2),
    [-1] DECIMAL(18, 2),
    [-2] DECIMAL(18, 2),
    [-3] DECIMAL(18, 2),
    [-4] DECIMAL(18, 2),
    [-5] DECIMAL(18, 2),
    [-6] DECIMAL(18, 2),
    [-7] DECIMAL(18, 2),
    [-8] DECIMAL(18, 2),
    [-9] DECIMAL(18, 2),
    [-10] DECIMAL(18, 2),
    [-11] DECIMAL(18, 2),
    [-12] DECIMAL(18, 2)
);


CREATE TABLE #Warnings
(
    Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    CheckId INT,
    Priority INT,
    DatabaseName VARCHAR(128),
    Finding VARCHAR(256),
    Warning VARCHAR(8000)
);

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @MSDBName)
	BEGIN
	RAISERROR('@MSDBName was specified, but the database does not exist.', 16, 1) WITH NOWAIT;
	RETURN;
	END

IF @PushBackupHistoryToListener = 1
GOTO PushBackupHistoryToListener


	RAISERROR('Inserting to #Backups', 0, 1) WITH NOWAIT;

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'WITH Backups AS (SELECT bs.database_name, bs.database_guid, bs.type AS backup_type ' + @crlf
		+ ' , MBpsAvg = CAST(AVG(( bs.backup_size / ( CASE WHEN DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) = 0 THEN 1 ELSE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) END ) / 1048576 )) AS INT) ' + @crlf
		+ ' , MBpsMin = CAST(MIN(( bs.backup_size / ( CASE WHEN DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) = 0 THEN 1 ELSE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) END ) / 1048576 )) AS INT) ' + @crlf
		+ ' , MBpsMax = CAST(MAX(( bs.backup_size / ( CASE WHEN DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) = 0 THEN 1 ELSE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) END ) / 1048576 )) AS INT) ' + @crlf
		+ ' , SizeMBAvg = AVG(backup_size / 1048576.0) ' + @crlf
		+ ' , SizeMBMin = MIN(backup_size / 1048576.0) ' + @crlf
		+ ' , SizeMBMax = MAX(backup_size / 1048576.0) ' + @crlf
		+ ' , CompressedSizeMBAvg = AVG(compressed_backup_size / 1048576.0) ' + @crlf
		+ ' , CompressedSizeMBMin = MIN(compressed_backup_size / 1048576.0) ' + @crlf
		+ ' , CompressedSizeMBMax = MAX(compressed_backup_size / 1048576.0) ' + @crlf;


	SET @StringToExecute += N' FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bs ' + @crlf
		+ N' WHERE bs.backup_finish_date >= @StartTime AND bs.is_damaged = 0 ' + @crlf
		+ N' GROUP BY bs.database_name, bs.database_guid, bs.type)' + @crlf;

	SET @StringToExecute += + N'INSERT INTO #Backups(database_name, database_guid, ' + @crlf
		+ N' FullMBpsAvg, FullMBpsMin, FullMBpsMax, FullSizeMBAvg, FullSizeMBMin, FullSizeMBMax, FullCompressedSizeMBAvg, FullCompressedSizeMBMin, FullCompressedSizeMBMax, ' + @crlf
		+ N' DiffMBpsAvg, DiffMBpsMin, DiffMBpsMax, DiffSizeMBAvg, DiffSizeMBMin, DiffSizeMBMax, DiffCompressedSizeMBAvg, DiffCompressedSizeMBMin, DiffCompressedSizeMBMax, ' + @crlf
		+ N' LogMBpsAvg, LogMBpsMin, LogMBpsMax, LogSizeMBAvg, LogSizeMBMin, LogSizeMBMax, LogCompressedSizeMBAvg, LogCompressedSizeMBMin, LogCompressedSizeMBMax ) ' + @crlf
		+ N'SELECT bF.database_name, bF.database_guid ' + @crlf
		+ N' , bF.MBpsAvg AS FullMBpsAvg ' + @crlf
		+ N' , bF.MBpsMin AS FullMBpsMin ' + @crlf
		+ N' , bF.MBpsMax AS FullMBpsMax ' + @crlf
		+ N' , bF.SizeMBAvg AS FullSizeMBAvg ' + @crlf
		+ N' , bF.SizeMBMin AS FullSizeMBMin ' + @crlf
		+ N' , bF.SizeMBMax AS FullSizeMBMax ' + @crlf
		+ N' , bF.CompressedSizeMBAvg AS FullCompressedSizeMBAvg ' + @crlf
		+ N' , bF.CompressedSizeMBMin AS FullCompressedSizeMBMin ' + @crlf
		+ N' , bF.CompressedSizeMBMax AS FullCompressedSizeMBMax ' + @crlf
		+ N' , bD.MBpsAvg AS DiffMBpsAvg ' + @crlf
		+ N' , bD.MBpsMin AS DiffMBpsMin ' + @crlf
		+ N' , bD.MBpsMax AS DiffMBpsMax ' + @crlf
		+ N' , bD.SizeMBAvg AS DiffSizeMBAvg ' + @crlf
		+ N' , bD.SizeMBMin AS DiffSizeMBMin ' + @crlf
		+ N' , bD.SizeMBMax AS DiffSizeMBMax ' + @crlf
		+ N' , bD.CompressedSizeMBAvg AS DiffCompressedSizeMBAvg ' + @crlf
		+ N' , bD.CompressedSizeMBMin AS DiffCompressedSizeMBMin ' + @crlf
		+ N' , bD.CompressedSizeMBMax AS DiffCompressedSizeMBMax ' + @crlf
		+ N' , bL.MBpsAvg AS LogMBpsAvg ' + @crlf
		+ N' , bL.MBpsMin AS LogMBpsMin ' + @crlf
		+ N' , bL.MBpsMax AS LogMBpsMax ' + @crlf
		+ N' , bL.SizeMBAvg AS LogSizeMBAvg ' + @crlf
		+ N' , bL.SizeMBMin AS LogSizeMBMin ' + @crlf
		+ N' , bL.SizeMBMax AS LogSizeMBMax ' + @crlf
		+ N' , bL.CompressedSizeMBAvg AS LogCompressedSizeMBAvg ' + @crlf
		+ N' , bL.CompressedSizeMBMin AS LogCompressedSizeMBMin ' + @crlf
		+ N' , bL.CompressedSizeMBMax AS LogCompressedSizeMBMax ' + @crlf
		+ N' FROM Backups bF ' + @crlf
		+ N' LEFT OUTER JOIN Backups bD ON bF.database_name = bD.database_name AND bF.database_guid = bD.database_guid AND bD.backup_type = ''I''' + @crlf
		+ N' LEFT OUTER JOIN Backups bL ON bF.database_name = bL.database_name AND bF.database_guid = bL.database_guid AND bL.backup_type = ''L''' + @crlf
		+ N' WHERE bF.backup_type = ''D''; ' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;


	RAISERROR('Updating #Backups with worst RPO case', 0, 1) WITH NOWAIT;

	SET @StringToExecute =N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							 SELECT  bs.database_name, bs.database_guid, bs.backup_set_id, bsPrior.backup_set_id AS backup_set_id_prior,
							         bs.backup_finish_date, bsPrior.backup_finish_date AS backup_finish_date_prior,
							         DATEDIFF(ss, bsPrior.backup_finish_date, bs.backup_finish_date) AS backup_gap_seconds
							 INTO #backup_gaps
							 FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS bs
							 CROSS APPLY ( 
							 	SELECT TOP 1 bs1.backup_set_id, bs1.backup_finish_date
							 	FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS bs1
							 	WHERE bs.database_name = bs1.database_name
							 	        AND bs.database_guid = bs1.database_guid
							 	        AND bs.backup_finish_date > bs1.backup_finish_date
							 			AND bs.backup_set_id > bs1.backup_set_id
							 	ORDER BY bs1.backup_finish_date DESC, bs1.backup_set_id DESC 
							 ) bsPrior
							 WHERE bs.backup_finish_date > @StartTime
							 
							 CREATE CLUSTERED INDEX cx_backup_gaps ON #backup_gaps (database_name, database_guid, backup_set_id, backup_finish_date, backup_gap_seconds);
							 
							 WITH max_gaps AS (
							 SELECT g.database_name, g.database_guid, g.backup_set_id, g.backup_set_id_prior, g.backup_finish_date_prior, 
							        g.backup_finish_date, MAX(g.backup_gap_seconds) AS max_backup_gap_seconds 
							 FROM #backup_gaps AS g
							 GROUP BY g.database_name, g.database_guid, g.backup_set_id, g.backup_set_id_prior, g.backup_finish_date_prior,  g.backup_finish_date
												)
							UPDATE #Backups
								SET   RPOWorstCaseMinutes = bg.max_backup_gap_seconds / 60.0
							        , RPOWorstCaseBackupSetID = bg.backup_set_id
									, RPOWorstCaseBackupSetFinishTime = bg.backup_finish_date
									, RPOWorstCaseBackupSetIDPrior = bg.backup_set_id_prior
									, RPOWorstCaseBackupSetPriorFinishTime = bg.backup_finish_date_prior
								FROM #Backups b
								INNER HASH JOIN max_gaps bg ON b.database_name = bg.database_name AND b.database_guid = bg.database_guid
								LEFT OUTER HASH JOIN max_gaps bgBigger ON bg.database_name = bgBigger.database_name AND bg.database_guid = bgBigger.database_guid AND bg.max_backup_gap_seconds < bgBigger.max_backup_gap_seconds
								WHERE bgBigger.backup_set_id IS NULL;
								';
	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;

	RAISERROR('Updating #Backups with worst RPO case queries', 0, 1) WITH NOWAIT;

    UPDATE #Backups
      SET RPOWorstCaseMoreInfoQuery = @MoreInfoHeader + N'SELECT * ' + @crlf
            + N' FROM ' + QUOTENAME(@MSDBName) + '.dbo.backupset ' + @crlf
            + N' WHERE database_name = ''' + database_name + ''' ' + @crlf
            + N' AND database_guid = ''' + CAST(database_guid AS NVARCHAR(50)) + ''' ' + @crlf
            + N' AND backup_finish_date >= DATEADD(hh, -2, ''' + CAST(CONVERT(DATETIME, RPOWorstCaseBackupSetPriorFinishTime, 102) AS NVARCHAR(100)) + ''') ' + @crlf
            + N' AND backup_finish_date <= DATEADD(hh, 2, ''' + CAST(CONVERT(DATETIME, RPOWorstCaseBackupSetPriorFinishTime, 102) AS NVARCHAR(100)) + ''') ' + @crlf
            + N' ORDER BY backup_finish_date;'
            + @MoreInfoFooter;


/* RTO */

RAISERROR('Gathering RTO information', 0, 1) WITH NOWAIT;


	SET @StringToExecute =N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							 INSERT INTO #RTORecoveryPoints(database_name, database_guid, log_last_lsn)
							 SELECT database_name, database_guid, MAX(last_lsn) AS log_last_lsn
							 FROM ' + QUOTENAME(@MSDBName) + '.dbo.backupset bLastLog
							 WHERE type = ''L''
							 AND bLastLog.backup_finish_date >= @StartTime
							 GROUP BY database_name, database_guid;
							';

	IF @Debug = 1
		PRINT @StringToExecute;

		EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;

/* Find the most recent full backups for those logs */

RAISERROR('Updating #RTORecoveryPoints', 0, 1) WITH NOWAIT;

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							 UPDATE #RTORecoveryPoints
							 SET log_backup_set_id = bLasted.backup_set_id
							     ,full_backup_set_id = bLasted.backup_set_id
							     ,full_last_lsn = bLasted.last_lsn
							     ,full_backup_set_uuid = bLasted.backup_set_uuid
							 FROM #RTORecoveryPoints rp
							 		CROSS APPLY (
							 				SELECT TOP 1 bLog.backup_set_id AS backup_set_id_log, bLastFull.backup_set_id, bLastFull.last_lsn, bLastFull.backup_set_uuid, bLastFull.database_guid, bLastFull.database_name
							 				FROM  ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bLog 
							 				INNER JOIN ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bLastFull 
							 					ON bLog.database_guid = bLastFull.database_guid 
							 					AND bLog.database_name = bLastFull.database_name
							 					AND bLog.first_lsn > bLastFull.last_lsn
							 					AND bLastFull.type = ''D''
							 				WHERE rp.database_guid = bLog.database_guid 
							 					AND rp.database_name = bLog.database_name
							 			) bLasted
							 LEFT OUTER JOIN ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bLaterFulls ON bLasted.database_guid = bLaterFulls.database_guid AND bLasted.database_name = bLaterFulls.database_name
							     AND bLasted.last_lsn < bLaterFulls.last_lsn
							     AND bLaterFulls.first_lsn < bLasted.last_lsn
							     AND bLaterFulls.type = ''D''
							 WHERE bLaterFulls.backup_set_id IS NULL;
							 ';

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute;

/* Add any full backups in the StartDate range that weren't part of the above log backup chain */

RAISERROR('Add any full backups in the StartDate range that weren''t part of the above log backup chain', 0, 1) WITH NOWAIT;

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							 INSERT INTO #RTORecoveryPoints(database_name, database_guid, full_backup_set_id, full_last_lsn, full_backup_set_uuid)
							 SELECT bFull.database_name, bFull.database_guid, bFull.backup_set_id, bFull.last_lsn, bFull.backup_set_uuid
							 FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bFull
							 LEFT OUTER JOIN #RTORecoveryPoints rp ON bFull.backup_set_uuid = rp.full_backup_set_uuid
							 WHERE bFull.type = ''D''
							     AND bFull.backup_finish_date IS NOT NULL
							     AND rp.full_backup_set_uuid IS NULL
							     AND bFull.backup_finish_date >= @StartTime;
							';

	IF @Debug = 1
		PRINT @StringToExecute;

		EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;

/* Fill out the most recent log for that full, but before the next full */

RAISERROR('Fill out the most recent log for that full, but before the next full', 0, 1) WITH NOWAIT;

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							UPDATE rp
						    SET log_last_lsn = (SELECT MAX(last_lsn) FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bLog WHERE bLog.first_lsn >= rp.full_last_lsn AND bLog.first_lsn <= rpNextFull.full_last_lsn AND bLog.type = ''L'')
							FROM #RTORecoveryPoints rp
						    INNER JOIN #RTORecoveryPoints rpNextFull ON rp.database_guid = rpNextFull.database_guid AND rp.database_name = rpNextFull.database_name
						        AND rp.full_last_lsn < rpNextFull.full_last_lsn
						    LEFT OUTER JOIN #RTORecoveryPoints rpEarlierFull ON rp.database_guid = rpEarlierFull.database_guid AND rp.database_name = rpEarlierFull.database_name
						        AND rp.full_last_lsn < rpEarlierFull.full_last_lsn
						        AND rpNextFull.full_last_lsn > rpEarlierFull.full_last_lsn
						    WHERE rpEarlierFull.full_backup_set_id IS NULL;
							';

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute;

/* Fill out a diff in that range */

RAISERROR('Fill out a diff in that range', 0, 1) WITH NOWAIT;

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							UPDATE #RTORecoveryPoints
							SET diff_last_lsn = (SELECT TOP 1 bDiff.last_lsn FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bDiff
							                        WHERE rp.database_guid = bDiff.database_guid AND rp.database_name = bDiff.database_name
							                            AND bDiff.type = ''I''
							                            AND bDiff.last_lsn < rp.log_last_lsn
							                            AND rp.full_backup_set_uuid = bDiff.differential_base_guid
							                            ORDER BY bDiff.last_lsn DESC)
							FROM #RTORecoveryPoints rp
							WHERE diff_last_lsn IS NULL;
							';

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute;

/* Get time & size totals for full & diff */

RAISERROR('Get time & size totals for full & diff', 0, 1) WITH NOWAIT;

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							UPDATE #RTORecoveryPoints
							    SET full_time_seconds = DATEDIFF(ss,bFull.backup_start_date, bFull.backup_finish_date)
							    , full_file_size_mb = bFull.backup_size / 1048576.0
							    , diff_backup_set_id = bDiff.backup_set_id
							    , diff_time_seconds = DATEDIFF(ss,bDiff.backup_start_date, bDiff.backup_finish_date)
							    , diff_file_size_mb = bDiff.backup_size / 1048576.0
							FROM #RTORecoveryPoints rp
							INNER JOIN ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bFull ON rp.database_guid = bFull.database_guid AND rp.database_name = bFull.database_name AND rp.full_last_lsn = bFull.last_lsn
							LEFT OUTER JOIN ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bDiff ON rp.database_guid = bDiff.database_guid AND rp.database_name = bDiff.database_name AND rp.diff_last_lsn = bDiff.last_lsn AND bDiff.last_lsn IS NOT NULL;
							';

	IF @Debug = 1
		PRINT @StringToExecute;
	
	EXEC sys.sp_executesql @StringToExecute;


/* Get time & size totals for logs */

RAISERROR('Get time & size totals for logs', 0, 1) WITH NOWAIT;

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							WITH LogTotals AS (
								 SELECT rp.id, log_time_seconds = SUM(DATEDIFF(ss,bLog.backup_start_date, bLog.backup_finish_date))
								    , log_file_size = SUM(bLog.backup_size)
								    , SUM(1) AS log_backups
								        FROM #RTORecoveryPoints rp
								            INNER JOIN ' + QUOTENAME(@MSDBName) + N'.dbo.backupset bLog ON rp.database_guid = bLog.database_guid AND rp.database_name = bLog.database_name AND bLog.type = ''L''
								            AND bLog.first_lsn > COALESCE(rp.diff_last_lsn, rp.full_last_lsn)
								            AND bLog.first_lsn <= rp.log_last_lsn
								        GROUP BY rp.id
								)
								UPDATE #RTORecoveryPoints
								    SET log_time_seconds = lt.log_time_seconds
								    , log_file_size_mb = lt.log_file_size / 1048576.0
								    , log_backups = lt.log_backups
								FROM #RTORecoveryPoints rp
								    INNER JOIN LogTotals lt ON rp.id = lt.id;
									';

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute;

RAISERROR('Gathering RTO worst cases', 0, 1) WITH NOWAIT;

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							WITH WorstCases AS (
								SELECT rp.*
								  FROM #RTORecoveryPoints rp
								    LEFT OUTER JOIN #RTORecoveryPoints rpNewer 
										ON rp.database_guid = rpNewer.database_guid 
										AND rp.database_name = rpNewer.database_name 
										AND rp.full_last_lsn < rpNewer.full_last_lsn 
										AND rpNewer.rto_worst_case_size_mb = (SELECT TOP 1 rto_worst_case_size_mb FROM #RTORecoveryPoints s	WHERE rp.database_guid = s.database_guid AND rp.database_name = s.database_name ORDER BY rto_worst_case_size_mb DESC)
								  WHERE rp.rto_worst_case_size_mb = (SELECT TOP 1 rto_worst_case_size_mb FROM #RTORecoveryPoints s WHERE rp.database_guid = s.database_guid AND rp.database_name = s.database_name ORDER BY rto_worst_case_size_mb DESC)
								    /* OR  rp.rto_worst_case_time_seconds = (SELECT TOP 1 rto_worst_case_time_seconds FROM #RTORecoveryPoints s WHERE rp.database_guid = s.database_guid AND rp.database_name = s.database_name ORDER BY rto_worst_case_time_seconds DESC) */
								  AND rpNewer.database_guid IS NULL
								)
								UPDATE #Backups
										SET RTOWorstCaseMinutes = 
                                                                    /* Fulls */
                                                                    (CASE WHEN @RestoreSpeedFullMBps IS NULL 
																	   THEN wc.full_time_seconds / 60.0
																	   ELSE @RestoreSpeedFullMBps / wc.full_file_size_mb
																	   END)

                                                                    /* Diffs, which might not have been taken */
                                                                    + (CASE WHEN @RestoreSpeedDiffMBps IS NOT NULL AND wc.diff_file_size_mb IS NOT NULL
                                                                        THEN @RestoreSpeedDiffMBps / wc.diff_file_size_mb
                                                                        ELSE COALESCE(wc.diff_time_seconds,0) / 60.0
                                                                        END)

                                                                    /* Logs, which might not have been taken */
                                                                    + (CASE WHEN @RestoreSpeedLogMBps IS NOT NULL AND wc.log_file_size_mb IS NOT NULL
                                                                        THEN @RestoreSpeedLogMBps / wc.log_file_size_mb
                                                                        ELSE COALESCE(wc.log_time_seconds,0) / 60.0
                                                                        END)
								        , RTOWorstCaseBackupFileSizeMB = wc.rto_worst_case_size_mb
								FROM #Backups b
								INNER JOIN WorstCases wc 
								ON b.database_guid = wc.database_guid 
									AND b.database_name = wc.database_name;
								';

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute, N'@RestoreSpeedFullMBps INT, @RestoreSpeedDiffMBps INT, @RestoreSpeedLogMBps INT', @RestoreSpeedFullMBps, @RestoreSpeedDiffMBps, @RestoreSpeedLogMBps;



/*Populating Recoverability*/


	/*Get distinct list of databases*/
	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							 SELECT DISTINCT b.database_name, database_guid
							 FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b;'

	IF @Debug = 1
		PRINT @StringToExecute;

	INSERT #Recoverability ( DatabaseName, DatabaseGUID )
	EXEC sys.sp_executesql @StringToExecute;


	/*Find most recent recovery model, backup size, and backup date*/
	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += 	N'
								UPDATE r
								SET r.LastBackupRecoveryModel = ca.recovery_model,
									r.LastFullBackupSizeMB = ca.compressed_backup_size,
									r.LastFullBackupDate = ca.backup_finish_date
								FROM #Recoverability r
									CROSS APPLY (
										SELECT TOP 1 b.recovery_model, (b.compressed_backup_size / 1048576.0) AS compressed_backup_size, b.backup_finish_date
										FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
										WHERE r.DatabaseName = b.database_name
										AND r.DatabaseGUID = b.database_guid
										AND b.type = ''D''
										AND b.backup_finish_date > @StartTime
										ORDER BY b.backup_finish_date DESC
												) ca;'

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;

	/*Find first backup size and date*/
	SET @StringToExecute =N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							UPDATE r
							SET r.FirstFullBackupSizeMB = ca.compressed_backup_size,
								r.FirstFullBackupDate = ca.backup_finish_date
							FROM #Recoverability r
								CROSS APPLY (
									SELECT TOP 1 (b.compressed_backup_size / 1048576.0) AS compressed_backup_size, b.backup_finish_date
									FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
									WHERE r.DatabaseName = b.database_name 
									AND r.DatabaseGUID = b.database_guid
									AND b.type = ''D''
									AND b.backup_finish_date > @StartTime
									ORDER BY b.backup_finish_date ASC
											) ca;'

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;


	/*Find average backup throughputs for full, diff, and log*/
	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							UPDATE r
							SET r.AvgFullBackupThroughputMB = ca_full.AvgFullSpeed,
								r.AvgDiffBackupThroughputMB = ca_diff.AvgDiffSpeed,
								r.AvgLogBackupThroughputMB = ca_log.AvgLogSpeed,
								r.AvgFullBackupDurationSeconds = AvgFullDuration,
								r.AvgDiffBackupDurationSeconds = AvgDiffDuration,
								r.AvgLogBackupDurationSeconds = AvgLogDuration
							FROM #Recoverability AS r
							OUTER APPLY (
								SELECT b.database_name, 
									   AVG( b.compressed_backup_size / ( DATEDIFF(ss, b.backup_start_date, b.backup_finish_date) ) / 1048576.0 ) AS AvgFullSpeed,
									   AVG( DATEDIFF(ss, b.backup_start_date, b.backup_finish_date) ) AS AvgFullDuration
								FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset b
								WHERE r.DatabaseName = b.database_name
								AND r.DatabaseGUID = b.database_guid
								AND b.type = ''D'' 
								AND DATEDIFF(SECOND, b.backup_start_date, b.backup_finish_date) > 0
								AND b.backup_finish_date > @StartTime
								GROUP BY b.database_name
										) ca_full
							OUTER APPLY (
								SELECT b.database_name, 
									   AVG( b.compressed_backup_size / ( DATEDIFF(ss, b.backup_start_date, b.backup_finish_date) ) / 1048576.0 ) AS AvgDiffSpeed,
									   AVG( DATEDIFF(ss, b.backup_start_date, b.backup_finish_date) ) AS AvgDiffDuration
								FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset b
								WHERE r.DatabaseName = b.database_name
								AND r.DatabaseGUID = b.database_guid
								AND b.type = ''I'' 
								AND DATEDIFF(SECOND, b.backup_start_date, b.backup_finish_date) > 0
								AND b.backup_finish_date > @StartTime
								GROUP BY b.database_name
										) ca_diff
							OUTER APPLY (
								SELECT b.database_name, 
									   AVG( b.compressed_backup_size / ( DATEDIFF(ss, b.backup_start_date, b.backup_finish_date) ) / 1048576.0 ) AS AvgLogSpeed,
									   AVG( DATEDIFF(ss, b.backup_start_date, b.backup_finish_date) ) AS AvgLogDuration
								FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset b
								WHERE r.DatabaseName = b.database_name
								AND r.DatabaseGUID = b.database_guid
								AND b.type = ''L''
								AND DATEDIFF(SECOND, b.backup_start_date, b.backup_finish_date) > 0
								AND b.backup_finish_date > @StartTime
								GROUP BY b.database_name
										) ca_log;'

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;


	/*Find max and avg diff and log sizes*/
	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							UPDATE r
							 SET r.AvgFullSizeMB = fulls.avg_full_size,
							 	 r.AvgDiffSizeMB = diffs.avg_diff_size,
							 	 r.AvgLogSizeMB = logs.avg_log_size
							 FROM #Recoverability AS r
							 OUTER APPLY (
							 	SELECT b.database_name, AVG(b.compressed_backup_size / 1048576.0) AS avg_full_size
							 	FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							 	WHERE r.DatabaseName = b.database_name
								AND r.DatabaseGUID = b.database_guid
							 	AND b.type = ''D''
								AND b.backup_finish_date > @StartTime
							 	GROUP BY b.database_name
							 			) AS fulls
							 OUTER APPLY (
							 	SELECT b.database_name, AVG(b.compressed_backup_size / 1048576.0) AS avg_diff_size
							 	FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							 	WHERE r.DatabaseName = b.database_name
								AND r.DatabaseGUID = b.database_guid
							 	AND b.type = ''I''
								AND b.backup_finish_date > @StartTime
							 	GROUP BY b.database_name
							 			) AS diffs
							 OUTER APPLY (
							 	SELECT b.database_name, AVG(b.compressed_backup_size / 1048576.0) AS avg_log_size
							 	FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							 	WHERE r.DatabaseName = b.database_name
								AND r.DatabaseGUID = b.database_guid
							 	AND b.type = ''L''
								AND b.backup_finish_date > @StartTime
							 	GROUP BY b.database_name
							 			) AS logs;'

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;
	
/*Trending - only works if backupfile is populated, which means in msdb */
IF @MSDBName = N'msdb'
BEGIN
	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' --+ @crlf;

	SET @StringToExecute += N'
							SELECT  p.DatabaseName,
									p.DatabaseGUID,
									p.[0],
									p.[-1],
									p.[-2],
									p.[-3],
									p.[-4],
									p.[-5],
									p.[-6],
									p.[-7],
									p.[-8],
									p.[-9],
									p.[-10],
									p.[-11],
									p.[-12]
								FROM ( SELECT b.database_name AS DatabaseName,
											  b.database_guid AS DatabaseGUID,
											  DATEDIFF(MONTH, @StartTime, b.backup_start_date) AS MonthsAgo ,
											  CONVERT(DECIMAL(18, 2), AVG(bf.file_size / 1048576.0)) AS AvgSizeMB
										FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b 
										INNER JOIN ' + QUOTENAME(@MSDBName) + N'.dbo.backupfile AS bf
										ON b.backup_set_id = bf.backup_set_id
										WHERE b.database_name NOT IN ( ''master'', ''msdb'', ''model'', ''tempdb'' )
										AND bf.file_type = ''D''
										AND b.backup_start_date >= DATEADD(YEAR, -1, @StartTime)
										AND b.backup_start_date <= SYSDATETIME()
										GROUP BY b.database_name,
												 b.database_guid,	
												 DATEDIFF(mm, @StartTime, b.backup_start_date)
									 ) AS bckstat PIVOT ( SUM(bckstat.AvgSizeMB) FOR bckstat.MonthsAgo IN ( [0], [-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11], [-12] ) ) AS p
								ORDER BY p.DatabaseName;
								'

		IF @Debug = 1
			PRINT @StringToExecute;

		INSERT #Trending ( DatabaseName, DatabaseGUID, [0], [-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11], [-12] )
		EXEC sys.sp_executesql @StringToExecute, N'@StartTime DATETIME2', @StartTime;

END

/*End Trending*/

/*End populating Recoverability*/

RAISERROR('Returning data', 0, 1) WITH NOWAIT;

	SELECT   b.*
		FROM     #Backups AS b
		ORDER BY b.database_name;

	SELECT   r.*,
             t.[0], t.[-1], t.[-2], t.[-3], t.[-4], t.[-5], t.[-6], t.[-7], t.[-8], t.[-9], t.[-10], t.[-11], t.[-12]
		FROM #Recoverability AS r
		LEFT JOIN #Trending t
		ON r.DatabaseName = t.DatabaseName
		AND r.DatabaseGUID = t.DatabaseGUID	
		WHERE r.LastBackupRecoveryModel IS NOT NULL
		ORDER BY r.DatabaseName


RAISERROR('Rules analysis starting', 0, 1) WITH NOWAIT;

/*Looking for out of band backups by finding most common backup operator user_name and noting backups taken by other user_names*/

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'
							WITH common_people AS (
									SELECT TOP 1 b.user_name, COUNT_BIG(*) AS Records
									FROM ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
									GROUP BY b.user_name
									ORDER BY Records DESC
													)								
								SELECT 
								1 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''Non-Agent backups taken'' AS [Finding], 
								''The database '' + QUOTENAME(b.database_name) + '' has been backed up by '' + QUOTENAME(b.user_name) + '' '' + CONVERT(VARCHAR(10), COUNT(*)) + '' times.'' AS [Warning]
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							WHERE  b.user_name NOT LIKE ''%Agent%'' AND b.user_name NOT LIKE ''%AGENT%'' 
							AND NOT EXISTS (
											SELECT 1
											FROM common_people AS cp
											WHERE cp.user_name = b.user_name
											)
							GROUP BY b.database_name, b.user_name
							HAVING COUNT(*) > 1;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings (CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*Looking for compatibility level changing. Only looking for databases that have changed more than twice (It''s possible someone may have changed up, had CE problems, and then changed back)*/

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
								2 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''Compatibility level changing'' AS [Finding],
								''The database '' + QUOTENAME(b.database_name) + '' has changed compatibility levels '' + CONVERT(VARCHAR(10), COUNT(DISTINCT b.compatibility_level)) + '' times.'' AS [Warning]
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							GROUP BY b.database_name
							HAVING COUNT(DISTINCT b.compatibility_level) > 2;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*Looking for password protected backups. This hasn''t been a popular option ever, and was largely replaced by encrypted backups, but it''s simple to check for.*/

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
								3 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''Password backups'' AS [Finding],
								''The database '' + QUOTENAME(b.database_name) + '' has been backed up with a password '' + CONVERT(VARCHAR(10), COUNT(*)) + '' times. Who has the password?'' AS [Warning]
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							WHERE b.is_password_protected = 1
							GROUP BY b.database_name;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*Looking for snapshot backups. There are legit reasons for these, but we should flag them so the questions get asked. What questions? Good question.*/

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
								4 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''Snapshot backups'' AS [Finding],
								''The database '' + QUOTENAME(b.database_name) + '' has had '' + CONVERT(VARCHAR(10), COUNT(*)) + '' snapshot backups. This message is purely informational.'' AS [Warning]
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							WHERE b.is_snapshot = 1
							GROUP BY b.database_name;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*It''s fine to take backups of read only databases, but it''s not always necessary (there''s no new data, after all).*/

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
								5 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''Read only state backups'' AS [Finding],
								''The database '' + QUOTENAME(b.database_name) + '' has been backed up '' + CONVERT(VARCHAR(10), COUNT(*)) + '' times while in a read-only state. This can be normal if it''''s a secondary, but a bit odd otherwise.'' AS [Warning]
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							WHERE b.is_readonly = 1
							GROUP BY b.database_name;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*So, I''ve come across people who think they need to change their database to single user mode to take a backup. Or that doing that will help something. I just need to know, here.*/

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
								6 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''Single user mode backups'' AS [Finding],
								''The database '' + QUOTENAME(b.database_name) + '' has been backed up '' + CONVERT(VARCHAR(10), COUNT(*)) + '' times while in single-user mode. This is really weird! Make sure your backup process doesn''''t include a mode change anywhere.'' AS [Warning]
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							WHERE b.is_single_user = 1
							GROUP BY b.database_name;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*C''mon, it''s 2017. Take your backups with CHECKSUMS, people.*/

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
								7 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''No CHECKSUMS'' AS [Finding],
								''The database '' + QUOTENAME(b.database_name) + '' has been backed up '' + CONVERT(VARCHAR(10), COUNT(*)) + '' times without CHECKSUMS in the past 30 days. CHECKSUMS can help alert you to corruption errors.'' AS [Warning]
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							WHERE b.has_backup_checksums = 0
							AND b.backup_finish_date >= DATEADD(DAY, -30, SYSDATETIME())
							GROUP BY b.database_name;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*Damaged is a Black Flag album. You don''t want your backups to be like a Black Flag album. */

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
								8 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''Damaged backups'' AS [Finding],
								''The database '' + QUOTENAME(b.database_name) + '' has had '' + CONVERT(VARCHAR(10), COUNT(*)) + '' damaged backups taken without stopping to throw an error. This is done by specifying CONTINUE_AFTER_ERROR in your BACKUP commands.'' AS [Warning]
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							WHERE b.is_damaged = 1
							GROUP BY b.database_name;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*Checking for encrypted backups and the last backup of the encryption key.*/

	/*2014 ONLY*/

IF @ProductVersionMajor >= 12
	BEGIN

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
								9 AS CheckId,
								100 AS [Priority],
								b.database_name AS [Database Name],
								''Encrypted backups'' AS [Finding],
								''The database '' + QUOTENAME(b.database_name) + '' has had '' + CONVERT(VARCHAR(10), COUNT(*)) + '' '' + b.encryptor_type + '' backups, and the last time a certificate was backed up is '
								+ CASE WHEN LOWER(@MSDBName) <> N'msdb'
									THEN + N'...well, that information is on another server, anyway.'' AS [Warning]'
									ELSE + CONVERT(VARCHAR(30), (SELECT MAX(c.pvt_key_last_backup_date) FROM sys.certificates AS c WHERE c.name NOT LIKE '##%')) + N'.'' AS [Warning]'
									END + 
							N'
							FROM   ' + QUOTENAME(@MSDBName) + N'.dbo.backupset AS b
							WHERE b.encryptor_type IS NOT NULL
							GROUP BY b.database_name, b.encryptor_type;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	END
	
	/*Looking for backups that have BULK LOGGED data in them -- this can screw up point in time LOG recovery.*/

	SET @StringToExecute =N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
		10 AS CheckId,
		100 AS [Priority],
		b.database_name AS [Database Name],
		''Bulk logged backups'' AS [Finding],
		''The database '' + QUOTENAME(b.database_name) + '' has had '' + CONVERT(VARCHAR(10), COUNT(*)) + '' backups with bulk logged data. This can make point in time recovery awkward. '' AS [Warning]
	FROM   ' + QUOTENAME(@MSDBName) + '.dbo.backupset AS b
	WHERE b.has_bulk_logged_data = 1
	GROUP BY b.database_name;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;
	
	/*Looking for recovery model being switched between FULL and SIMPLE, because it''s a bad practice.*/

	SET @StringToExecute =N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
		11 AS CheckId,
		100 AS [Priority],
		b.database_name AS [Database Name],
		''Recovery model switched'' AS [Finding],
		''The database '' + QUOTENAME(b.database_name) + '' has changed recovery models from between FULL and SIMPLE '' + CONVERT(VARCHAR(10), COUNT(DISTINCT b.recovery_model)) + '' times. This breaks the log chain and is generally a bad idea.'' AS [Warning]
	FROM   ' + QUOTENAME(@MSDBName) + '.dbo.backupset AS b
	WHERE b.recovery_model <> ''BULK-LOGGED''
	GROUP BY b.database_name
	HAVING COUNT(DISTINCT b.recovery_model) > 4;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;

	/*Looking for uncompressed backups.*/

	SET @StringToExecute =N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT 
		12 AS CheckId,
		100 AS [Priority],
		b.database_name AS [Database Name],
		''Uncompressed backups'' AS [Finding],
		''The database '' + QUOTENAME(b.database_name) + '' has had '' + CONVERT(VARCHAR(10), COUNT(*)) + '' uncompressed backups in the last 30 days. This is a free way to save time and space. And SPACETIME. If your version of SQL supports it.'' AS [Warning]
	FROM   ' + QUOTENAME(@MSDBName) + '.dbo.backupset AS b
	WHERE backup_size = compressed_backup_size AND type = ''D''
	AND b.backup_finish_date >= DATEADD(DAY, -30, SYSDATETIME())
	GROUP BY b.database_name;' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		EXEC sys.sp_executesql @StringToExecute;

RAISERROR('Rules analysis starting on temp tables', 0, 1) WITH NOWAIT;

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		SELECT
			13 AS CheckId,
			100 AS Priority,
			r.DatabaseName as [DatabaseName],
			'Big Diffs' AS [Finding],
			'On average, Differential backups for this database are >=40% of the size of the average Full backup.' AS [Warning]
			FROM #Recoverability AS r
			WHERE r.IsBigDiff = 1

		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )
		SELECT
			13 AS CheckId,
			100 AS Priority,
			r.DatabaseName as [DatabaseName],
			'Big Logs' AS [Finding],
			'On average, Log backups for this database are >=20% of the size of the average Full backup.' AS [Warning]
			FROM #Recoverability AS r
			WHERE r.IsBigLog = 1



/*Insert thank you stuff last*/
		INSERT #Warnings ( CheckId, Priority, DatabaseName, Finding, Warning )

		SELECT
		2147483647 AS [CheckId],
		2147483647 AS [Priority],
		'From Your Community Volunteers' AS [DatabaseName],
		'sp_BlitzBackups Version: ' + @Version + ', Version Date: ' + CONVERT(VARCHAR(30), @VersionDate) + '.' AS [Finding],
		'Thanks for using our stored procedure. We hope you find it useful! Check out our other free SQL Server scripts at firstresponderkit.org!' AS [Warning];

RAISERROR('Rules analysis finished', 0, 1) WITH NOWAIT;

SELECT w.CheckId, w.Priority, w.DatabaseName, w.Finding, w.Warning
FROM #Warnings AS w
ORDER BY w.Priority, w.CheckId;

DROP TABLE #Backups, #Warnings, #Recoverability, #RTORecoveryPoints


RETURN;

PushBackupHistoryToListener:

RAISERROR('Pushing backup history to listener', 0, 1) WITH NOWAIT;

DECLARE @msg NVARCHAR(4000) = N'';
DECLARE @RemoteCheck TABLE (c INT NULL);


IF @WriteBackupsToDatabaseName IS NULL
	BEGIN
	RAISERROR('@WriteBackupsToDatabaseName can''t be NULL.', 16, 1) WITH NOWAIT
	RETURN;
	END

IF LOWER(@WriteBackupsToDatabaseName) = N'msdb'
	BEGIN
	RAISERROR('We can''t write to the real msdb, we have to write to a fake msdb.', 16, 1) WITH NOWAIT
	RETURN;
	END

IF @WriteBackupsToListenerName IS NULL
BEGIN
	IF @AGName IS NULL
		BEGIN
			RAISERROR('@WriteBackupsToListenerName and @AGName can''t both be NULL.', 16, 1) WITH NOWAIT;
			RETURN;
		END
	ELSE
		BEGIN	
			SELECT @WriteBackupsToListenerName =  dns_name
			FROM sys.availability_groups AS ag
			JOIN sys.availability_group_listeners AS agl
			ON ag.group_id = agl.group_id
			WHERE name = @AGName;
		END

END

IF @WriteBackupsToListenerName IS NOT NULL
BEGIN
	IF NOT EXISTS 
		(
			SELECT *
			FROM sys.servers s
			WHERE name = @WriteBackupsToListenerName
		)
			BEGIN
				SET @msg = N'We need a linked server to write data across. Please set one up for ' + @WriteBackupsToListenerName + N'.';
				RAISERROR(@msg, 16, 1) WITH NOWAIT;
				RETURN;
			END
END

	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT TOP 1 1 FROM ' 
							+ QUOTENAME(@WriteBackupsToListenerName) + N'.master.sys.databases d WHERE d.name = @i_WriteBackupsToDatabaseName;'

	IF @Debug = 1
		PRINT @StringToExecute;

	INSERT @RemoteCheck (c)
	EXEC sp_executesql @StringToExecute, N'@i_WriteBackupsToDatabaseName NVARCHAR(256)', @i_WriteBackupsToDatabaseName = @WriteBackupsToDatabaseName;

	IF @@ROWCOUNT = 0
		BEGIN
		SET @msg = N'The database ' + @WriteBackupsToDatabaseName + N' doesn''t appear to exist on that server.'
		RAISERROR(@msg, 16, 1) WITH NOWAIT
		RETURN;
		END


	SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

	SET @StringToExecute += N'SELECT TOP 1 1 FROM ' 
							+ QUOTENAME(@WriteBackupsToListenerName) + '.' + QUOTENAME(@WriteBackupsToDatabaseName) + '.sys.tables WHERE name = ''backupset'' AND SCHEMA_NAME(schema_id) = ''dbo'';
							' + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

	INSERT @RemoteCheck (c)
	EXEC sp_executesql @StringToExecute;

	IF @@ROWCOUNT = 0
		BEGIN

		SET @msg = N'The database ' + @WriteBackupsToDatabaseName + N' doesn''t appear to have a table called dbo.backupset in it.'
		RAISERROR(@msg, 0, 1) WITH NOWAIT
		RAISERROR('Don''t worry, we''ll create it for you!', 0, 1) WITH NOWAIT
		
		SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

		SET @StringToExecute += N'CREATE TABLE ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.dbo.backupset
											( backup_set_id INT IDENTITY(1, 1), backup_set_uuid UNIQUEIDENTIFIER, media_set_id INT, first_family_number TINYINT, first_media_number SMALLINT, 
											  last_family_number TINYINT, last_media_number SMALLINT, catalog_family_number TINYINT, catalog_media_number SMALLINT, position INT, expiration_date DATETIME, 
											  software_vendor_id INT, name NVARCHAR(128), description NVARCHAR(255), user_name NVARCHAR(128), software_major_version TINYINT, software_minor_version TINYINT, 
											  software_build_version SMALLINT, time_zone SMALLINT, mtf_minor_version TINYINT, first_lsn NUMERIC(25, 0), last_lsn NUMERIC(25, 0), checkpoint_lsn NUMERIC(25, 0), 
											  database_backup_lsn NUMERIC(25, 0), database_creation_date DATETIME, backup_start_date DATETIME, backup_finish_date DATETIME, type CHAR(1), sort_order SMALLINT, 
											  code_page SMALLINT, compatibility_level TINYINT, database_version INT, backup_size NUMERIC(20, 0), database_name NVARCHAR(128), server_name NVARCHAR(128), 
											  machine_name NVARCHAR(128), flags INT, unicode_locale INT, unicode_compare_style INT, collation_name NVARCHAR(128), is_password_protected BIT, recovery_model NVARCHAR(60), 
											  has_bulk_logged_data BIT, is_snapshot BIT, is_readonly BIT, is_single_user BIT, has_backup_checksums BIT, is_damaged BIT, begins_log_chain BIT, has_incomplete_metadata BIT, 
											  is_force_offline BIT, is_copy_only BIT, first_recovery_fork_guid UNIQUEIDENTIFIER, last_recovery_fork_guid UNIQUEIDENTIFIER, fork_point_lsn NUMERIC(25, 0), database_guid UNIQUEIDENTIFIER, 
											  family_guid UNIQUEIDENTIFIER, differential_base_lsn NUMERIC(25, 0), differential_base_guid UNIQUEIDENTIFIER, compressed_backup_size NUMERIC(20, 0), key_algorithm NVARCHAR(32), 
											  encryptor_thumbprint VARBINARY(20) , encryptor_type NVARCHAR(32) 
											);
								 ' + @crlf;
		
		SET @InnerStringToExecute = N'EXEC( ''' + @StringToExecute +  ''' ) AT ' + QUOTENAME(@WriteBackupsToListenerName) + N';'
	
	IF @Debug = 1
		PRINT @InnerStringToExecute;		
		
		EXEC sp_executesql @InnerStringToExecute


		RAISERROR('We''ll even make the indexes!', 0, 1) WITH NOWAIT

		/*Checking for and creating the PK/CX*/

		SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;
		
		SET @StringToExecute += N'
		
		IF NOT EXISTS (
		SELECT t.name, i.name
		FROM ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.tables AS t
		JOIN ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.indexes AS i  
		ON t.object_id = i.object_id
		WHERE t.name = ?
		AND i.name LIKE ?
		)

		BEGIN
		ALTER TABLE ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.[dbo].[backupset] ADD PRIMARY KEY CLUSTERED ([backup_set_id] ASC)
		END
		'

		SET @InnerStringToExecute = N'EXEC( ''' + @StringToExecute +  ''', ''backupset'', ''PK[_][_]%'' ) AT ' + QUOTENAME(@WriteBackupsToListenerName) + N';'
	
	IF @Debug = 1
		PRINT @InnerStringToExecute;		
		
		EXEC sp_executesql @InnerStringToExecute



		/*Checking for and creating index on backup_set_uuid*/

		SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;
		
		SET @StringToExecute += N'IF NOT EXISTS (
		SELECT t.name, i.name
		FROM ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.tables AS t
		JOIN ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.indexes AS i  
		ON t.object_id = i.object_id
		WHERE t.name = ?
		AND i.name = ?
		)

		BEGIN
		CREATE NONCLUSTERED INDEX [backupsetuuid] ON ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.[dbo].[backupset] ([backup_set_uuid] ASC)
		END
		'

		SET @InnerStringToExecute = N'EXEC( ''' + @StringToExecute +  ''', ''backupset'', ''backupsetuuid'' ) AT ' + QUOTENAME(@WriteBackupsToListenerName) + N';'
	
	IF @Debug = 1
		PRINT @InnerStringToExecute;		
		
		EXEC sp_executesql @InnerStringToExecute		
		
		
		
		/*Checking for and creating index on media_set_id*/

		SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;
		
		SET @StringToExecute += 'IF NOT EXISTS (
		SELECT t.name, i.name
		FROM ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.tables AS t
		JOIN ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.indexes AS i  
		ON t.object_id = i.object_id
		WHERE t.name = ?
		AND i.name = ?
		)

		BEGIN
		CREATE NONCLUSTERED INDEX [backupsetMediaSetId] ON ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.[dbo].[backupset] ([media_set_id] ASC)
		END
		'

		SET @InnerStringToExecute = N'EXEC( ''' + @StringToExecute +  ''', ''backupset'', ''backupsetMediaSetId'' ) AT ' + QUOTENAME(@WriteBackupsToListenerName) + N';'
	
	IF @Debug = 1
		PRINT @InnerStringToExecute;		
		
		EXEC sp_executesql @InnerStringToExecute
		
				
				
		/*Checking for and creating index on backup_finish_date*/

		SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;
		
		SET @StringToExecute += N'IF NOT EXISTS (
		SELECT t.name, i.name
		FROM ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.tables AS t
		JOIN ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.indexes AS i  
		ON t.object_id = i.object_id
		WHERE t.name = ?
		AND i.name = ?
		)

		BEGIN
		CREATE NONCLUSTERED INDEX [backupsetDate] ON ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.[dbo].[backupset] ([backup_finish_date] ASC)
		END
		'

		SET @InnerStringToExecute = N'EXEC( ''' + @StringToExecute +  ''', ''backupset'', ''backupsetDate'' ) AT ' + QUOTENAME(@WriteBackupsToListenerName) + N';'
	
	IF @Debug = 1
		PRINT @InnerStringToExecute;		
		
		EXEC sp_executesql @InnerStringToExecute


			
		/*Checking for and creating index on database_name*/

		SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;
		
		SET @StringToExecute += N'IF NOT EXISTS (
		SELECT t.name, i.name
		FROM ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.tables AS t
		JOIN ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.sys.indexes AS i  
		ON t.object_id = i.object_id
		WHERE t.name = ?
		AND i.name = ?
		)

		BEGIN				
		CREATE NONCLUSTERED INDEX [backupsetDatabaseName] ON ' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.[dbo].[backupset] ([database_name] ASC ) INCLUDE ([backup_set_id], [media_set_id]) 
		END

		'

		SET @InnerStringToExecute = N'EXEC( ''' + @StringToExecute +  ''', ''backupset'', ''backupsetDatabaseName'' ) AT ' + QUOTENAME(@WriteBackupsToListenerName) + N';'
	
	IF @Debug = 1
		PRINT @InnerStringToExecute;		
		
		EXEC sp_executesql @InnerStringToExecute

		RAISERROR('Table and indexes created! You''re welcome!', 0, 1) WITH NOWAIT
		END


		RAISERROR('Beginning inserts', 0, 1) WITH NOWAIT;
		RAISERROR(@crlf, 0, 1) WITH NOWAIT;

		/*
		Batching code comes from the lovely and talented Michael J. Swart
		http://michaeljswart.com/2014/09/take-care-when-scripting-batches/
		If you're ever in Canada, he says you can stay at his house, too.
		*/


		SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;' + @crlf;

		SET @StringToExecute += N'	
									DECLARE
										@StartDate DATETIME = DATEADD(HOUR, @i_WriteBackupsLastHours, SYSDATETIME()),
										@StartDateNext DATETIME,
										@RC INT = 1,
										@msg NVARCHAR(4000) = N'''';
									
									SELECT @StartDate = MIN(b.backup_start_date)
									FROM msdb.dbo.backupset b
									WHERE b.backup_start_date >= @StartDate
									AND  NOT EXISTS (
													SELECT 1 
													FROM ' + QUOTENAME(@WriteBackupsToListenerName) + N'.' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.dbo.backupset b2
													WHERE b.backup_set_uuid = b2.backup_set_uuid
													AND b2.backup_start_date >= @StartDate
													)

									SET @StartDateNext = DATEADD(MINUTE, 10, @StartDate);

								 IF
									( @StartDate IS NULL )
										BEGIN
											SET @msg = N''No data to move, exiting.''
											RAISERROR(@msg, 0, 1) WITH NOWAIT	

											RETURN;
										END

									RAISERROR(''Starting insert loop'', 0, 1) WITH NOWAIT;

									WHILE EXISTS (
												SELECT 1
												FROM msdb.dbo.backupset b
												WHERE NOT EXISTS (
													SELECT 1 
													FROM ' + QUOTENAME(@WriteBackupsToListenerName) + N'.' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.dbo.backupset b2
													WHERE b.backup_set_uuid = b2.backup_set_uuid
													AND b2.backup_start_date >= @StartDate
																)
												)
									BEGIN
										
									SET @msg = N''Inserting data for '' + CONVERT(NVARCHAR(30), @StartDate) + '' through '' +  + CONVERT(NVARCHAR(30), @StartDateNext) + ''.''
									RAISERROR(@msg, 0, 1) WITH NOWAIT																			
										
										'

		SET @StringToExecute += N'INSERT ' + QUOTENAME(@WriteBackupsToListenerName) + N'.' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.dbo.backupset
									' 
		SET @StringToExecute += N' (database_name, database_guid, backup_set_uuid, type, backup_size, backup_start_date, backup_finish_date, media_set_id, time_zone, 
									compressed_backup_size, recovery_model, server_name, machine_name, first_lsn, last_lsn, user_name, compatibility_level, 
									is_password_protected, is_snapshot, is_readonly, is_single_user, has_backup_checksums, is_damaged, ' + CASE WHEN @ProductVersionMajor >= 12 
																																				THEN + N'encryptor_type, has_bulk_logged_data)' + @crlf
																																				ELSE + N'has_bulk_logged_data)' + @crlf
																																				END
		
		SET @StringToExecute +=N'
									SELECT database_name, database_guid, backup_set_uuid, type, backup_size, backup_start_date, backup_finish_date, media_set_id, time_zone, 
									compressed_backup_size, recovery_model, server_name, machine_name, first_lsn, last_lsn, user_name, compatibility_level, 
									is_password_protected, is_snapshot, is_readonly, is_single_user, has_backup_checksums, is_damaged, ' + CASE WHEN @ProductVersionMajor >= 12 
																																				THEN + N'encryptor_type, has_bulk_logged_data' + @crlf
																																				ELSE + N'has_bulk_logged_data' + @crlf
																																				END
		SET @StringToExecute +=N'
								 FROM msdb.dbo.backupset b
								 WHERE 1=1
								 AND b.backup_start_date >= @StartDate
								 AND b.backup_start_date < @StartDateNext
								 AND NOT EXISTS (
										SELECT 1 
										FROM ' + QUOTENAME(@WriteBackupsToListenerName) + N'.' + QUOTENAME(@WriteBackupsToDatabaseName) + N'.dbo.backupset b2
										WHERE b.backup_set_uuid = b2.backup_set_uuid
										AND b2.backup_start_date >= @StartDate
													)'  + @crlf;


		SET @StringToExecute +=N'
								 SET @RC = @@ROWCOUNT;
								 								
								SET @msg = N''Inserted '' + CONVERT(NVARCHAR(30), @RC) + '' rows for ''+ CONVERT(NVARCHAR(30), @StartDate) + '' through '' + CONVERT(NVARCHAR(30), @StartDateNext) + ''.''
								RAISERROR(@msg, 0, 1) WITH NOWAIT
								 
								 SET @StartDate = @StartDateNext;
								 SET @StartDateNext = DATEADD(MINUTE, 10, @StartDate);

								 IF
									( @StartDate > SYSDATETIME() )
										BEGIN
											
											SET @msg = N''No more data to move, exiting.''
											RAISERROR(@msg, 0, 1) WITH NOWAIT	
											
											BREAK;

										END
								 END'  + @crlf;

	IF @Debug = 1
		PRINT @StringToExecute;

	EXEC sp_executesql @StringToExecute, N'@i_WriteBackupsLastHours INT', @i_WriteBackupsLastHours = @WriteBackupsLastHours;

END;

END;

GO