{"id":8204,"date":"2019-09-13T09:45:24","date_gmt":"2019-09-13T09:45:24","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/migrate-a-sql-server-database-to-an-aws-rds-instance\/"},"modified":"2026-11-03T19:29:29","modified_gmt":"2026-11-03T19:29:29","slug":"migrate-a-sql-server-database-to-an-aws-rds-instance","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/migrate-a-sql-server-database-to-an-aws-rds-instance\/","title":{"rendered":"Migrate a SQL Server database to an AWS RDS instance"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

This blog explains how to move a Microsoft\u00a9  SQL Server\u00a9  database hosted on-premises (or on Amazon\u00a9  EC2 or Azure\u00a9  to Amazon Relational Database Service (RDS). This move requires backing up your SQL database to an AWS S3 bucket and restoring the database on your AWS RDS instance from that S3 bucket.<\/p>\n

Previous versions of the Amazon RDS instance did not let you restore data from .bak<\/strong> files. As a result, users had to use Amazon Data Migration Services or use the Import and Export<\/strong> wizard to move data to and from AWS RDS. Amazon started support for Native Backup Restore in July 2016 and added the following stored procedures in MSDB database on RDS:<\/p>\n

– rds\\_backup\\_database<\/strong> \u2013 Back up a single database to an S3 bucket.
\n– rds\\_restore\\_database<\/strong> \u2013 Restore a single database from S3.
\n– rds\\_task\\_status<\/strong> \u2013 Track a running backup and restore task.
\n– rds\\_cancel\\_task<\/strong> \u2013 Cancel a running backup or restore task.<\/p>\n

\nThis blog discusses how to leverage the rds\\_restore\\_database<\/strong> procedure to restore a .bak<\/strong> file from S3 and how to use the rds\\_task\\_status <\/strong>procedure to monitor the restore progress while the AWS S3 sync command is used to upload the backup file to an AWS S3 bucket.<\/p>\n

Prerequisites<\/strong><\/p>\n

To perform a migration, you should have the following prerequisites:<\/p>\n

–    SQL Server Agent Proxy
\n–    AWS Command Line Interface (AWS CLI)
\n–    ProfileName (an AWS user with access to Amazon S3 and an S3 bucket including
\n   the aws_access_key_id<\/em> and aws_secret_access_key<\/em>.)
\n–    AWS RDS instance with an appropriate option group with the SQLSERVER_BACKUP_RESTORE o<\/em>ption mapped to it.
\n–  PowerShell installation<\/p>\n

You also should have basic knowledge about SQL Server, creating an S3 bucket, creating an AWS user, granting access to an S3 bucket, and creating an RDS instance.<\/p>\n

Solution<\/strong><\/p>\n

The following steps cover the solution in the following sections:<\/p>\n

1.    Back up the database on the local server.<\/p>\n

2.    Copy the backup file to an AWS S3 bucket.<\/p>\n

3.    Restore the SQL backup in RDS from the S3 bucket.<\/p>\n

Back up the database on the local server<\/strong><\/p>\n

You can use any method to take a backup locally. The following example uses a .bat<\/strong> script with a sqlcmd command that you can use as in any SQL agent job, in case you need to schedule the migration task:<\/p>\n

    Sqlcmd -S SourceInstanceName -U sa -P password_here -Q
\n    “Declare @DBName nvarchar(200)=’MigrationTestDB’
\n    DECLARE @BackupLocation NVARCHAR(2000) = ‘C:\\Temp\\RDSmigration\\backup\\’+@DBName+
\n    + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120) + ‘.bak’, ‘:’, ”);
\n    BACKUP DATABASE @DBName TO DISK = @BackupLocation with compression;”<\/em><\/p>\n

Copy and upload the backup file to an AWS S3 bucket<\/strong><\/p>\n

You can do this by using the AWS S3 copy<\/em> or AWS S3 sync<\/em> commands. However, the sync command is very popular and widely used in the industry, so the following example uses it.<\/p>\n

By default, the AWS sync command does not delete files. It simply copies new or modified files to the destination. You can use the following PowerShell script in an SQL agent job. You need to run both the backup and this copy step by using an SQL agent proxy account configured to run cmdExec<\/em> and the PowerShell subsystem.<\/p>\n

 $LogDate = Get-Date -Format yyyy-MM-dd
\n    $Global:LogFile = “C:\\Temp\\RDSmigration\\Logs\\$LogDate.log”<\/em><\/p>\n

    $env:Path += ‘;C:\\Program Files\\Amazon\\AWSCLI\\bin’<\/em><\/p>\n

    Set-AWSCredential -ProfileName backuptos3user
