Check Dbcc Database Backup Verification for Data Recovery

Twice a year, auditors verify that — should there be an “incident,” we can recover the database with little to no loss. As a DBA I want to make sure that there are no errors in the database. Most DBAs are familiar with and use some form of maintenance scripts from http://ola.hallengren.com/. I do too.

This week, the auditing team notified me that we would be doing an exercise in December to verify the database could be recovered. After reviewing their AUDIT SHEET, I was pleasantly surprised that what they are looking for, is already set up on all my databases, it runs weekly, and it’s automated. Naturally, I went through my solution with a fine comb just to make sure everything was in order. While doing so, I decided to screenshot things and share them here, for future reference.

OVERVIEW

My PRODUCTION and DEVELOPMENT both run on AWS RDS SQL Server 2019. This solution can be easily adapted to run on an EC2 or on-prem server location as well.

My servers are in two different environments and in separate AWS accounts. That means that my solution must have the ability to move a copy of the PRODUCTION databases back to DEVELOPMENT where they can be restored, tested, and verified. Since my company’s DEV TEAM likes to have a fresh production copy each week, instead of deleting the restored PROD databases, I can clean the PI data after my check. (See my post on how to do that here: SQL Table PI Data Cleaning Solution (Part I) – Love The SQL .)

If my DEV and PROD environments were within the same AWS Account, I would not need to set up the shared S3 buckets. But since they are, I will first review my post on how to set that up, before proceeding with configuring the PowerShell Script shown below. I can review that here: https://lovethesql.com/sql-performance-monitor-part-4-aws-bucket-iam-user-set-up/ .

COPY DATABASE FILES

Because my PRODUCTION and DEVELOPMENT servers are in two different environments, I will use a scheduled PowerShell job to copy the databases from an S3 bucket in my PRODUCTION AWS ACCOUNT to an S3 bucket in my DEVELOPMENT AWS ACCOUNT.

#DbccCopyProdS3toImplS3.ps1
# By David Speight
# Created 20230330
# DBCC Database copy over from PROD to DEV
# Use KMS Key form DEV (Destination, so things are re-encrypted with that accounts key.

$DigiDate = (Get-Date -Format "yyyyMMdd").ToString()
# This is the bucket/S3 location of my last FULL native daily backup.
$s3SourcePath = 's3://MyBucket-prod-database/backup/prodsqlrds/daily/{0}/' -f $DigiDate
# This is the bucket/S3 location of where I want to copy the backup to.
$sDestinationPath = 's3://MyBucket-dev-database/dbccbackup/prodsqlrds/'

# Use this line of code if DEV and PROD are in the same AWS Account
# Alternatively, this script is not needed for a single AWS Account, you can just update the SPROCS to restore from the Source path.
aws s3 cp $s3SourcePath  $sDestinationPath --recursive

# Use this line of code if DEV and PROD are in different AWS Accounts
aws s3 cp $s3SourcePath  $sDestinationPath --recursive --acl bucket-owner-full-control --profile MyProdProfile --sse aws:kms --sse-kms-key-id "arn:aws:kms:us-east-1:AwsDevAcct99999999:key/mrk-9999999999999999999999"

And I will use task scheduler to schedule this. The task will run under a Service Account that uses the AWS CLI with Credentials configured for the IAM Account I created in the previously mentioned blog post.

TABLES

To start the project, I will create these four (4) tables on the DEVELOPMENT server that will keep track of my databases, log errors, and log the process as it progresses. I am using a database I’ve called MAINTENANCE.

