How long does it take to create a backup for my Amazon RDS for SQL Server DB instance?

Resolution

Create a backup based on how much data you want to back up

The time it takes to create a backup for your DB instance depends on the database workload. A higher database workload increases the number of incremental changes in the volume and results in a longer backup time.

In Amazon RDS for SQL Server, you can use automated backups, manual DB snapshots, and native backups to back up your DB instances.

Amazon RDS automated backups and manual DB snapshots are storage volume snapshots of your DB instance that back up the entire DB instance. The first backup of a DB instance contains the data for the full DB instance. The subsequent backups of the same DB instance are incremental and only save the data that changes after your most recent snapshot.

For native SQL backups, you can create full and differential backups of your DB instance. Store the file in Amazon Simple Storage Service (Amazon S3) buckets. You can also create backups of individual databases on your DB instance.

In some scenarios, your DB instance might require a full backup instead of an incremental backup. For example, your instance might require a full backup after a Multi-AZ failover or after you turn on Amazon RDS automated backups. It can take much longer to create a full backup.

Other factors that can increase backup time include the following:

• CPU and memory availability
• Disk I/O
• Database activity
• Size of the database

Multi-AZ failover and backup times

After a Multi-AZ failover, the first snapshot takes longer than other Amazon RDS snapshots for the following reasons:

• For SQL Server, snapshots are always taken from the Multi-AZ primary instance and are incremental.
• The Amazon EBS volumes for the Multi-AZ standby instance might be out of sync with those for the Multi-AZ primary instance.
• After a failover, the former standby becomes the new primary. If a recent snapshot from this instance isn't available, then the snapshot must back up all blocks that are out of sync.
• The backup duration depends on the number of data blocks that have changed since the last backup.

Prepare for Multi-AZ failover during your maintenance window or off-peak hours with failover exercises. Regular failover exercises ensure that both instances maintain recent snapshots, which reduces backup time after an actual failover event.

Note: Failover exercises create downtime, so plan for them based on your operational needs.

Check the progress of a native backup or restore

To check the progress of a native backup or restore, get the task ID after you perform the backup or restore statement.

Or, run the following script to identify all the completed and pending tasks for a specific database:

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

Note: Replace database_name with the name of your database.

To track the status of the task, run the following SQL statement:

exec msdb.dbo.rds_task_status @task_id=my_task

Note: Replace my_task with your Task ID.

Check the progress of a manual DB snapshot

Complete the following steps:

1. Open the Amazon Aurora and RDS console.
2. In the navigation pane, choose Snapshots.
3. Select the snapshot that you want to check the progress for, and then choose the Progress tab.

Note: You can't use the Aurora and RDS console or AWS Command Line Interface (AWS CLI) to check the progress of an Amazon RDS automated backup. Instead, you must contact AWS Support.