\n    aws configure set aws_access_key_id AKIAVIH6FYWVO62BZ7QA
\n    aws configure set aws_secret_access_key pATGeYmJNsJNJTnf3hgQMk8gi5ekOerB\/\/JBCkzV
\n    aws configure set region ap-south-1<\/em>
\n try
\n    {
\n       $now = (Get-Date -Format G)
\n       aws s3 sync C:\\Temp\\RDSmigration\\backup s3:\/\/ ramkrdsrestore –sse | out-file $LogFile
\n    }
\n    catch {
\n       Write-Host $_.Exception.Message -ForegroundColor Green
\n    }<\/em><\/p>\n

 Restore the SQL backup in RDS from the S3 bucket<\/strong><\/p>\n

Run the following command to restore the MigrationTestDB2019-08-15 181640.bak <\/strong>file as the MigrationTestDB<\/strong> database:<\/p>\n

    EXEC msdb.dbo.rds_restore_database
\n       @restore_db_name = ‘MigrationTestDB’,
\n       @S3_arn_to_restore_from = ‘arn:aws:s3:::ramkrdsrestore\/MigrationTestDB2019-08-15 181640.bak’<\/em><\/p>\n

\"sql<\/p>\n

Note the parameters supplied to the stored procedure. You need to specify the following parameters:<\/p>\n

–    The name of the database to restore.
\n–    The Amazon Resource Name (ARN) of the backup file. For S3 objects, the ARN
\n   follows the format shown here.<\/p>\n

 SQL Server starts a restore task and assigns it a TaskID<\/strong>. You can easily track the status of the task with the following command:<\/p>\n

  EXEC  msdb.[dbo].[rds_task_status]  @db_name =’DestinationDBName’<\/em><\/p>\n

You can use the TaskID<\/strong> to track progress instead of @db_name<\/strong>.<\/p>\n

\"sql<\/p>\n

Be aware of the following concerns:<\/p>\n

–    The bucket backup file name is case sensitive in the rds\\_restore\\_database <\/strong>procedure.
\n–    The IAM role used in the option group has access to the S3 bucket.
\n–    The S3 bucket policy is not restrictive to exclude the IAM role.
\n–    The RDS SQL instance uses the correct option group where you added the backup\/restore option. This setting is critical. Without it, restore does not begin. <\/p>\n

Limitations of SQL Server native backup and restore in Amazon RDS<\/strong><\/p>\n

Following are some limitations of SQL Server native backup and restore in Amazon RDS:<\/p>\n

–    No differential, transaction log, filegroup backup, or restore is possible,    which is not a hindrance to data recovery. Amazon RDS allows you to create scheduled instance snapshots, and those snapshots are kept accessible for a rolling 35-day period. You can restore the instance within five minutes of a specified time in the last thirty-five days.
\n–    You cannot restore KMS-encrypted backups from S3 to on-premises.
\n–    You cannot restore a database in the same RDS instance.
\n–  You cannot restore backups of Tabular Data Encryption (TDE) enabled databases.
\n–    Target RDS instances require access to the S3 bucket.
\n–    The user account that is running RDS SQL Server native backup and restore commands needs to have appropriate permissions.<\/p>\n

 Conclusion<\/strong><\/p>\n

This blog showed you how to back up a database on a cloud destination and to restore it on an AWS RDS instance. The RDS instance does not provide access to the drives or server. The key is to transfer a backup file to the AWS S3 bucket and then restore it from there.<\/p>\n

One more use case for this article is to store your SQL backups directly on AWS S3, which provides data availability, security, and performance enhancements. Amazon S3 is designed for 99.999999999% (eleven 9s) durability. This approach is cost effective when it comes to storing database backups by saving a lot of hard storage.<\/p>\n","protected":false},"excerpt":{"rendered":"

Migrate a SQL Server database to an AWS RDS instance<\/p>\n","protected":false},"author":2,"featured_media":8206,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26621","content_type":"blog","created_by":"10130","authored_on":"2024-02-19T10:45:24+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Ramkumar Singh, SQL Server Database Administrator, Rackspace Technology","seo_description":"Migrate your SQL Server database to Amazon Relational Database Service (RDS) using native backup restore.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26621-insights_image-general-architecture-technical-blog-5-insights.jpg","field_partners":"","rxt_platforms":"Amazon Web Services (AWS), Microsoft SQL","product":"","publisheddate":"2019-09-13T09:45:24","rxt_solutions":"Cloud Adoption & Migration, Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Migrate a SQL Server Database to an AWS RDS Instance | Rackspace Technology","field_topics":"","field_slug":"migrate-an-sql-server-database-to-an-aws-rds-instance","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-8204","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/8204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=8204"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/8204\/revisions"}],"predecessor-version":[{"id":9955,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/8204\/revisions\/9955"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/8206"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=8204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=8204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=8204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}