How to Restore SQL Server Database on AWS RDS from S3

1. Overview

Amazon RDS for SQL Server does not allow direct access to the underlying file system. As a result, database restores cannot be performed using standard SQL Server file paths or local disks.

Instead, Amazon provides a managed restore mechanism that pulls SQL Server backup files (.bak) directly from Amazon S3 using built-in stored procedures.

This document provides a step-by-step technical walkthrough of restoring SQL Server databases on Amazon RDS from S3, including required permissions, restore execution, task monitoring, and validation. If you need expert assistance with AWS managed services, our team can help optimize your RDS deployments and automate database operations.

---

2. Key Constraints of SQL Server on Amazon RDS

Before performing any restore, it is important to understand the constraints:

• Direct RESTORE DATABASE FROM DISK is not supported
• Access to C:\, D:\, or SQL data directories is restricted
• Only Amazon-provided stored procedures can be used
• Only one restore operation can run at a time per RDS instance
• Restore operations are asynchronous

These constraints exist to ensure platform stability and managed service guarantees.

---

3. Prerequisites

Ensure the following prerequisites are met:

• Amazon RDS SQL Server instance is running and accessible
• SQL Server Management Studio (SSMS) connectivity is available
• SQL Server backup file (.bak) exists and is valid
• Backup file is uploaded to an S3 bucket
• An IAM role is available to allow RDS to read from S3

---

4. Preparing the Backup File in S3

Upload the .bak file to Amazon S3 using a structured path.

Example structure:

s3://<bucket-name>/nonprod/<database-name>.bak

Best practices:

• Keep database name and file name aligned
• Avoid spaces or special characters
• Use environment-based prefixes (nonprod, staging, prod)
• Keep bucket region consistent with RDS region

---

5. IAM Role Configuration for RDS Restore

Amazon RDS assumes an IAM role for S3 access during restore operations. This role must include both bucket-level and object-level permissions.

5.1 Required IAM Permissions

The role must allow:

• s3:ListBucket
• s3:GetBucketLocation
• s3:GetObject

A common error occurs when bucket-level permissions are missing. Proper IAM configuration is critical—if you’re implementing cloud migration services, our consultants can help architect secure, compliant IAM policies for database operations.

5.2 Example IAM Policy

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": "arn:aws:s3:::your-bucket-name"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject"
      ],
      "Resource": "arn:aws:s3:::your-bucket-name/*"
    }
  ]
}

Attach this IAM role to the RDS instance with S3 integration enabled.

---

6. Restore Execution Using RDS Stored Procedure

Amazon RDS provides the native restore stored procedure rds_restore_database.

6.1 Restore Command

EXEC msdb.dbo.rds_restore_database
    @restore_db_name = 'DatabaseName',
    @s3_arn_to_restore_from = 'arn:aws:s3:::your-bucket-name/nonprod/DatabaseName.bak';

Key points:

• The database does not need to exist beforehand
• Database name does not need to match the backup file name, but alignment is recommended
• The restore runs asynchronously
• Only one restore task can be active at a time

---

7. Monitoring Restore Progress

Restore progress can be monitored using the RDS task tracking procedure:

EXEC msdb.dbo.rds_task_status;

7.1 Task States

Common task lifecycle:

CREATED → IN_PROGRESS → SUCCESS

Notes:

• A delay in the CREATED state is normal
• Large backups will remain in IN_PROGRESS longer
• Restore percentage updates are logged internally

For production environments, consider implementing automated monitoring with Prometheus and Grafana to track RDS performance and restore operations.

---

8. Understanding Restore Logs

During restore execution, RDS logs include:

• S3 chunk processing progress
• Page-level restore metrics
• SQL Server version upgrades
• Database conversion steps
• Final success confirmation

Example events observed during restore:

• Processing S3 chunks
• Database version upgrade steps
• Page restore completion
• Final database state transition

These logs confirm that:

• The backup is compatible
• SQL Server version upgrades are applied automatically
• The database is brought online after restore

---

9. Common Error and Resolution

Error: Unauthorized s3:GetBucketLocation

is not authorized to perform: s3:GetBucketLocation

Root cause

• IAM role allows object access (bucket/*) but not bucket-level access (bucket)

Resolution

• Add s3:GetBucketLocation permission on the bucket ARN without /*

This error typically causes restore tasks to abort immediately.

---

10. Post-Restore Validation

After restore completion, validate database status.

SELECT name, state_desc
FROM sys.databases;

Expected state:

ONLINE

---

11. Post-Restore Cleanup and Fixes

11.1 Fix Orphaned Users

Restores across environments may create orphaned SQL Server users. Report orphaned users with:

EXEC sp_change_users_login 'Report';

Fix users if required:

EXEC sp_change_users_login 'Auto_Fix', 'username';

---

11.2 Validate Cross-Database Dependencies

If applications rely on cross-database queries:

ALTER DATABASE DatabaseName SET DB_CHAINING ON;

Enable only if required by application design.

---

12. Operational Considerations

• Do not run concurrent restore operations
• Monitor available storage before restoring
• Verify SQL Server version compatibility
• Test restores in non-production environments first
• Automate restores carefully if used in DevOps automation pipelines

When implementing database restores as part of your infrastructure automation, consider using Terraform for infrastructure as code and CI/CD pipelines to ensure repeatable, version-controlled deployments.

---

13. Summary

Restoring SQL Server databases on Amazon RDS requires a controlled, permission-driven process using Amazon-managed stored procedures. Proper IAM configuration, S3 preparation, and restore monitoring are critical for success.

When implemented correctly, this approach supports repeatable database restores for enterprise cloud migration, disaster recovery, and DevOps-driven environment provisioning.

---

Need Help with AWS RDS and Database Migration?

Restoring databases on AWS RDS is just one piece of a comprehensive cloud database strategy. If you’re facing challenges with:

• Database migration planning and execution from on-premises to AWS
• AWS RDS optimization for performance, cost, and reliability
• Automated backup and restore pipelines integrated with CI/CD
• PostgreSQL or SQL Server consulting for production workloads
• Infrastructure as code using Terraform for RDS deployments

Our AWS managed services team provides expert guidance for database architecture, migration strategy, and ongoing operations. We’ve helped organizations migrate hundreds of databases to AWS with zero downtime and optimized performance.

Schedule a free 30-minute consultation to discuss your AWS database requirements, or contact our team for a tailored migration assessment.