RDS SQL Server : Script to automate native backup of all the individual databases using msdb.dbo.rds_backup_database Stored procedure

You can use the below command to automate native backup of all the databases using msdb.dbo.rds_backup_database Stored procedure. You can also use the below script to schedule as a agent job to take native backup (in .bak file) of your all databases.

Script to take Full backup without encryption

DECLARE @BackupFileName varchar(50)
DECLARE @DBName sysname
DECLARE @S3ARN_Prefix nvarchar(100)
DECLARE @S3ARN nvarchar(100)
SET @S3ARN_Prefix = 'arn:aws:s3:::awsbucket_name/' -- update your bucketname

DECLARE DBBackup CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('rdsadmin', 'master', 'model', 'msdb', 'tempdb')
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_read_only = 0

OPEN DBBackup
FETCH NEXT FROM DBBackup INTO @DBName
WHILE (@@FETCH_STATUS = 0)

BEGIN
SET @BackupFileName = @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak'
SET @S3ARN = @S3ARN_Prefix + @BackupFileName
EXEC msdb.dbo.rds_backup_database
@source_db_name = @DBName,
@S3_arn_to_backup_to = @S3ARN,
@overwrite_S3_backup_file = 0
FETCH NEXT FROM DBBackup INTO @DBName
END

Script to take Full backup with encryption

DECLARE @BackupFileName varchar(50)
DECLARE @DBName sysname
DECLARE @S3ARN_Prefix nvarchar(100)
DECLARE @S3ARN nvarchar(100)
DECLARE @KMS_master_key_ARN nvarchar(100)
SET @S3ARN_Prefix = 'arn:aws:s3:::awsbucket_name/' -- update your bucketname
SET @KMS_master_key_ARN = 'arn:aws:kms:us-east-1:XXXXXXXXXX:key/xxxxxxxx-xxxx-xxxx-xxxx-xxxx' -- update your kms key ARN

DECLARE DBBackup CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('rdsadmin', 'master', 'model', 'msdb', 'tempdb')
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_read_only = 0

OPEN DBBackup
FETCH NEXT FROM DBBackup INTO @DBName
WHILE (@@FETCH_STATUS = 0)

BEGIN
SET @BackupFileName = @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak'
SET @S3ARN = @S3ARN_Prefix + @BackupFileName
EXEC msdb.dbo.rds_backup_database
@source_db_name = @DBName,
@S3_arn_to_backup_to = @S3ARN,
@KMS_master_key_arn = @KMS_master_key_ARN,
@overwrite_S3_backup_file = 0
FETCH NEXT FROM DBBackup INTO @DBName
END

To track the status of the job, use this SQL statement:

exec msdb..rds_task_status @task_id= 5 -- 5 as an example of your task id