-- Note: Because I use tables in multiple projects, there will be some columns that are not used in this project, those are noted with a comment.
CREATE TABLE [dbo].[Databases](
	[DatabaseID] [int] IDENTITY(1,1) NOT NULL,
	[ServerDBID] [int] NULL,
	[GlobalDBID] [int] NULL,	 -- Not used in this project
	[DatabaseName] [varchar](200) NULL,
	[IsCounter] [bit] NOT NULL,	-- Not used in this project
	[CountTypeID] [int] NULL,	-- Not used in this project
	[CountTable] [varchar](50) NULL,-- Not used in this project
	[CountColumn] [varchar](50) NULL,-- Not used in this project
	[OrderBy] [int] NULL,
	[RestoreName] [varchar](200) NULL,
	[TrnLogPath] [varchar](100) NULL,-- Not used in this project
 CONSTRAINT [PK_Databases] PRIMARY KEY CLUSTERED 
([DatabaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[DatabaseCheckDBStatus](
	[CheckDBStatusID] [bigint] IDENTITY(1,1) NOT NULL,
	[DatabaseID] [int] NOT NULL,
	[CheckDate] [datetime] NOT NULL,
	[IsSuccess] [bit] NOT NULL,
 CONSTRAINT [PK_DatabaseCheckDBStatus] PRIMARY KEY CLUSTERED 
([CheckDBStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DatabaseCheckDBStatus] ADD  CONSTRAINT [DF_DatabaseCheckDBStatus_CheckDate]  DEFAULT (getdate()) FOR [CheckDate]
GO

ALTER TABLE [dbo].[DatabaseCheckDBStatus]  WITH CHECK ADD  CONSTRAINT [FK_DatabaseCheckDBStatus_Databases] FOREIGN KEY([DatabaseID])
REFERENCES [dbo].[Databases] ([DatabaseID])
GO

ALTER TABLE [dbo].[DatabaseCheckDBStatus] CHECK CONSTRAINT [FK_DatabaseCheckDBStatus_Databases]
GO



CREATE TABLE [dbo].[DatabaseCheckDB](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[CheckDBStatusID] [bigint] NOT NULL,
	[DatabaseID] [int] NOT NULL,
	[Error] [int] NULL,
	[Level] [int] NULL,
	[State] [int] NULL,
	[MessageText] [varchar](7000) NULL,
	[RepairLevel] [int] NULL,
	[Status] [int] NULL,
	[DbId] [int] NULL,
	[ObjectId] [int] NULL,
	[IndexId] [int] NULL,
	[PartitionID] [int] NULL,
	[AllocUnitID] [int] NULL,
	[File] [int] NULL,
	[Page] [int] NULL,
	[Slot] [int] NULL,
	[RefFile] [int] NULL,
	[RefPage] [int] NULL,
	[RefSlot] [int] NULL,
	[Allocation] [int] NULL,
 CONSTRAINT [PK_DatabaseCheckDB] PRIMARY KEY CLUSTERED 
([ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DatabaseCheckDB]  WITH CHECK ADD  CONSTRAINT [FK_DatabaseCheckDB_DatabaseCheckDBStatus] FOREIGN KEY([CheckDBStatusID])
REFERENCES [dbo].[DatabaseCheckDBStatus] ([CheckDBStatusID])
GO

ALTER TABLE [dbo].[DatabaseCheckDB] CHECK CONSTRAINT [FK_DatabaseCheckDB_DatabaseCheckDBStatus]
GO

ALTER TABLE [dbo].[DatabaseCheckDB]  WITH CHECK ADD  CONSTRAINT [FK_DatabaseCheckDB_Databases] FOREIGN KEY([DatabaseID])
REFERENCES [dbo].[Databases] ([DatabaseID])
GO

ALTER TABLE [dbo].[DatabaseCheckDB] CHECK CONSTRAINT [FK_DatabaseCheckDB_Databases]
GO




CREATE TABLE [dbo].[DbccCheckLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[logdate] [datetime] NULL,
	[ServerDatabaseID] [int] NULL,
	[DatabaseName] [varchar](200) NULL,
	[RestoreName] [varchar](200) NULL,
	[FinalStatus] [varchar](25) NULL,
	[ErrorMessage] [varchar](500) NULL,
	[BatchDigiDate8] [int] NULL,
 CONSTRAINT [PK_DbccCheckLog] PRIMARY KEY CLUSTERED 
([Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I want to make sure that the dbo.DATABASES table is loaded with current data. This PROCEDURE will do that. I can run it anytime a new databases is added or removed to update the table.

USE [Maintenance]
GO
/****** Object:  StoredProcedure [dbo].[DatabasesTabelSync]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Create date: 20231120
-- DescriptionThis syncs the Maintenance.dbo.Databases table with the dbo.sysdatabases table, updating IDs where needed.
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[DatabasesTabelSync] 
@ExcludeRestoreDbNameLike VARCHAR(100)='rds',
@ExcludeRestoreDbNameList VARCHAR(4000)='master,tempdb,model,msdb,SSISDB,DB_Clean_DataGeneration,TestDb'
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE @mxOrderBy INT;
	DECLARE @iLoopCount INT;
	DECLARE @tSysDatabaseList AS TABLE (DBID [INT] NOT NULL, Name VARCHAR(250) NULL, IsRestore BIT NULL)

	INSERT INTO @tSysDatabaseList
	SELECT DBID, Name, 1 from dbo.sysdatabases;

	-- Exclude RESTORE name for selected databases defined in params
	UPDATE L
	SET IsRestore = 0
	FROM @tSysDatabaseList AS L
	INNER JOIN string_split(@ExcludeRestoreDbNameList,',') AS S ON L.Name = S.value

	UPDATE @tSysDatabaseList 
	SET IsRestore = 0
	WHERE Name LIKE CONCAT('%', (SELECT TOP(1) Value FROM string_split(@ExcludeRestoreDbNameLike,',')) ,'%')

	-- CLEAR ALL ITEMS NOT FOUND ON CURRENT DATABASE
	UPDATE dbo.Databases
	SET OrderBy = NULL,
		RestoreName = NULL
	WHERE DatabaseName NOT IN (SELECT NAME FROM @tSysDatabaseList);

	-- REMOVE ENTRIES where a duplicate exists.
	DELETE
	FROM dbo.Databases
	WHERE DatabaseName IN 
		(	SELECT DataBaseName
			FROM dbo.Databases
			WHERE OrderBY > 0
			GROUP BY DatabaseName
			Having COUNT(*) > 1)

	-- UPDATE DBID
	UPDATE D
	SET ServerDBID = S.DBID
	FROM dbo.Databases AS D
	INNER JOIN @tSysDatabaseList AS S ON D.DatabaseName = S.Name

	-- Update RESTORE Names missing
	UPDATE D
	SET RestoreName = CONCAT('Restore_',D.DatabaseName)
	FROM dbo.Databases AS D
	INNER JOIN @tSysDatabaseList AS S ON D.DatabaseName = S.Name
	WHERE D.RestoreName IS NULL and S.IsRestore = 1

	-- INSERT NEW RECORDS
	INSERT INTO dbo.Databases
	(ServerDBID,DatabaseName,RestoreName)
	SELECT DBID, Name, CONCAT('Restore_', Name)
	FROM @tSysDatabaseList
	WHERE Name NOT IN (SELECT DatabaseName from dbo.Databases)

	-- Configure new items ORDER BY, incrimenting from MAX OrderBy found.
	SELECT @iLoopCount =  COUNT(*) FROM dbo.Databases WHERE RestoreName IS NOT NULL and OrderBy IS NULL  ;
	WHILE @iLoopCount > 0
	BEGIN
			SELECT @mxOrderBy = ISNULL(MAX(OrderBy),0)+1 FROM dbo.Databases;
			UPDATE TOP(1) dbo.Databases
			SET OrderBy = @mxOrderBy
			WHERE RestoreName IS NOT NULL AND OrderBy IS NULL;

			SELECT @iLoopCount = @iLoopCount - 1
	END
END

GO

RESTORE OPTION

Like I mentioned earlier, I want to use some of the PRODUCTION databases to refresh/replace the ones in DEVELOPMENT. There are databases in both environments with the same name that I DO NOT want to replace (ie. AUDIT, MAINTENANCE, SERVERANALYSIS, etc.) When I restore one of those databases I will PREFIX that database name with “Restore_” and then my solution will know to delete that copy right after checking it. The others will be left for the CLEANING SOLUTION to handle later.

Restore=0

The current database will be DROPPED and replaced with the copy from PRODUCTION with the same name. If this is DEV, you will want to check out my CLEANING BLOG POST to clean PI data from these. THE ORDER BY digit in the dbo.databases table should be > 100.

Restore=1

No databases are dropped and the copy that is RESTORED is prefixed with the word “Restore_” This is ideal to use for duplicated databases in PROD & DEV where each would be different, such as an Audit or Performance Analysis database. This may also be used in cases where you DO NOT want to refresh and/or wipe out the database on DEV. THE ORDER BY digit in the dbo.databases table needs of be within the range of 1 and 99. Alternatively, if there are zero (0) databases you want dropped and ALL to be prefixed with “Restore_” you may skip this DROP procedure.

DROP DATABASES

This will DROP those databases that I want to replace.

/****** Object:  StoredProcedure [dbo].[WeeklyDbccRds_DropDatabases]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Date:		20230323
-- NOTES:		Assumes this is a scheduled job on a SUNDAY utc time
--			This assumes PRODUCTION is running on AWS RDS SQL Server (Please, revise script if not.)
-- =============================================
CREATE OR ALTER       PROCEDURE [dbo].[WeeklyDbccRds_DropDatabases]
AS
BEGIN

-- SAFETY SO THIS CAN ONLY BE RUN ON A DEV SERVER
IF (select COUNT(*) from ServerAnalysis.Analysis.Server WHERE ServerNm= @@SERVERNAME and ServerResolvedname NOT LIKE 'prod%') > 0
BEGIN

	DECLARE @tDatabases as TABLE (ID INT NOT NULL IDENTITY(1,1), DatabaseID INT, DatabaseName varchar(250),  OrderBy INT, RestoreName  varchar(250), IsCdc BIT)
	DECLARE @BatchDateID INT;
	DECLARE @iCount INT;
	DECLARE @ErrorMsg varchar(500) = '';
	DECLARE @curDatabaseName varchar(500);
	DECLARE @tSql varchar(2000)='';
	DECLARE @isCdc BIT;

	-- Get the date of the most recent SUNDAY.
	SELECT @BatchDateID =  (CASE  DATEPART(weekday,getutcdate()) WHEN 1 
	THEN CONVERT(VARCHAR(10), getutcdate(),112)
	ELSE CONVERT(VARCHAR(10), (DATEADD(day,(1-DATEPART(weekday,getutcdate())), getutcdate())),112)
	END);

	-- Log start of job chain.
	INSERT INTO dbo.DbccCheckLog
	([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
	VALUES (getutcdate(),1,'WeeklyDbccRds','','JOB_START','',@BatchDateID);
	
	-- These databases with ORDERBY > 100 should all be marked Restore = 0.
	INSERT INTO @tDatabases
	select DatabaseID, DatabaseName,  OrderBy, RestoreName, 0
	from dbo.databases where orderby > 100;    -- FOR TESTING SET THIS TO SINGLE DIGIT

	-- Discover if change data capture is enabled on the database.
	Update d
	SET IsCdc = ISNULL(s.is_cdc_enabled,0)
	FROM @tDatabases AS d
	LEFT JOIN sys.databases as s on d.DatabaseName = s.name;

	select @iCount = MAX(ID) FROM @tDatabases;

	WHILE @iCount > 0
	BEGIN
		SELECT @curDatabaseName = DatabaseName, @isCdc = IsCdc
		FROM @tDatabases
		WHERE ID = @iCount;


		IF DB_ID(@curDatabaseName) IS NOT NULL
		BEGIN
			IF @isCdc = 1 
			BEGIN
			BEGIN TRY
				SELECT @tSql = CONCAT('msdb.dbo.rds_cdc_disable_db ''',@curDatabaseName,''';');
				EXEC(@tSql);		
				WAITFOR DELAY '00:00:05';
			END TRY
			BEGIN CATCH
				SELECT @ErrorMsg = CONCAT(@ErrorMsg,'CDC did not disable for ',@curDatabaseName,'. Sql: ',@tSql,' | ');
			END CATCH;
			END;

			BEGIN TRY			
				SELECT @tSql = CONCAT(' ALTER DATABASE [',@curDatabaseName,']  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  
											DROP DATABASE [',@curDatabaseName,'] ;');
				--SELECT @tSql = CONCAT('  msdb.dbo.rds_drop_database  N''',@curDatabaseName,''';');   -- Alternate method, but may work much slower.				
				EXEC(@tSql);				
			END TRY
			BEGIN CATCH
				SELECT @ErrorMsg = CONCAT(@ErrorMsg,' Database drop did not execute for ',@curDatabaseName,'. Sql: ',@tSql,' | ');
			END CATCH;
		END;	

		SELECT @iCount = @iCount - 1, @curDatabaseName = '',@tSql='',@isCdc=0;
	END;

	-- Log errors and details.
	INSERT INTO dbo.DbccCheckLog
	([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
	VALUES (getutcdate(),1,'WeeklyDbccRds','','STEP 1: Drop Databases',@ErrorMsg,@BatchDateID);
END
END

GO

RESTORE DATABASES

This PROCEDURE will RESTORE all the databases and add the “Restore_” prefix as defined.

/****** Object:  StoredProcedure [dbo].[WeeklyDbccRds_RestoreProdDbs]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Date:		20230323
-- NOTES:		Assumes this is a scheduled job on a SUNDAY utc time
--				This assumes PRODUCTION is running on AWS RDS SQL Server (Please, revise script if not.)
-- =============================================
CREATE OR ALTER       PROCEDURE [dbo].[WeeklyDbccRds_RestoreProdDbs]
@ProdAwsArnBackupLocation VARCHAR(250) = 'arn:aws:s3:::MyBucket/dbccbackup/prodsqlrds01/'
AS

BEGIN


-- SAFETY SO THIS CAN ONLY BE RUN ON A SERVER that is NOT named PROD.
IF (select COUNT(*) from ServerAnalysis.Analysis.Server WHERE ServerNm= @@SERVERNAME and ServerResolvedname NOT LIKE 'prod%') > 0
BEGIN

	DECLARE @tDatabases as TABLE (ID INT NOT NULL IDENTITY(1,1), DatabaseID INT, DatabaseName varchar(250),  OrderBy INT, RestoreName  varchar(250))
	DECLARE @BatchDateID INT;
	DECLARE @iCount INT;
	DECLARE @ErrorMsg varchar(2500) = '';
	DECLARE @curDatabaseName varchar(500);
	DECLARE @ProdDatabaseName varchar(500);
	DECLARE @tSql varchar(2000)='';
	DECLARE @CurrOrderBy INT=0;

	-- Get the date of the most recent SUNDAY
	SELECT @BatchDateID = (CASE  DATEPART(weekday,getutcdate()) WHEN 1 
	THEN CONVERT(VARCHAR(10), getutcdate(),112)
	ELSE CONVERT(VARCHAR(10), (DATEADD(day,(1-DATEPART(weekday,getutcdate())), getutcdate())),112)
	END);

	INSERT INTO dbo.DbccCheckLog
	([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
	VALUES (getutcdate(),1,'WeeklyDbccRds','','STEP 2 JOB_START','',@BatchDateID);

	INSERT INTO @tDatabases
	select DatabaseID, DatabaseName,  OrderBy, RestoreName
	from dbo.databases where orderby > 0;    -- FOR TESTING SET THIS TO SINGLE DIGIT ie:  =102
	
	select @iCount = MAX(ID) FROM @tDatabases;

	WHILE @iCount > 0
	BEGIN
		SELECT	@curDatabaseName = RestoreName,
				@ProdDatabaseName = (CASE WHEN LEFT(RestoreName,8) = 'Restore_' THEN REPLACE(RestoreName,'Restore_','' ) ELSE RestoreName END),
				@CurrOrderBy = OrderBy
		FROM	@tDatabases
		WHERE	ID = @iCount;

		-- Skipped if the database exists and was not dropped using a previous procedure or method.
		IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @curDatabaseName)
		BEGIN
			SELECT @ErrorMsg = CONCAT(@ErrorMsg,' DB present, skipped: ',@curDatabaseName,'. ');
		END
		ELSE
		BEGIN
		-- Schedule RDS server to restore the db.
		BEGIN TRY
			SELECT @tSql = CONCAT('EXEC msdb.dbo.rds_restore_database @restore_db_name=''',@curDatabaseName,''', @s3_arn_to_restore_from= ''',@ProdAwsArnBackupLocation,@ProdDatabaseName,'.bak'';');					
			EXEC(@tSql);

			INSERT INTO dbo.DbccCheckLog
			([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
			VALUES (getutcdate(),1,'WeeklyDbccRds',@curDatabaseName,'STEP 2: INFO',@tSql,@BatchDateID);
		END TRY
		BEGIN CATCH
			SELECT @ErrorMsg = CONCAT(@ErrorMsg,' Failed: ',@curDatabaseName,'. ');
		END CATCH
		END;
		SELECT @iCount = @iCount - 1, @curDatabaseName = '',@tSql='';
	END;

	PRINT @ErrorMsg;  -- Full error will appear in log when turned on.

	SELECT @ErrorMsg = LEFT(@ErrorMsg,495);

	INSERT INTO dbo.DbccCheckLog
	([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
	VALUES (getutcdate(),1,'WeeklyDbccRds','','STEP 2: Restore COMPLETE',@ErrorMsg,@BatchDateID);


END

END


GO

RUN CHECK

This is a combination of TWO PROCEDURES that run the DBCC CHECK on each database. They will also clean up and delete databases with the PREFIX “Restore_.”

/****** Object:  StoredProcedure [dbo].[MaintenanceDBCC]    Script Date: 11/21/2023 8:33:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Unknown
-- Date:	2004. Similar code found on multiple forums dating back to 2004.
--		20230403 -- Revised by David Speight.
-- NOTES:	Revised by David Speight to fit current Dbcc solution.
-- =============================================
CREATE OR ALTER     PROCEDURE [dbo].[MaintenanceDBCC]
	@ServerDBID int,
	@DatabaseName varchar(200),
	@IsRestore bit,
	@AgentProfile varchar(100) = 'MyPROFILE',
	@Emailrecipients varchar(200) = 'my.name@myCompany.com'
AS
BEGIN
	-- sql 2012 VERSION	
	SET NOCOUNT ON;
	
	DECLARE @DatabaseID int,  @CheckDBStatusID bigint;
	DECLARE @ErrorCount int = 0;
	DECLARE @IsSuccess bit = 0;

	/* get our database id */
	SELECT	@DatabaseID = DatabaseID,
		@DatabaseName = (Case @IsRestore WHEN 1 THEN CONCAT('Restore_',@DatabaseName) ELSE @DatabaseName END)
	FROM	Maintenance.dbo.Databases  with(nolock)
	WHERE	ServerDBID = @ServerDBID and
		DatabaseName = @DatabaseName and
		RestoreName IS NOT NULL;

	/* create temp table to place the output from dbbc check; sql 2012 UPDATE */
	DECLARE @CheckDB table (ID int identity(1,1),
		Error INT,
		[Level] INT,
		[State] INT,
		MessageText VARCHAR(7000),
		RepairLevel INT,
		[Status] INT,
		[DbId] INT,
		[DbFragId] INT,
		ObjectId INT,
		IndexId INT,
		PartitionID INT,
		AllocUnitID INT,
		[RidDbld] INT,
		[RidPruld] INT,
		[File] INT,
		Page INT,
		Slot INT,
		[RefDbld] INT,
		[RefPruld] INT,
		RefFile INT,
		RefPage INT,
		RefSlot INT,
		Allocation INT );

		/* run chefk and populate tmep table */
		INSERT INTO @CheckDB (Error, [Level], [State], MessageText, RepairLevel, [Status], [DbId], [DbFragId], ObjectId, IndexId, PartitionId, AllocUnitId, [RidDbld],[RidPruld], [File], [Page], Slot, [RefDbld], [RefPruld], RefFile, RefPage, RefSlot,Allocation)
		EXEC ('dbcc checkdb(''' + @DatabaseName + ''') with tableresults');

		/* determine if the check is a success */
		SELECT  TOP 1 @ErrorCount =  ((LEN(MessageText) - LEN(REPLACE(MessageText,'0','')))) - ((LEN(@DatabaseName) - LEN(REPLACE(@DatabaseName,'0',''))))
		FROM @CheckDB
		ORDER BY ID DESC;

		SELECT @IsSuccess = (case when @ErrorCount = 2 then 1 else 0 end);

		/* log the run */
		INSERT INTO Maintenance.dbo.DatabaseCheckDBStatus (DatabaseID,CheckDate,IsSuccess) VALUES (@DatabaseID,(getdate()),@IsSuccess);
		SET @CheckDBStatusID = SCOPE_IDENTITY();
	
		/* log the lastline of output */
		INSERT INTO Maintenance.dbo.DatabaseCheckDB (CheckDBStatusID, DatabaseID, Error, [Level], [State], MessageText, RepairLevel, [Status], [DbId], ObjectId, IndexId, PartitionID, AllocUnitID, [File], Page, Slot, RefFile, RefPage, RefSlot, Allocation)
		SELECT TOP 1 @CheckDBStatusID, @DatabaseID, Error, [Level], [State], MessageText, RepairLevel, [Status], [DbId], ObjectId, IndexId, PartitionID, AllocUnitID, [File], Page, Slot, RefFile, RefPage, RefSlot, Allocation
		FROM @CheckDB
		ORDER BY ID DESC;

		/* clean it all up */
		IF @IsSuccess = 0
		BEGIN
		DECLARE @Subject VARCHAR(200), @Body VARCHAR(1000);
		SELECT	@Subject = 'CheckDB Failed for ' + @DatabaseName + ' on ' + @@SERVERNAME,
		@Body = 'CheckDB Failed Database: ' + @DatabaseName + ' Server ' + @@SERVERNAME + 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),121);

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @AgentProfile,
			@recipients = @Emailrecipients,
			@subject = @Subject,
			@body = @Body;

		END;
END


GO

/****** Object:  StoredProcedure [dbo].[WeeklyDbccRds_RunCheck]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Date:		20230403
-- NOTES:		Assumes this is a scheduled job on a SUNDAY utc time
--				This assumes PRODUCTION is running on AWS RDS SQL Server (But should work with non-RDS as well.)
-- =============================================
CREATE OR ALTER      PROCEDURE [dbo].[WeeklyDbccRds_RunCheck]
AS
BEGIN

-- SAFETY SO THIS CAN ONLY BE RUN ON A DEV SERVERNOT 
IF (select COUNT(*) from ServerAnalysis.Analysis.Server WHERE ServerNm= @@SERVERNAME and ServerResolvedname NOT LIKE 'prod%') > 0
BEGIN

	DECLARE @tDatabases as TABLE (ID INT NOT NULL IDENTITY(1,1), DatabaseID INT, DatabaseName varchar(250),  OrderBy INT, RestoreName  varchar(250), ServerDatabaseID INT)
	DECLARE @BatchDateID INT;
	DECLARE @iCount INT;
	DECLARE @ErrorMsg varchar(2500) = '';
	DECLARE @curDatabaseName varchar(500);
	DECLARE @tSql varchar(2000)='';
	DECLARE @isRestore CHAR(1)='0';
	DECLARE @iLoopMaxID INT = 0;
	DECLARE @iLoopCur INT = 0;
	DECLARE @DBID INT=0;
	DECLARE @sqlDbccMaintenance varchar(1000);

	-- UPDATE DATABASE IDs from existing sys databases
	UPDATE D
	SET ServerDBID = S.database_id
	FROM dbo.Databases AS D 
	INNER JOIN sys.Databases AS S ON D.DatabaseName = S.name;	

	select * from dbo.sysDatabases

	-- Get the date of the most recent SUNDAY
	SELECT @BatchDateID = (CASE  DATEPART(weekday,getutcdate()) WHEN 1 
	THEN CONVERT(VARCHAR(10), getutcdate(),112)
	ELSE CONVERT(VARCHAR(10), (DATEADD(day,(1-DATEPART(weekday,getutcdate())), getutcdate())),112)
	END);

	INSERT INTO dbo.DbccCheckLog
	([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
	VALUES (getutcdate(),1,'WeeklyDbccRds','RUN CHECK','RUN CHECK JOB_START','',@BatchDateID);

	INSERT INTO @tDatabases
	select DatabaseID, DatabaseName,  OrderBy, RestoreName, ServerDBID
	from dbo.databases where orderby  > 0   -- FOR TESTING SET THIS TO SINGLE DIGIT ie:  =102

	select @iCount = MAX(ID) FROM @tDatabases;

	WHILE @iCount > 0
	BEGIN
		SELECT @curDatabaseName = RestoreName, @DBID=ServerDatabaseID
		FROM @tDatabases
		WHERE ID = @iCount;

		IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @curDatabaseName)
		BEGIN
		SELECT @ErrorMsg = CONCAT(@ErrorMsg,' DB not present: ',@curDatabaseName,'. ');
		END
		ELSE
		BEGIN
		BEGIN TRY
			INSERT INTO dbo.DbccCheckLog
			([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
			VALUES (getutcdate(),@DBID,@curDatabaseName, @curDatabaseName,'DBCC Started','',@BatchDateID);

			-- 20231120 -- Bug fix for IsRestore prefixed databases.
			SELECT @isRestore = (CASE WHEN CHARINDEX('Restore',@curDatabaseName) = 0 THEN '0' ELSE '1' END);
									
			SELECT @sqlDbccMaintenance=CONCAT('dbo.MaintenanceDBCC @ServerDBID=',@DBID,', @DatabaseName=''',@curDatabaseName,''', @IsRestore=',@isRestore) 
			EXEC(@sqlDbccMaintenance);					
		END TRY
		BEGIN CATCH
			SELECT @ErrorMsg = CONCAT(@ErrorMsg,' DBCC not completd: ',@curDatabaseName,'. ');
		END CATCH;
		END;
		SELECT @iCount = @iCount - 1, @curDatabaseName = '',@tSql='';
	END;

	--- DROP RESTORE_ DATABASES
	select @iCount = MAX(ID) FROM @tDatabases;

	WHILE @iCount > 0
	BEGIN
		SELECT @curDatabaseName = RestoreName
		FROM @tDatabases
		WHERE ID = @iCount;

		-- DROP database if prefixed with Restore_
		if left(@curDatabaseName,7) = 'Restore'
		BEGIN TRY
			WAITFOR DELAY '00:00:05';
			SELECT @tSql = CONCAT(' ALTER DATABASE [',@curDatabaseName,']  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  
			DROP DATABASE [',@curDatabaseName,'] ;');				
			EXEC(@tSql);
		END TRY
		BEGIN CATCH
			SELECT @ErrorMsg = CONCAT(@ErrorMsg,' DB not dropped: ',@curDatabaseName,'. ');
		END CATCH;		
	
		SELECT @iCount = @iCount - 1, @curDatabaseName = '',@tSql='';
	END;

	PRINT @ErrorMsg;  -- Full error will appear in sql manager log when turned on.

	SELECT @ErrorMsg = LEFT(@ErrorMsg,495);

	INSERT INTO dbo.DbccCheckLog
	([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
	VALUES (getutcdate(),1,'WeeklyDbccRds','','DBCC Run Check END',@ErrorMsg,@BatchDateID);

	SELECT @ErrorMsg ='*** END ***';

	INSERT INTO dbo.DbccCheckLog
	([logdate], [ServerDatabaseID], [DatabaseName], [RestoreName], [FinalStatus], [ErrorMessage], [BatchDigiDate8])
	VALUES (getutcdate(),1,'WeeklyDbccRds','','BATCH END: DBCC JOBS',@ErrorMsg,@BatchDateID);
END
END


GO

AUTOMATE and VIEW LOG

I simply schedule this to run on Sunday morning leaving about two (2) hours between each step.

When I come in Monday morning, I use this PROCEDURE to view the LOGS and see if there are any errors I need to address.

/****** Object:  StoredProcedure [dbo].[WeeklyDbccRds_GetLatestLogs]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Date:		20231120
-- =============================================
CREATE OR ALTER      PROCEDURE [dbo].[WeeklyDbccRds_GetLatestLogs]
AS
BEGIN


	DECLARE @maxDate DATE;

	SELECT @maxDate = MAX(CheckDate) FROM dbo.DatabaseCheckDBStatus WITH(NOLOCK);

	SELECT D.DatabaseID, D.DatabaseName, D.RestoreName, S.*, C.*
	FROM dbo.Databases AS D
	INNER JOIN dbo.DatabaseCheckDBStatus AS S ON D.DatabaseID = S.DatabaseID
	INNER JOIN dbo.DatabaseCheckDB AS C ON S.CheckDBStatusID = C.CheckDBStatusID
	WHERE S.CheckDate > @maxDate
	ORDER BY D.DatabaseName;


	select top(1000) * 
	FROM dbo.DbccCheckLog
	WHERE LogDate > @maxDate
	ORDER BY 1;


END


GO

Cheers!