I want to perform a native backup of my Amazon RDS SQL Server DB instance so that I can store the backup file in Amazon S3, or use the backup file to restore an RDS instance later.
Amazon RDS supports native backup and restore for Microsoft SQL Server databases. You can create a full backup of your on-premises database, store the file in S3, and restore the backup file to an existing Amazon RDS DB instance running SQL Server. You can also restore this backup file to an on-premises server or a different Amazon RDS DB instance running SQL Server.
To set up a native backup of the SQL Server database, use the following services:
- 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 the DB instance
Note: The S3 bucket should be created in the same region as your RDS DB instance.
- Open the Amazon RDS console, and choose Option Groups in the navigation pane. Then choose Create Group and enter the name, description, engine, and engine version of your server. Then choose Create.
- Select the Option Group you just created, and choose Add Option. Choose “SQLSERVER_BACKUP_RESTORE”. We recommend creating a new IAM role and choosing Add Option, so that your IAM role has the required privileges. Choose your S3 bucket, or create a new S3 bucket. Then choose Apply Immediately and Add Option.
- Associate the option group with the RDS instance by choosing Instances in the navigation pane and choosing the instance you’d like to back up. For Instance Actions, choose Modify.
- Under Database Options, choose the Option Group that you created, and choose Apply Immediately and Continue. Review the information, and then choose Modify DB Instance. This Option Group modification has no downtime because instance reboot is not required.
- When the status has changed from modifying to available, connect to the RDS instance through SQL Server Management Studio and choose New Query (or press Ctrl-N). Enter one of the following SQL statements to initiate the backup of the desired database:
Initiate backup for unencrypted databases
Note: Replace database_name, bucket_name, file_name_and_extension, region, account-id, and key-id stated on examples accordingly with your real case scenario. The backup file is generated in the S3 bucket, which can be used later to restore to a new RDS DB instance. When the task is created, it will output some information and create a task to perform the operation.
When the lifecycle of the task is SUCCESS, the task is complete. You can then open the Amazon S3 console, choose the bucket in which you created the backup, and view the backup file. You can download this file or use the file to restore a new RDS instance.
Use one of the following SQL statements to restore from the backup file available in the S3 bucket:
You can get the Task ID after you’ve executed the backup/restore statement, or you can use the following script to identify all completed and pending tasks for a particular database:
exec msdb.dbo.rds_task_status @db_name=’database_name‘
If you’d like to track the status of the job later, use this SQL statement:
exec msdb..rds_task_status @task_id= 5