Using native backup and restore - Amazon Relational Database Service
Backing up a databaseRestoring a databaseFinishing a restorePartially restored databasesCanceling a taskTracking the status of tasks
Services or capabilities described in AWS documentation might vary by Region. To see the differences applicable to the AWS European Sovereign Cloud Region, see the AWS European Sovereign Cloud User Guide.

Using native backup and restore

After you have enabled and configured native backup and restore, you can start using it. First, you connect to your Microsoft SQL Server database, and then you call an Amazon RDS stored procedure to do the work. For instructions on connecting to your database, see Connecting to your Microsoft SQL Server DB instance.

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-eusc:s3:::bucket_name/file_name.extension. Amazon S3 doesn't require an account number or AWS Region in ARNs.

If you also provide an optional KMS key, the format for the ARN of the key is arn:aws-eusc:kms:region:account-id:key/key-id. For more information, see Amazon resource names (ARNs) and AWS service namespaces. You must use a symmetric encryption KMS key to encrypt your backups. Amazon RDS doesn't support asymmetric KMS keys. For more information, see Creating symmetric encryption KMS keys in the AWS Key Management Service Developer Guide.

Note

Whether or not you use a KMS key, the native backup and restore tasks enable server-side Advanced Encryption Standard (AES) 256-bit encryption through SSE-S3 by default for files uploaded to S3. Passing in @enable_bucket_default_encryption=1 to the backup stored procedure uses your S3 bucket's configured default encryption key.

For instructions on how to call each stored procedure, see the following topics:

Backing up a database

To back up your database, use the rds_backup_database stored procedure.

Note

You can't back up a database during the maintenance window, or while Amazon RDS is taking a snapshot.

Usage

exec msdb.dbo.rds_backup_database
	@source_db_name='database_name',
	@s3_arn_to_backup_to='arn:aws-eusc:s3:::bucket_name/file_name.extension',
	[@kms_master_key_arn='arn:aws-eusc:kms:region:account-id:key/key-id'],	
	[@overwrite_s3_backup_file=0|1],
	[@block_size=512|1024|2048|4096|8192|16384|32768|65536],
        [@max_transfer_size=n],
        [@buffer_count=n],
	[@enable_bucket_default_encryption=0|1];

The following parameters are required:

  • @source_db_name – The name of the database to back up.

  • @s3_arn_to_backup_to – The ARN indicating the Amazon S3 bucket or access point to use for the backup, plus the name of the backup file.

    The file can have any extension, but .bak is usually used. Note that access point ARNs must be of the format arn:aws:s3:eusc-de-east-1:111122223333:access-point-name/object/key.

The following parameters are optional:

  • @kms_master_key_arn – The ARN for the symmetric encryption KMS key to use to encrypt the item.

    • You can't use the default encryption key. If you use the default key, the database won't be backed up.

    • If you don't specify a KMS key identifier, the backup file won't be encrypted. For more information, see Encrypting Amazon RDS resources.

    • When you specify a KMS key, client-side encryption is used.

    • Amazon RDS doesn't support asymmetric KMS keys. For more information, see Creating symmetric encryption KMS keys in the AWS Key Management Service Developer Guide.

  • @overwrite_s3_backup_file – A value that indicates whether to overwrite an existing backup file.

    • 0 – Doesn't overwrite an existing file. This value is the default.

      Setting @overwrite_s3_backup_file to 0 returns an error if the file already exists.

    • 1 – Overwrites an existing file that has the specified name, even if it isn't a backup file.

  • @block_size – Block size (in bytes) specifying the physical block size for backup operations. Valid values are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536

  • @max_transfer_size – Maximum transfer size denotes the upper limit of data volume (in bytes) transmitted per I/O operation during the backup process. Valid values are multiples of 65536 bytes (64 KB) up to 4194304 bytes (4 MB).

  • @buffer_count – Total number of I/O buffers to be use for the backup process.

  • @enable_bucket_default_encryption – A value that indicates whether to use the S3 buckets default encryption configuration for server-side encryption in S3.

    • 0 – Server-side encryption uses Advanced Encryption Standard (AES) 256-bit encryption through SSE-S3.

    • 1 – Server-side encryption uses your S3 bucket’s configured default encryption.

Examples

