Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Importing and Exporting SQL Server Databases

Amazon RDS supports native backup and restore for Microsoft SQL Server databases using full backup files (.bak files). You can import and export SQL Server databases in a single, easily-portable file. You can create a full backup of your on-premises database, store it on Amazon Simple Storage Service (Amazon S3), and then restore the backup file onto an existing Amazon RDS DB instance running SQL Server. You can backup an Amazon RDS SQL Server database, store it on Amazon S3, and then restore the backup file onto an on-premises server, or a different Amazon RDS DB instance running SQL Server.

The following diagram shows the supported scenarios.

Native Backup and Restore Architecture

Using .bak files to backup and restore databases is heavily optimized, therefore it is usually the fastest way to backup and restore databases. There are many additional advantages to using native backup and restore. You can:

  • Easily migrate databases to Amazon RDS.

  • Easily move databases between Amazon RDS SQL Server DB instances.

  • Easily import and export data.

  • Easily migrate schemas, stored procedures, triggers and other database code.

  • Easily change your storage type or storage capacity.

  • Backup and restore single databases, instead of entire DB instances.

  • Create copies of databases for testing, training, and demonstrations.

  • Store and transfer backup files into and out of Amazon RDS through Amazon S3, giving you an added layer of protection for disaster recovery.

Native backup and restore is available in all regions except AWS GovCloud (US), and for both Single-AZ and Multi-AZ DB instances. Native backup and restore is available for all editions of Microsoft SQL Server supported on Amazon RDS, and both the License Included and the Bring Your Own License models.

The following are some limitations to using native backup and restore:

  • Native backup and restore for SQL Server is not supported on the db.t1.micro DB instance class. For more information about instance classes, see Specifications for All Available DB Instance Classes.

  • You can't restore a backup file to the same DB instance that was used to create the backup file. Instead, restore the backup file to a new DB instance. Renaming the database is not a workaround for this limitation.

  • You can't restore the same backup file to the same DB instance multiple times. Renaming the database is not a workaround for this limitation.

  • You can't back up databases larger than 1 TB.

  • You can't restore databases larger than 4 TB.

  • You can't back up a database during the maintenance window, or any time Amazon RDS is in the process of taking a snapshot of the database.

Native backup and restore should be the preferred way to migrate your database to Amazon RDS if your database can be offline while the backup file is created, copied and restored. If your on-premises database can't be offline, you should use the AWS Database Migration Service to migrate your database to Amazon RDS. For more information, see What Is AWS Database Migration Service?

Native backup and restore is not intended to replace the data recovery capabilities of the cross-region snapshot copy feature. Snapshot copy is the recommended way to copy your database snapshot to another region for cross-region disaster recovery in Amazon RDS. For more information, see Copying a DB Snapshot.

Setting Up for Native Backup and Restore

There are 3 components required to set up for native backup and restore:

  • An Amazon S3 bucket to store your backup files.

  • An AWS Identity and Access Management (IAM) role to access the bucket.

  • The SQLSERVER_BACKUP_RESTORE option added to an option group on your DB instance.

If you already have an Amazon S3 bucket, you can use that. If you don't have an Amazon S3 bucket, you can create a new one manually. Alternatively, you can choose to have a new bucket created for you when you add the SQLSERVER_BACKUP_RESTORE option by using the AWS Management Console. If you want to create a new bucket manually, see Creating a Bucket .

If you already have an IAM role, you can use that. If you don't have an IAM role, you can create a new one manually. Alternatively, you can choose to have a new IAM role created for you when you add the SQLSERVER_BACKUP_RESTORE option by using the AWS Management Console. If you want to create a new IAM role manually, or attach trust and permissions policies to an existing IAM role, use the information in the next section.

To enable native backup and restore on your DB instance, you add the SQLSERVER_BACKUP_RESTORE option to an option group on your DB instance. For more information and instructions, see Microsoft SQL Server Native Backup and Restore Support.

Manually Creating an IAM Role for Native Backup and Restore

If you want to manually create a new IAM role to use with native backup and restore, you create a role to delegate permissions from the Amazon RDS service, to your Amazon S3 bucket. When you create an IAM role you attach trust and permissions policies. For the native backup and restore feature, use trust and permissions policies similar to the examples below. For more information about creating the role, see Creating a Role to Delegate Permissions to an AWS Service .

The trust and permissions policies require that you provide an Amazon Resource Name (ARN). For more information about ARN formatting, see Amazon Resource Names (ARNs) and AWS Service Namespaces .

Example Trust Policy for Native Backup and Restore

{
    "Version": "2012-10-17",
    "Statement":
    [{
        "Effect": "Allow",
        "Principal": {"Service":  "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
    }]
}

Example Permission Policy for Native Backup and Restore Without Encryption Support

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

Example Permission Policy for Native Backup and Restore With Encryption Support

If you want to encrypt your backup files, include an encryption key in your permissions policy. For more information about encryption keys, see Getting Started in the AWS Key Management Service (AWS KMS) documentation.

{
    "Version": "2012-10-17",
    "Statement":
    [
        {
        "Effect": "Allow",
        "Action":
            [
                "kms:DescribeKey",
                "kms:GenerateDataKey",
                "kms:Encrypt",
                "kms:Decrypt"
            ],
        "Resource": "arn:aws:kms:region:account-id:key/key-id"
        },
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
        "Resource": "arn:aws:s3:::bucket_name"
        },
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
        "Resource": "arn:aws:s3:::bucket_name/*"
        }
    ]
}

