Migrate On-Premises SQL Server to Amazon RDS: Native Backup/Restore vs AWS DMS

Moving an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server is one of the most common — and most quietly risky — migrations a DBA runs. RDS is a managed service, which means you give up sysadmin, lose direct filesystem access, and can no longer run a plain RESTORE DATABASE FROM DISK. The good news: AWS gives you two well-trodden paths, and picking the right one for your downtime budget is most of the battle. This guide walks both — the native backup/restore via Amazon S3 route for simple lift-and-shift, and AWS DMS for near-zero-downtime cutovers — with the exact stored procedures, IAM, and option-group setup RDS requires.

The Migration at a Glance

Every SQL Server-to-RDS migration moves through the same five phases regardless of which data-movement path you pick. Keeping this end-to-end shape in mind stops the common failure of jumping straight to the restore before the landing zone or compatibility checks are done.

Why You Can't Just Restore a .bak the Old Way

On a self-managed SQL Server, migrating a database is a one-liner: copy the .bak to the new server's disk and RESTORE DATABASE. Amazon RDS removes that option on purpose. As a managed service, RDS does not give you the host operating system, does not grant the sysadmin server role, and does not let you place files on the instance's disk. Instead, AWS exposes a controlled native backup and restore feature that moves backup files through Amazon S3 and runs the actual restore on your behalf via a set of msdb stored procedures.

That single architectural fact drives everything else in this guide: you will stage backups in S3, grant RDS an IAM role to read that bucket, enable the feature through an option group, and orchestrate the restore through stored procedures rather than T-SQL RESTORE statements.

Choosing Your Migration Path

The decision comes down to one question: how much downtime can you tolerate?

Prerequisites: What to Validate Before You Touch Anything

Both paths share the same planning checklist. Skipping these is how migrations slip from a weekend to a month.

• Version & edition compatibility. Confirm your source SQL Server version and edition are supported as an RDS target. Map Enterprise-only features (e.g. certain partitioning, TDE specifics, Resource Governor) against what RDS exposes.
• Feature support. RDS does not support every on-prem feature. Audit for FILESTREAM, certain CLR assemblies, cross-database ownership chaining, distributed transactions, Service Broker across instances, and linked servers — each may need rework.
• Sizing. Right-size the RDS instance class (vCPU/RAM) and storage (gp3 vs io2 IOPS) against your current workload, not your current hardware. Over-provisioned on-prem boxes don't translate one-to-one.
• Network. VPC, subnets, and security groups that allow your migration host (and DMS replication instance, if used) to reach both source and target on TCP 1433.
• What RDS won't bring over automatically. Server-level logins, SQL Agent jobs, linked servers, server-level triggers, and credentials live in master/msdb — script these out and reapply them on the target.

Configure the Target: VPC, RDS Instance, Option Group, IAM

Before either migration path, stand up the landing zone. The order matters because the native restore feature depends on the option group and IAM role existing first. Here is how the pieces wire together inside your AWS account:

1. Network and instance

Create (or reuse) a VPC with private subnets across at least two Availability Zones, a DB subnet group, and a security group that allows inbound TCP 1433 from your migration host and application tier. Then launch the RDS for SQL Server instance with the engine version and license model (License Included or BYOL) you validated.

2. Option group with native backup/restore

The native backup/restore feature is delivered through an RDS option group. Create one for your engine version and add the SQLSERVER_BACKUP_RESTORE option, pointing it at the IAM role you'll create next, then attach the option group to the instance.

3. S3 bucket and IAM role

Create an S3 bucket in the same Region to stage backup files, then an IAM role that RDS can assume to read from (and write to, for export) that bucket. The trust policy allows the RDS service to assume the role; the permission policy grants object access on the bucket.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket", "s3:GetBucketLocation"],
      "Resource": "arn:aws:s3:::my-sqlserver-migration"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:PutObject", "s3:ListMultipartUploadParts", "s3:AbortMultipartUpload"],
      "Resource": "arn:aws:s3:::my-sqlserver-migration/*"
    }
  ]
}

Option 1: Native Backup and Restore via Amazon S3

This is the simplest path and the right default when you can afford a maintenance window. The flow is: back up on-prem → upload to S3 → restore on RDS via stored procedure → verify.

Step 1 — Back up the source database

Take a full backup on the on-premises instance. Use compression to shrink the upload and CHECKSUM to catch corruption early.

BACKUP DATABASE [SalesDB]
TO DISK = N'D:\backups\SalesDB_full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

Step 2 — Upload the backup to S3

aws s3 cp D:\backups\SalesDB_full.bak \
  s3://my-sqlserver-migration/SalesDB_full.bak \
  --region us-east-1

Step 3 — Restore into RDS with the stored procedure

Connect to the RDS instance as the master user and call rds_restore_database. There is no RESTORE DATABASE here — the procedure queues an asynchronous task that RDS executes.

EXEC msdb.dbo.rds_restore_database
  @restore_db_name   = 'SalesDB',
  @s3_arn_to_restore_from = 'arn:aws:s3:::my-sqlserver-migration/SalesDB_full.bak';

Step 4 — Track the restore task

The call returns a task_id immediately; the restore runs in the background. Poll its status until it reports SUCCESS.

-- All recent tasks:
EXEC msdb.dbo.rds_task_status @db_name = 'SalesDB';

-- Or a specific task:
EXEC msdb.dbo.rds_task_status @task_id = 5;