Example of full backup with client-side encryption
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:::mybucket/backup1.bak',
@kms_master_key_arn='arn:aws-eusc:kms:us-east-1:123456789012:key/AKIAIOSFODNN7EXAMPLE',
@overwrite_s3_backup_file=1,
@type='FULL';
Example of multifile backup
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:::mybucket/backup*.bak',
@number_of_files=4;
Example of multifile backup with encryption
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:::mybucket/backup*.bak',
@kms_master_key_arn='arn:aws-eusc:kms:us-east-1:123456789012:key/AKIAIOSFODNN7EXAMPLE',
@number_of_files=4;
Example of multifile backup with S3 overwrite
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:::mybucket/backup*.bak',
@overwrite_s3_backup_file=1,
@number_of_files=4;
Example of backup with block size
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:::mybucket/backup*.bak',
@block_size=512;
Example of multifile backup with @max_transfer_size and @buffer_count
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:::mybucket/backup*.bak',
@number_of_files=4,
@max_transfer_size=4194304,
@buffer_count=10;
Example of single-file backup with the @number_of_files parameter

This example generates a backup file named backup*.bak.

exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:::mybucket/backup*.bak',
@number_of_files=1;
Example of full backup with server-side encryption
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:::mybucket/backup*.bak',
@overwrite_s3_backup_file=1,
@type='FULL',
@enable_bucket_default_encryption=1;
Example of full backup using an access point
exec msdb.dbo.rds_backup_database
@source_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:eusc-de-east-1:111122223333:accesspoint/my-access-point/object/backup1.bak',
@overwrite_s3_backup_file=1,
@type='FULL';

Restoring a database

To restore your database, call the rds_restore_database stored procedure. Amazon RDS creates an initial snapshot of the database after the restore task is complete and the database is open.

Usage

exec msdb.dbo.rds_restore_database
	@restore_db_name='database_name',
	@s3_arn_to_restore_from='arn:aws-eusc:s3:::bucket_name/file_name.extension',
	@with_norecovery=0|1,
	[@keep_cdc=0|1],
	[@data_file_volume='D:|H:|I:|J:'],
	[@log_file_volume='D:|H:|I:|J:'],
	[@kms_master_key_arn='arn:aws-eusc:kms:region:account-id:key/key-id'],
        [@block_size=512|1024|2048|4096|8192|16384|32768|65536],
        [@max_transfer_size=n],
        [@buffer_count=n];

The following parameters are required:

  • @restore_db_name – The name of the database to restore. Database names are unique. You can't restore a database with the same name as an existing database.

  • @s3_arn_to_restore_from – The ARN indicating the Amazon S3 prefix and names of the backup files used to restore the database.

    • For a single-file backup, provide the entire file name.

    • For a multifile backup, provide the prefix that the files have in common, then suffix that with an asterisk (*).

    • If @s3_arn_to_restore_from is empty, the following error message is returned: S3 ARN prefix cannot be empty.

The following parameters are optional:

  • @keep_cdc – Indicates whether to retain Change Data Capture (CDC) configuration on the restored database. Set to 1 to enable KEEP_CDC, 0 to disable. The default value is 0.

  • @data_file_volume – Specifies the drive letter for database data files. The default value is D:.

  • @log_file_volume – Specifies the drive letter for database log files The default value is D:.

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

    When you specify a KMS key, client-side encryption is used.

  • @block_size – Block size (in bytes) specifying the physical block size for backup operations. Valid values are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536

  • @max_transfer_size – Maximum transfer size denotes the upper limit of data volume (in bytes) transmitted per I/O operation during the backup process. Valid values are multiples of 65536 bytes (64 KB) up to 4194304 bytes (4 MB).

  • @buffer_count – Total number of I/O buffers to be use for the backup process.

Examples

Example of single-file restore
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup1.bak';
Example of multifile restore

To avoid errors when restoring multiple files, make sure that all the backup files have the same prefix, and that no other files use that prefix.

exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup*';
Example of full database restore with RECOVERY

The following three examples perform the same task, full restore with RECOVERY.

exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup1.bak';
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup1.bak';
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup1.bak',
@with_norecovery=0;
Example of full database restore with encryption
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup1.bak',
@kms_master_key_arn='arn:aws-eusc:kms:us-east-1:123456789012:key/AKIAIOSFODNN7EXAMPLE';
Example of restore with block size
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup1.bak',
@block_size=512;
Example of multifile restore with @max_transfer_size and @buffer_count
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup*',
@max_transfer_size=4194304,
@buffer_count=10;
Example of full database restore with NORECOVERY
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_restore_from='arn:aws-eusc:s3:::mybucket/backup1.bak',
@with_norecovery=1;
Example of full database restore with RECOVERY using an access point
exec msdb.dbo.rds_restore_database
@restore_db_name='mydatabase',
@s3_arn_to_backup_to='arn:aws-eusc:s3:eusc-de-east-1:111122223333:accesspoint/my-access-point/object/backup1.bak',
@with_norecovery=0;

Finishing a database restore

If the last restore task on the database was performed using @with_norecovery=1, the database is now in the RESTORING state. Open this database for normal operation by using the rds_finish_restore stored procedure.

Usage

exec msdb.dbo.rds_finish_restore @db_name='database_name';
Note

To use this approach, the database must be in the RESTORING state without any pending restore tasks.

To finish restoring the database, use the master login. Or use the user login that most recently restored the database with NORECOVERY.

Working with partially restored databases

Dropping a partially restored database

To drop a partially restored database (left in the RESTORING state), use the rds_drop_database stored procedure.

exec msdb.dbo.rds_drop_database @db_name='database_name';
Note

You can't submit a DROP database request for a database that already has a pending restore or finish restore task.

To drop the database, use the master login. Or use the user login that most recently restored the database with NORECOVERY.

Snapshot restore and point-in-time recovery behavior for partially restored databases

Partially restored databases in the source instance (left in the RESTORING state) are dropped from the target instance during snapshot restore and point-in-time recovery.

Canceling a task

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

Note

You can't cancel a FINISH_RESTORE task.

Usage

exec msdb.dbo.rds_cancel_task @task_id=ID_number;

The following parameter is required:

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

Tracking the status of tasks

To track the status of your backup and restore tasks, 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 two minutes. Task history is retained for 36 days.

Usage

exec msdb.dbo.rds_task_status
	[@db_name='database_name'],
	[@task_id=ID_number];

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.

Examples

Example of listing the status for a specific task
exec msdb.dbo.rds_task_status @task_id=5;
Example of listing the status for a specific database and task
exec msdb.dbo.rds_task_status
@db_name='my_database',
@task_id=5;
Example of listing all tasks and their statuses on a specific database
exec msdb.dbo.rds_task_status @db_name='my_database';
Example of listing all tasks and their statuses on the current instance
exec msdb.dbo.rds_task_status;

Response

The rds_task_status stored procedure returns the following columns.

Column Description

task_id

The ID of the task.

task_type

Task type depending on the input parameters, as follows:

  • For backup tasks:

    • BACKUP_DB – Full database backup

  • For restore tasks:

    • RESTORE_DB – Full database restore with RECOVERY

    • RESTORE_DB_NORECOVERY – Full database restore with NORECOVERY

  • For tasks that finish a restore:

    • FINISH_RESTORE – Finish restore and open database

Amazon RDS creates an initial snapshot of the database after it is open on completion of the following restore tasks:

  • RESTORE_DB

  • FINISH_RESTORE

database_name

The name of the database that the task is associated with.

% complete

The progress of the task as a percent value.

duration (mins)

The amount of time spent on the task, in minutes.

lifecycle

The status of the task. The possible statuses 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. For more information about the error, see the task_info column.

  • 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 canceled, the status of the task is set to CANCELLED.

task_info

Additional information about the task.

If an error occurs while backing up or restoring a database, this column contains information about the error. For a list of possible errors, and mitigation strategies, see Troubleshooting.

last_updated

The date and time that the task status was last updated. The status is updated after every 5 percent of progress.

created_at

The date and time that the task was created.
S3_object_arn The ARN indicating the Amazon S3 prefix and the name of the file that is being backed up or restored.

overwrite_s3_backup_file

The value of the @overwrite_s3_backup_file parameter specified when calling a backup task. For more information, see Backing up a database.
KMS_master_key_arn The ARN for the KMS key used for encryption (for backup) and decryption (for restore).
filepath Not applicable to native backup and restore tasks.
overwrite_file Not applicable to native backup and restore tasks.