Using the Native Backup and Restore Feature

After you have enabled and configured the native backup and restore feature, you can start using it. To use the feature, you connect to your Microsoft SQL Server database, and then call an Amazon RDS stored procedure to do the work. For instructions on connecting to your database, see Connecting to a DB Instance Running the Microsoft SQL Server Database Engine.

Some of the stored procedures require that you provide an Amazon Resource Name (ARN) to your Amazon S3 bucket and file. The format for your ARN is arn:aws:s3:::bucket_name/file_name. Amazon S3 does not require an account number or region in ARNs. If you also provide an optional AWS KMS encryption key, the format your ARN is arn:aws:kms:region:account-id:key/key-id. For more information, see Amazon Resource Names (ARNs) and AWS Service Namespaces .

There are stored procedures for backing up your database, restoring your database, cancelling tasks that are in progress, and tracking the status of the backup and restore tasks. For instructions on how to call each stored procedure, see the following subsections:

Backing Up a Database

To backup your database, you call the rds_backup_database stored procedure.

Note

You can't back up a database during the maintenance window, or any time Amazon RDS is in the process of taking a snapshot of the database.

The following parameters are required:

  • @source_db_name – The name of the database to create a backup of.

  • @s3_arn_to_backup_to – The Amazon S3 bucket to save the backup file in, and the name of the file. The file can have the extension .bak, or any extension you want.

The following parameters are optional:

  • @kms_master_key_arn – If you want to encrypt the backup file, the key to use to encrypt the file. For more information about encrytion keys, see Getting Started in the AWS Key Management Service (AWS KMS) documentation.

  • @overwrite_S3_backup_file – Whether or not to overwrite the backup file if it already exists in the Amazon S3 bucket. Specify 1 to overwrite the existing file. This overwrites any file in the bucket with the specified name, whether it is a backup file or another type of file. Specify 0 to not overwrite the existing file, and return an error instead if the file already exists. The default is 0.

Example Without Encryption

exec msdb.dbo.rds_backup_database 
        @source_db_name='database_name', 
        @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
        @overwrite_S3_backup_file=1;

Example With Encryption

exec msdb.dbo.rds_backup_database 
        @source_db_name='database_name',
        @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
        @kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id',
        @overwrite_S3_backup_file=1;

Restoring a Database

To restore your database, you call the rds_restore_database stored procedure.

The following parameters are required:

  • @restore_db_name – The name of the database to restore.

  • @s3_arn_to_restore_from – The Amazon S3 bucket that contains the backup file, and the name of the file.

The following parameters are optional:

  • @kms_master_key_arn – If you encrypted the backup file, the key to use to decrypt the file.

Example Without Encryption

exec msdb.dbo.rds_restore_database 
        @restore_db_name='database_name', 
        @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';

Example With Encryption

exec msdb.dbo.rds_restore_database 
        @restore_db_name='database_name', 
        @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension',
        @kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id';

Cancelling a Task

To cancel a backup or restore task, you call the rds_cancel_task stored procedure.

The following parameters are optional:

  • @db_name – The name of the database to cancel the task for.

  • @task_id – The id of the task to cancel. You can get the task id by calling rds_task_status.

Example

exec msdb.dbo.rds_cancel_task @task_id=1234;

Tracking the Status of Tasks

To track the status of your backup and restore tasks, you call the rds_task_status stored procedure. If you don't provide any parameters, the stored procedure returns the status of all tasks. The status for tasks is updated approximately every 2 minutes.

The following parameters are optional:

  • @db_name – The name of the database to show the task status for.

  • @task_id – The id of the task to show the task status for.

Example

exec msdb.dbo.rds_task_status @db_name='database_name'

The possible statuses for a task are the following:

  • CREATED – As soon as you call rds_backup_database or rds_restore_database, a task is created and the status is set to CREATED.

  • IN_PROGRESS – After a backup or restore task starts, the status is set to IN_PROGRESS. It can take up to 5 minutes for the status to change from CREATED to IN_PROGRESS.

  • SUCCESS – After a backup or restore task completes, the status is set to SUCCESS.

  • ERROR – If a backup or restore task fails, the status is set to ERROR. Examples include trying to backup during the maintenance window, or trying to overwrite an existing backup file with @overwrite_S3_backup_file set to 0.

  • CANCEL_REQUESTED – As soon as you call rds_cancel_task, the status of the task is set to CANCEL_REQUESTED.

  • CANCELLED – After a task is successfully cancelled, the status of the task is set to CANCELLED.

Migrating to Amazon RDS by Using Native Backup and Restore

To migrate your database from your corporate data center to Amazon RDS, you follow the procedures in this topic. However, you can perform the following steps to prepare:

  1. Create an Amazon S3 bucket. For more information, see Creating a Bucket .

  2. Upload your database backup file to your Amazon S3 bucket. For more information, see Uploading Objects into Amazon S3 .