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.
exec msdb.dbo.rds_backup_database @source_db_name='Sunny', @s3_arn_to_backup_to='arn:aws:s3:::sqlserver2023testingbackup/sunny1.bak', @overwrite_S3_backup_file=1; 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
Cause: The issue with permission to enable the backup or restore policy for the database
Solution: 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:
- Go to the IAM console in AWS –> Create a new Role with following permissions:
Steps to create a new role as follows: click on IAM console — > Create a new role:
Go to Next, after type RDS in use cases for other AWS Services: Select RDS-ADD Role to Database.
Type S3 in Search and select the policy as: AmazonS3FullAccess
Again, type backup and select the policy as: AWSBackupServiceRolePolicyForBackup and AWSBackupServiceRolePolicyForRestores
Create the Role by specifying the name:
Finally, Role is created for used in the database instance running in RDS Service of AWS.
Add the Role into the RDS instance for running the backup or avoid the permission issue that occurred
Go to the RDS Service –> Select the option groups in RDS main console window at left panel.
Create the new group by clicking CREATE GROUP button:
Fill the detail for create group:
Check the version of database from SQL management studio with select @@VERSION command. I am using express edition.
Click on the new created group and choose the add option
Select the following option and IAM role which is created above:
Finally, Now adds this option group to the database.
Go to the Database tab — Select the database name on which you want to modify the option group for running backup.
select database-1 for modify:
Go to option group and select the new group:
Press Continue at last of the screen:
After sometime the database Status becomes available, then we tried to rerun the backup with command. Hopefully it will fix the issue.
exec msdb.dbo.rds_backup_database @source_db_name='Sunny', @s3_arn_to_backup_to='arn:aws:s3:::sqlserver2023testingbackup/sunny1.bak', @overwrite_S3_backup_file=1;
Pingback: Backup and Restore RDS SQL Server database to S3 storage in AWS | Smart way of Technology
Pingback: Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup. | Smart way of Technology