Watch the lifecycle column move through CREATED → IN_PROGRESS → SUCCESS. The % complete and task_info columns surface progress and any error detail.

Step 5 — Reduce downtime with a differential or log tail

To shrink the window, take the full backup early, restore it to RDS while the source keeps running, then at cutover take a final differential (or log) backup, upload it, and restore just that delta. This turns a multi-hour full-restore window into a short final-delta window.

Option 2: AWS DMS for Near-Zero-Downtime Migration

When the database is large or the system runs 24×7, AWS Database Migration Service (DMS) is the answer. DMS performs an initial full load of existing data and then uses change data capture (CDC) to replicate ongoing changes, keeping the RDS target continuously in sync with the live source until you flip the application over.

DMS setup at a glance

1. Prepare the source for CDC. The source database must use the FULL recovery model and have regular transaction log backups running, so DMS can read ongoing changes from the log.
2. Create a replication instance in the same VPC, sized for your change volume.
3. Create source and target endpoints (on-prem SQL Server and the RDS instance) and run Test connection on both.
4. Create a migration task with migration type Migrate existing data and replicate ongoing changes, plus table mappings selecting the schemas to move.
5. Monitor via CloudWatch and the task's table statistics; watch CDCLatencySource and CDCLatencyTarget approach zero before cutover.

Cutover: Switching the Application Over

Cutover is where preparation pays off. A clean cutover follows the same shape regardless of path — and the order is what keeps the downtime window short and the rollback option open:

1. Quiesce writes on the source (stop the application or set the source database read-only).
2. Apply the final delta — restore the final differential/log backup (Option 1) or wait for DMS CDC latency to hit zero (Option 2).
3. Reconcile server-level objects — confirm logins, jobs, and linked servers exist on the target; fix orphaned users.
4. Validate — run row counts, checksums on critical tables, and a smoke test of the application against RDS.
5. Repoint the application connection string to the RDS endpoint and re-enable writes.
6. Keep the source available (read-only) for a rollback window before decommissioning.

Fix orphaned users after restore

-- Find users whose login no longer maps:
SELECT dp.name AS orphaned_user
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S','U') AND sp.sid IS NULL AND dp.authentication_type_desc = 'INSTANCE';

-- Remap to an existing login on the target:
ALTER USER [app_user] WITH LOGIN = [app_user];

Post-Migration: Make It Production-Ready

Restoring data is not the finish line. Before you call the migration done:

• Enable Multi-AZ for high availability if it wasn't created that way — RDS handles the synchronous standby and failover.
• Configure automated backups and a retention period; set the backup and maintenance windows off-peak.
• Re-establish maintenance. RDS has no SQL Agent sysadmin, so adapt your index/stats maintenance accordingly — our Ola Hallengren maintenance setup guide covers running the standard maintenance solution on RDS.
• Update statistics and rebuild indexes after the bulk load so the optimizer has fresh data.
• Baseline performance with Performance Insights and wait statistics — see the SQL Server wait stats diagnostic playbook to confirm the new instance is sized correctly under real load.
• Decommission the DMS replication instance, the S3 staging objects, and the on-prem source only after the rollback window closes.

Frequently Asked Questions

Can I restore a .bak file directly to Amazon RDS for SQL Server?

Not with a normal T-SQL RESTORE DATABASE statement — RDS does not grant filesystem or sysadmin access. You upload the .bak to an S3 bucket and restore it using the msdb.dbo.rds_restore_database stored procedure, after enabling the SQLSERVER_BACKUP_RESTORE option and granting RDS an IAM role for the bucket.

What's the largest database I can migrate to RDS for SQL Server?

A single database is limited to 16 TB. Larger databases must be split, have cold data archived, or be hosted on self-managed SQL Server on Amazon EC2 instead.

Should I use native backup/restore or AWS DMS?

Use native backup/restore when you can take a maintenance window and want a simple, exact copy. Use AWS DMS (full load + CDC) when you need near-zero downtime, the database is large, or the system runs 24×7 and cannot go offline for hours.

Does the migration bring over my logins and SQL Agent jobs?

No. Both paths migrate the user database contents but not server-level objects. Script out logins, SQL Agent jobs, alerts, operators, and linked servers from the source and reapply them on the RDS target, then fix any orphaned database users with ALTER USER ... WITH LOGIN.

How do I check the status of an RDS restore?

The rds_restore_database call returns a task_id and runs asynchronously. Poll EXEC msdb.dbo.rds_task_status @db_name = 'YourDB'; and watch the lifecycle column move from CREATED to IN_PROGRESS to SUCCESS.

Migrating SQL Server to AWS with JusDB

A migration pattern tells you the happy path; production migrations are where the edge cases live — unsupported features that surface at restore time, CDC latency that won't converge, orphaned logins discovered after cutover, and the rollback plan you hope you never need. JusDB's database team runs SQL Server-to-RDS migrations end to end: compatibility assessment, landing-zone setup, native backup/restore and DMS pipelines, cutover orchestration with minimal downtime, and post-migration tuning and 24×7 support across RDS, Aurora, and self-managed SQL Server on EC2.

If you're planning a move off on-premises SQL Server — or want a second set of eyes on a cutover plan — talk to the JusDB team. For the pipeline internals behind Option 2, read the AWS DMS performance tuning guide, and to validate the target instance under load, see the SQL Server wait stats diagnostic playbook.