Backup the RDS SQL Server database to S3 storage in AWS

1. You must have the S3 Storage & database instance of SQL Server already configured in AWS.
2. For taking the backup you go to the SQL Management studio and connect the database of SQL Server in your local machine:

3. Connecting with AWS SQL Server RDS service the object explorer look like as follows. the RDSADMIN is the default database present which is used by AWS.

4. For Backup the database, we are creating a test database with name SUNNY.

5. After creating, start the new query window on your local SQL Server Management Studio and run the following command for backup the database.

-- RDS syntax:
exec msdb.dbo.rds_backup_database 
@source_db_name='databasename', @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension', 
@overwrite_S3_backup_file=1;

-- Example RDS syntax:

exec msdb.dbo.rds_backup_database 
@source_db_name='Sunny', @s3_arn_to_backup_to='arn:aws:s3:::sqlserver2023testingbackup/sunny.bak', 
@overwrite_S3_backup_file=1;

Error:

Msg 50000, Level 16, State 0, Procedure msdb.dbo.rds_backup_database, Line 120 [Batch Start Line 9]
Database backup/restore option is not enabled or is in the process of being enabled. Please try again later.
USAGE: 
        EXECUTE msdb.dbo.rds_backup_database @source_db_name, @s3_arn_to_backup_to, [@kms_master_key_arn], [@overwrite_s3_backup_file], [@type], [@number_of_files]

@source_db_name           : Source database name to create a full backup of.
@s3_arn_to_backup_to      : S3 key ARN to store the backup file at.
@kms_master_key_arn       : KMS customer master key ARN to encrypt the backup file with.
@overwrite_s3_backup_file : Indicates whether to overwrite the specified file in S3 or not, if one exists.
@type                     : Type of backup to create. Valid options are: FULL and DIFFERENTIAL. Defaults to FULL.
@number_of_files          : (Multifile backups only) Number of files to create for this backup.

Completion time: 2023-02-03T16:35:51.1447142+05:30

To Overcome this error during running backup issue. Issue with the permission of S3 and backup permission. We need to create a role and assign to the database to overcome this error for backup:

Follow the link for solution: https://smarttechways.com/2023/02/03/procedure-msdb-dbo-rds_backup_database-line-120-batch-start-line-9-database-backup-restore-option-is-not-enabled/

6. Check the Status of command for the backup is completed or in progress:

exec msdb.dbo.rds_task_status

7. Verify the backup is created into the S3 Storage. By checking at S3 from AWS console login.

Restore the SQL Server database from S3 Storage

Restore the SQL Server database from S3 Storage backup and create a new database name “HELLO” from that backup.

exec msdb.dbo.rds_restore_database @restore_db_name='Hello', 
@s3_arn_to_restore_from='arn:aws:s3:::sqlserver2023testingbackup/sunny.bak';

Check the Status after restore command:

Hello database is created in SQL Server Management Studio. You can be verified in object Explorer of SQL Server Management Studio.