I want to backup microsoft SQL database to RDS. I have setup all prerequisites now I am having error while msdb.dbo.rds_backup_database command

Question

I have setup everythig but i am not able to run

```
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws:s3:::mybucket/backup1.bak',
@overwrite_s3_backup_file=1,
@type='DIFFERENTIAL';
```
I connected to mssql via SQLcmd and the master user and password.

I am getting the following error.

Msg 50000, Level 16, State 1, Server EC2AMAZ-RR2AQ97, Procedure msdb.dbo.rds_backup_database, Line 119
Database backups can only be performed by members of db_owner or db_backupoperator roles in the source database
1>

I have also tried this solution too.
https://repost.aws/questions/QUk-ltmINRSQujBSVkWrpqUw/rds-microsoft-sql-admin-user-permission-to-run-msdb-dbo-rds-restore-database-restore-command

re:Post Agent · Accepted Answer

The error you're encountering suggests that your current user doesn't have the necessary permissions to perform database backups in Amazon RDS for SQL Server. To resolve this issue, you need to ensure that your user has the appropriate role assignments. Here are the steps you can take:

1. Connect to your RDS SQL Server instance using a user with sufficient privileges (like the master user).

2. Add your user to the 'db_backupoperator' role for the specific database you're trying to back up. You can do this by executing the following SQL command:

USE [mydatabase];
   EXEC sp_addrolemember 'db_backupoperator', 'your_username';

Replace 'mydatabase' with the name of the database you're backing up, and 'your_username' with the actual username you're using.

3. If you need to perform backups on multiple databases, you may need to repeat this process for each database.

4. After adding the user to the appropriate role, try running the backup command again.

If you're still encountering issues after following these steps, you may need to check the following:

- Ensure that the SQLSERVER_BACKUP_RESTORE option is enabled in your RDS option group.
- Verify that your IAM role has the necessary permissions to access the S3 bucket.
- Double-check that the S3 bucket name and ARN are correct in your backup command.

Remember that in Amazon RDS, you don't have direct access to grant BACKUP DATABASE permissions. Instead, you need to use the built-in roles and the RDS-specific stored procedures for managing backups.  
**Sources**  
[AWS RDS Native SQL Backups - rds_deny_backups_trigger | AWS re:Post](https://repost.aws/questions/QUjgFbMPrxREabtufAjfvHjw/aws-rds-native-sql-backups-rds-deny-backups-trigger)  
[Troubleshooting - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.Native.Troubleshooting.html)  
[Native SQL Server backup/restore - AWS Prescriptive Guidance](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/native-backup-restore.html)

I want to backup microsoft SQL database to RDS. I have setup all prerequisites now I am having error while msdb.dbo.rds_backup_database command

Add your answer

Relevant content