SQL Server Backup Script for AWS RDS

DECLARE @dbs TABLE (dbname VARCHAR(50))
DECLARE @currentDB VARCHAR(50)

declare @SQL1 nvarchar(3000)
declare @SQL2 nvarchar(3000)

declare @BUCKET nvarchar(200)
-- change the bucket name based on environment
set @BUCKET='arn:aws:s3:::ee-development-db-backups'

DECLARE @DATE AS VARCHAR(10)
SET @DATE= CONVERT(VARCHAR(8),GETDATE(),112)
INSERT INTO @dbs (dbname)



SELECT [Name]
FROM master.sys.databases WHERE name not in ('master','model','msdb','tempdb','rdsadmin')


WHILE EXISTS (SELECT dbname FROM @dbs)

BEGIN

SET @currentDB = (SELECT TOP 1 dbname FROM @dbs ORDER BY dbname)
SET @SQL1='exec msdb.dbo.rds_backup_database @source_db_name=''' + @currentDB + ''', @s3_arn_to_backup_to=''' + @BUCKET + '/14DY_' + @currentDB + '_' + @DATE + '.bak'', @overwrite_S3_backup_file=1;'
SET @SQL2='exec msdb.dbo.rds_backup_database @source_db_name=''' + @currentDB + ''', @s3_arn_to_backup_to=''' + @BUCKET + '/7YR_' + @currentDB + '_' + @DATE + '.bak'', @overwrite_S3_backup_file=1;'


if day(getdate()) = '01'

   exec sp_executesql @SQL2

else

  exec sp_executesql @SQL1


DELETE FROM @dbs WHERE dbname = @currentDB

END