Update Reporting from Live (part 1 of 2)

I can hardly believe I am revisiting my old “Smart Diff backup / restore” project from 2015 – in this – the age of the cloud!

This time around I chose to use native commands instead of Ola’s scripts as the architecture was simpler. And SQL Server 2008r2 (yes, I know).

To start, I made a SQL job on Live “Backup for Reporting” to create a local backup …

Step-1 “Jump to Diff or Full backup”

This step attempts to manage smart diff-backups. If it is Friday, or there is no current full backup, or if some 3rd party has taken a full backup (rendering our full backup obsolete), then a full backup is taken via step-2.

If none of the above are true, over an hour can be saved by jumping directly to Step-3.

(NOTE: Step-1 was set to go to Step-3 on success, or the next-step on failure).

/* 1. Do a FULL backup if its Friday */

	SET DATEFIRST 7 /* FirstDayOfWeek = Sunday, so Friday = 6 */
	IF (SELECT DATEPART(WEEKDAY, GETDATE())) = 6 /* its Friday */	
	BEGIN
		RAISERROR ('Force this Job-Step to Fail - Its Friday', 16, 1)
		Return
	END

/* 2. Do a FULL backup if there aren't any */
 
	DECLARE @backups char(1)
	EXEC @backups = [titan].[master].[sys].[xp_cmdshell] 'DIR /b X:\SQLBackups\CMI_ProDB_Audit_Live\*FULL.bak'
	IF (select @backups) > 0 /* 0 = found, 1 = not found */
	BEGIN
		RAISERROR ('Force this Job-Step to Fail - There are no FULL backups', 16, 1)
		Return
	END

/* 3. Do a FULL backup if the LSNs don't match */

	IF	(SELECT isnull(MAX(differential_base_lsn), 1)
		FROM [Titan].[master].[sys].[master_files]
		WHERE physical_name like '%Audit%')
		!=
		(SELECT isnull(MAX(differential_base_lsn), 1)
		FROM [SQLREPORTING].[master].[sys].[master_files]
		WHERE physical_name like '%Audit%')
	BEGIN
		RAISERROR ('Force this Job-Step to Fail - The LSNs do not match', 16, 1)
		Return
	END

/* Else jump to the DIFF-backup step */

	Select 'We can do a DIFF backup'
	Print 'We can do a DIFF backup'

Step-2 “Create a new FULL backup”

This step creates a Full backup locally, overwriting any that already exist. When complete a Diff backup is taken via step-3.

A Diff backup immediately after a Full backup is not strictly necessary, but ensures that a) the restore process can be simple and robust (IE: a Full backup is always restored, then a Diff backup is always restored), and b) that we never have a Diff backup older that the partnering Full backup.

BACKUP DATABASE [LiveDatabaseName] 

TO DISK =  N'V:\SQLBackups\LiveDatabaseName\LiveDatabaseName_FULL.bak'

WITH CHECKSUM, COMPRESSION, INIT;

Step-3 “Create a new DIFF backup”

This step creates a Diff backup (containing changes since the last Full backup), overwriting any Diff backup for this database that already exists.

BACKUP DATABASE [LiveDatabaseName]

TO DISK =  N'V:\SQLBackups\LiveDatabaseName\LiveDatabaseName_DIFF.bak'

WITH CHECKSUM, COMPRESSION, DIFFERENTIAL, INIT;

Step-4 “Start the Restore job”.

Kicks-off the restore job on the Report Server.

EXECUTE [ReportServerName].[msdb].[dbo].[sp_start_job] 'Restore LiveDatabaseName';

Next I will detail the steps in the “restore” job on the report server.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s