AWS – RDS – SQL Server – Scheduling Database Backups

Background

Preparing for a meeting where we will be discussing our experience with Amazon AWS /RDS Deployment.

And, so it is time to document and share a bit more.

 

Code

  1. We will have a set of Stored Procedures
    •  dbo.usp_RDSDBBackupCore
      • The core SP
    • dbo.usp_RDSDBBackupFullAutomated
      • Manager
        • passes in targeted S3 folder and backup type

Stored Procedure

dbo.usp_RDSDBBackupCore



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if object_id('[dbo].[usp_RDSDBBackupCore]') is null
begin

    exec('create procedure [dbo].[usp_RDSDBBackupCore] as ')

end
go

ALTER PROCEDURE [dbo].[usp_RDSDBBackupCore]
(
      @s3Bucket		varchar(100) 
    , @s3Folder		varchar(100) = null
    , @type			varchar(30) = 'FULL'
	, @addTS		bit         = 0
	, @dbName		sysname		= null
	, @scriptOnly	bit = 0

)
AS
BEGIN

	/*
		-- =============================================
		-- Author:		Daniel Adeniji
		-- =============================================
	*/
    SET NOCOUNT ON;
	SET XACT_ABORT ON;


	declare @tblDatabase TABLE
	(
		  [id] smallint not null
			identity(1,1)

		, [name] sysname not null
	)

	declare @tblDatabaseSkip TABLE
	(
		  [id] smallint not null
			identity(1,1)

		, [name] sysname not null

		, UNIQUE NONCLUSTERED 
			(
				[name]
			)
	)

	declare @id smallint
	declare @idMax smallint
	declare @db    sysname

	declare @s3arnBase			   varchar(30)
	declare @s3arn				   sysname
	declare @overwriteS3BackupFile int

	declare @CHAR_BACKSLASH        char(1)
	declare @CHAR_DASH			   char(1)
	declare @CHAR_COLON			   char(1)
	declare @CHAR_PERIOD		   char(1)
	
	declare @CHAR_EXT_BAK		   varchar(10)
	declare @CHAR_EXT_LOG		   varchar(10)
	declare @charExt               varchar(10)

	declare @backupTypeFULL		   varchar(15)
	declare @backupTypeLOG         varchar(15)

	declare @dtNow				   datetime
	declare @dateFormat			   smallint
	declare @TSAsString			   varchar(30)

	declare @log				   varchar(300)

	set    @s3arnBase = 'arn:aws:s3:::'


	set    @CHAR_EXT_BAK = '.bak'
	set    @CHAR_EXT_LOG = '.log'

	set    @backupTypeFULL = 'full';
	set    @backupTypeLOG = 'log';

	set @CHAR_DASH = '-'
	set @CHAR_BACKSLASH = '/'
	set @CHAR_COLON = ':'
	set @CHAR_PERIOD = '.'


	set @overwriteS3BackupFile = 1

	set @dateFormat = 126

	if (@type = @backupTypeLOG)
	begin

		set @charExt = @CHAR_EXT_LOG 

	end
	else if (@type = @backupTypeFULL)
	begin

		set @charExt = @CHAR_EXT_BAK 

	end
	else
	begin

		raiserror('Unsupported @type', 16,1)

		return
	end

	/*
		List databases that we will be skipping
			System Databases
				a) master
				b) tempdb
				c) model
				d) msdb
	*/
	 insert @tblDatabaseSkip
	 (
		[name]
	 )
	 select 'tempdb'
	 union 
	 select 'msdb'
	 union 
	 select 'master'
	 union 
	 select 'model'
	 union 
	 select 'rdsadmin'


	if (@dbName is not null)
	begin

		insert into  @tblDatabase 
		(
			[name]
		)
		select
			@dbname

	end
	else
	begin

		/*

			Skip Databases that are offline

				a) databasepropertyex(name, 'collation')	
						is null when a db is offline

		*/
		insert into  @tblDatabase 
		(
			[name]
		)
		select   tblSD.name

		from   sys.databases tblSD

		where  not exists
				(
					select [name]
					from   @tblDatabaseSkip tblDS
					where  tblSD.[name] = tblDS.[name]
				)

		and   databasepropertyex
				(
					   tblSD.[name]
					, 'collation'
				) 
					is not null

	end

	/*
		Get Number of databases
	*/
	set @idMax
			=
				(
					select max(id)
					from    @tblDatabase 
				)

	/*
		reset pointer
	*/
	set @id = 1

	while (@id <= @idMax)
	begin

		/*
			Get contextual data
		*/
		select 
				@db = tblD.name
		from   @tblDatabase tblD
		where  tbLD.id = @id

		if (@addTS = 1)
		begin

			set @dtNow = getdate()

			set @TSAsString = convert(varchar(30), @dtNow, @dateFormat)
			set @TSAsString = replace(@TSAsString, @CHAR_DASH, '')
			set @TSAsString = replace(@TSAsString, @CHAR_COLON, '')
			set @TSAsString = replace(@TSAsString, @CHAR_PERIOD, '')

		end

		/*
			Get computed data
		*/
		set  @s3arn =  @s3arnBase 
						+ @s3Bucket 
						+ case
							when @s3folder is null then ''
							when @s3folder = '' then ''
							else @CHAR_BACKSLASH + + @s3folder 
							end
							
						+ @CHAR_BACKSLASH
						+ @db
						+ case
							when (@addTS = 1) 
								then '__' + @TSAsString
							else ''
						  end	
						+ @charExt

		/*
			issue backup command
				note that backup is not completed, only issued
		*/
		set @log = 's3arn :-' + @s3arn

		print @log

		if (
				   ( @scriptOnly = 0)
				or ( @scriptOnly is null)
		   )
		begin

			exec msdb.dbo.rds_backup_database 
				  @source_db_name= @db
				, @s3_arn_to_backup_to=@s3arn
				, @overwrite_S3_backup_file = @overwriteS3BackupFile
				, @type = @type

		end

		/*
			prepare for next record
		*/
		set @id = @id + 1

	end


END
GO



dbo.usp_RDSDBBackupFullAutomated



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[usp_RDSDBBackupFullAutomated]') is null
begin

    exec('create procedure [dbo].[usp_RDSDBBackupFullAutomated] as ')

end
go

ALTER PROCEDURE [dbo].[usp_RDSDBBackupFullAutomated]
AS
BEGIN


    SET NOCOUNT ON;

    declare  @s3Bucket  varchar(100) 
    declare  @s3Folder  varchar(100) 
    declare  @type	    varchar(30)
	declare  @addTS		bit

    set @s3Bucket = 'dbabackupmssql'
    set @s3Folder = 'full'
    set @type = 'FULL'
	set @addTS = 1

    exec [dbo].[usp_RDSDBBackupCore]
              @s3Bucket = @s3Bucket
            , @s3Folder = @s3Folder
            , @type = @type
	    , @addTS = @addTS


END
GO





Scheduler

Invoke Scheduler and scheduled job that calls the  dbo.usp_RDSDBBackupFullAutomated Stored Procedure.

Job Properties -Tab – General

 

Job Properties -Tab – Steps

 

Review

SQL Server

Stored Procedure

Stored Procedure – msdb.dbo.rds_task_status

Script

declare @dbName sysname

exec msdb.[dbo].[rds_task_status]
       @db_name = @dbName

Output

 

S3

Access S3 Service and the specific folder and you should see the files.

 

Source Control

GitHub

DanielAdeniji/SQLServerBackup.AWS
Link

Leave a Reply

Please log in using one of these methods to post your comment:

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 )

w

Connecting to %s