Snippet Name: RDS_SQL_Server_Backup
Tags:
Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:32:16
Dependencies:
DBA Notes:
Sample Output:
Title: RDS SQL Server Backup
Author: user
in AWs RDS for SQLServer, i am issueing this command for a striped backup, which defintiely exist son teh S3 bucket,a dn previous single file backups work successfully
Author: ChatGPT
It looks like your message got cut off before you could include the command or the error you're running into.
Can you please share:
1. The **exact `rds_backup_database` command** or T-SQL you're using for the **striped backup**.
2. The **S3 bucket location and filenames** you're writing to.
3. The **error message**, if any.
4. Confirmation that the **option group has the `SQLSERVER_BACKUP_RESTORE` feature** enabled.
5. Whether you're using **Amazon RDS SQL Server Standard/Enterprise/Express**, and the **version**.
Once I have that, I can help troubleshoot your striped backup issue more precisely.
Author: user
exec msdb.dbo.rds_restore_database
@restore_db_name='GYMT_GYMENGINE',
@s3_arn_to_restore_from='arn:aws:s3:::dev26db-backup/GYMTGYMENGINE*' but i get this error
Author: ChatGPT
Thanks, Lowell ??? your use of the `rds_restore_database` command with a wildcard (`*`) in the `@s3_arn_to_restore_from` is actually the issue here.
### ??????**Problem:**
`@s3_arn_to_restore_from` **does not support wildcards** (`*`). Even if you're restoring a striped backup that spans multiple files (like `GYMTGYMENGINE_1.bak`, `GYMTGYMENGINE_2.bak`, etc.), you must **list each file explicitly**, separated by semicolons.
---
### ??? **Correct Syntax for Striped Backup Restore:**
If your backup was created in multiple parts (striped backup), and they???re in the same S3 bucket like:
```
arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_part1.bak
arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_part2.bak
arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_part3.bak
```
Then your restore should look like this:
```sql
exec msdb.dbo.rds_restore_database
@restore_db_name = 'GYMT_GYMENGINE',
@s3_arn_to_restore_from = 'arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_part1.bak;
arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_part2.bak;
arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_part3.bak';
```
> ?????? **Important**: All striped files must be present, and the list must be **in the correct order**.
---
### ???? Tips to Verify:
- Run `exec msdb.dbo.rds_task_status` to monitor the restore job and check for errors.
- Ensure all `.bak` parts were uploaded **without encryption or metadata changes**.
- Confirm that your **RDS Option Group** has `SQLSERVER_BACKUP_RESTORE` enabled and applied.
Let me know your filenames and I can help construct the proper `@s3_arn_to_restore_from` value for you.
Author: user
[2025-04-01 15:53:22.160] Aborted the task because of a task failure or a concurrent RESTORE_DB request.
[2025-04-01 15:53:22.217] Task has been aborted
[2025-04-01 15:53:22.223] Error making request with Error Code Forbidden and Http Status Code Forbidden. No further error information was returned by the service.
Author: ChatGPT
Thanks Lowell ??? that error gives us a clearer path to troubleshoot. The key part is:
> **"Error Code Forbidden and Http Status Code Forbidden"**
This indicates an **IAM permission issue** when RDS tries to access the S3 bucket or files.
---
### ??? **Checklist to Resolve ???403 Forbidden??? Error:**
#### 1. ??? **S3 Bucket Policy Permissions**
Make sure your S3 bucket (`dev26db-backup`) allows access from the RDS service role. The typical policy looks like this:
```json
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowRDSBackupRestore",
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": [
"s3:GetObject",
"s3:ListBucket"
],
"Resource": [
"arn:aws:s3:::dev26db-backup",
"arn:aws:s3:::dev26db-backup/*"
]
}
]
}
```
Double-check:
- The bucket name is correct.
- The wildcard `*` for object paths is included.
- The policy is attached to the correct S3 bucket.
---
#### 2. ??? **Correct IAM Role Attached to RDS Instance**
Ensure that your RDS SQL Server instance has a role with the `AmazonS3ReadOnlyAccess` policy or a custom policy that allows `s3:GetObject` and `s3:ListBucket` for the backup bucket.
You can verify or attach the role via the AWS Console:
- Go to **RDS > Databases > [your instance] > Modify**
- Under **Manage IAM roles**, confirm the role has the correct permissions
- If missing, add an IAM role that has `s3:GetObject` access to your bucket
- Save changes and **reboot** the instance if necessary
---
#### 3. ??? **Correct ARN Paths and Permissions Match**
You must match the exact file paths. If you are trying to use:
```sql
arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_1.bak
arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_2.bak
```
Make sure these objects actually exist in the bucket, and the file names are **case-sensitive**.
Use this command (no wildcard):
```sql
exec msdb.dbo.rds_restore_database
@restore_db_name = 'GYMT_GYMENGINE',
@s3_arn_to_restore_from = 'arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_1.bak;
arn:aws:s3:::dev26db-backup/GYMTGYMENGINE_2.bak';
```
---
### ???? Optional: Verify from CLI
You can check S3 permissions by using the AWS CLI:
```bash
aws s3 ls s3://dev26db-backup/
```
---
If you want to paste the current S3 bucket policy or the RDS IAM role info, I can help validate it quickly.
Author: user
i beleive you are incorrect about wildcards. this page has the expected syntax; https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.sql-server-rds-sql-server-full-load-backup-restore.html
Author: ChatGPT
You're absolutely right to double-check, Lowell ??? and you're referring to **Amazon???s documentation for using `rds_restore_database` with striped backups**, specifically in the [AWS DMS Workshop docs](https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.sql-server-rds-sql-server-full-load-backup-restore.html).
That page **does indeed show usage of wildcards** in `@s3_arn_to_restore_from`, like:
```sql
exec msdb.dbo.rds_restore_database
@restore_db_name='MyTargetDatabase',
@s3_arn_to_restore_from='arn:aws:s3:::mybucket/mybackup*.bak'
```
---
### ??? So yes, wildcard support **does exist** for **striped backups** in **RDS SQL Server**, **but**:
Despite the documented support, it has **limitations** and can **fail with 403 Forbidden** if:
#### ???? 1. **IAM Role Does Not Have ListBucket Permissions**
Even if the object-level access (`s3:GetObject`) works, using wildcards means RDS must call `s3:ListBucket` to resolve matching objects. If your S3 bucket policy (or the IAM role attached to RDS) does **not include** this:
```json
{
"Action": "s3:ListBucket",
"Effect": "Allow",
"Resource": "arn:aws:s3:::dev26db-backup"
}
```
???then the wildcard usage will fail with `403 Forbidden`.
---
#### ???? 2. **The IAM Role is Not Properly Attached to RDS**
Even if you created the IAM role and assigned the permissions, the RDS instance must have the IAM role **explicitly attached** under **"Manage IAM roles"** in the RDS console.
Also ensure:
- The role has a trust policy for `rds.amazonaws.com`
- The role includes both `s3:ListBucket` and `s3:GetObject` for your bucket
---
### ??? Action Plan Summary:
1. **Check S3 bucket policy** ??? confirm it allows both `s3:GetObject` and `s3:ListBucket` from `rds.amazonaws.com`
2. **Check IAM role attached to RDS** ??? make sure it's attached and has S3 access to your bucket
3. **Use wildcard only if the above are correct** ??? or fall back to explicitly listing files if needed
4. **Double-check case sensitivity** ??? S3 paths are case-sensitive; `GYMTGYMENGINE*` won???t match `GymtGymEngine_1.bak` if there's a mismatch
---
Want to paste the current bucket policy or IAM role? I can help verify that it???s wildcard-friendly.