Restore the data of an ApsaraDB RDS for SQL Server instance - ApsaraDB RDS

If a backup file is created for an ApsaraDB RDS for SQL Server instance, you can restore the backup data to an existing RDS instance or a new RDS instance. This is suitable for the analysis of historical data and the restoration of the data on which unintended operations are performed.

Note

If you want to restore all data of an RDS instance to another RDS instance in the same region, you can refer to this topic. If you want to restore data across regions or restore the backup files of an RDS instance to a self-managed database, you can refer to Methods to restore an ApsaraDB RDS for SQL Server instance.

Limits

If you enable the feature to archive data to an Object Storage Service (OSS) bucket, you can restore only databases whose data is included in the backup sets of your RDS instance. The databases cannot be cold storage databases. If a database is converted into a cold storage database, the database cannot be restored.
If your RDS instance is a serverless instance, you can restore the backup data of your RDS instance only to a new serverless RDS instance instead of an existing RDS instance.
The restoration methods described in this topic are not supported by RDS instances that run SQL Server 2008 R2 with local disks. You can use a temporary RDS instance to restore the data of RDS instances that run SQL Server 2008 R2 with local disks.

Restore the data to an existing RDS instance

You can restore the data of RDS Instance A to an existing RDS instance, including the RDS Instance A, from a backup set or to a specific point in time. During this process, you can restore some or all of the databases that are created on your RDS instance.

Restoration rules

Item	Description
Database engine version	The database engine version of the existing RDS instance must be later than or equal to the database engine version of the original RDS instance.
RDS edition	The RDS edition of the existing RDS instance must be higher than or equal to the RDS edition of the original RDS instance. The following RDS editions are listed in descending order: RDS Cluster Edition, RDS High-availability Edition, and RDS Basic Edition.
Instance type	The RDS instances can belong to the same instance family or different instance families. The instance families include the general-purpose instance family and the dedicated instance family.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Restore.
In the dialog box that appears, select Restore to Existing Instance and click OK.

Configure the following parameters and click OK.

Parameter	Description
Restore Method	By Backup Set: allows you to restore data from a backup set. By Time: allows you to restore the data to a point in time within the specified log retention period. The system restores the data to a specific point in time based on the most recent full backup file and incremental backup files. You cannot restore data from a specified incremental backup file. You can query and modify the log retention period based on your business requirements.
Restore Time	This parameter is available only when you set the Restore Method parameter to By Time. You must select the point in time to which you want to restore the data.
Backup Set	This parameter is available only when you set the Restore Method parameter to By Backup Set. You must select the backup set from which you want to restore the data.
More Backup Sets	If you cannot find the required backup set from the Backup Set drop-down list, you can select the check box. Then, the system displays more backup sets for you to query.
Destination Instance Name	Select the destination RDS instance to which you want to restore the data. You can restore the data to an RDS instance that runs a later SQL Server version. By default, the system displays all the RDS instances that are created within your Alibaba Cloud account and reside in the selected region. The displayed RDS instances include the original RDS instance whose data you want to restore. Note If you use a snapshot backup file, you can restore the data only to an RDS instance on which the snapshot backup feature is enabled. If the original RDS instance belongs to the shared instance family, you cannot restore the data of the instance to a general-purpose or dedicated RDS instance. Similarly, if the original RDS instance belongs to the general-purpose or dedicated instance family, you cannot restore the data of the instance to a shared RDS instance. If a large number of RDS instances are displayed, you can enter a keyword in the Destination Instance Name field to search for the required destination RDS instance.
Databases to Restore	Select specific or all databases that you want to restore. By default, the system displays all the databases that are created on the original RDS instance. Specify the names that you want to use for the selected databases on the destination RDS instance. By default, the original names of the selected databases are retained. Take note of the following items: The names of the selected databases on the original RDS instance cannot be the same as those of the existing databases on the destination RDS instance. If selected databases on the original RDS instance have the same names as existing databases on the destination RDS instance, an error is reported for the restoration task. You must change the names that you want to use for the selected databases on the destination RDS instance to unique names. If the names of the selected databases on the original RDS instance and the destination RDS instance are different, the system creates databases, and the existing data of the original RDS instance is not overwritten. The name of the selected database on the destination RDS instance can contain letters, digits, underscores (_), and hyphens (-).

View the progress of the restoration task.
The system creates a restoration task. You can click the icon in the upper-right corner. On the Tasks page, query the task whose Task Type is Clone Instance to view the progress of the restoration task.

Restore data to a new RDS instance

You can restore the data of RDS Instance A to a new RDS instance, including the RDS Instance A, from a backup set or to a specific point in time. During this process, you can restore some or all of the databases that are created on your RDS instance. For more information about the time that is required to restore an RDS instance to a new RDS instance, see FAQ.

Billing rules

You are charged for the new RDS instance. You can view the price of the new RDS instance when you create the instance.

Note

If you no longer require the original RDS instance after the restoration, we recommend that you release or unsubscribe from the instance at the earliest opportunity.
You are immediately charged for the new RDS instance after the instance is created.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Restore.
In the Select Restore Method dialog box, select Restore to New Instance and click OK.

On the Restore Instance page, configure the following parameters.

Parameter	Description
Billing Method	Subscription: A subscription RDS instance is an instance for which you pay an upfront fee. For long-term use, the subscription billing method is more cost-effective than the pay-as-you-go billing method. You are offered lower prices for longer subscription periods. Pay-as-you-go: A pay-as-you-go RDS instance is billed per hour based on your actual resource usage. We recommend that you select the pay-as-you-go billing method for short-term use. If you no longer require a pay-as-you-go RDS instance, you can release the RDS instance to reduce costs.
Restoration Mode	By Backup Set: allows you to restore data from a backup set. By Time: allows you to restore the data to a point in time within the specified log retention period. The system restores the data to a specific point in to based on the most recent full backup file and incremental backup files. You cannot restore data from a specified incremental backup file. You can query and modify the log retention period based on your business requirements.
Database	You can restore some or all of the databases. If you select Some, you must manually enter the names of the databases that you want to restore and separate the database names with commas (,). Note If you enable the snapshot backup feature for the RDS instance, you can select only All.
Edition	The available RDS editions vary based on the region and database engine version that you select.
Storage Type	You can select ESSD or general ESSD. For more information, see Storage types.
Zone of Primary Node	The zone of the primary RDS instance.
Deployment Method	Multi-zone Deployment: The primary RDS instance and the secondary RDS instance reside in different zones to provide zone-disaster recovery. This is the recommended deployment method. Single-zone Deployment: The primary RDS instance and the secondary RDS instance reside in the same zone. Note All zones in a region provide the same services. If the RDS instance resides in the same zone as the ECS instance that you want to connect, these instances can deliver optimal performance. If the RDS instance and the ECS instance reside in different zones in the same region, the performance of these instances slightly decreases. If you set the Edition parameter to Basic Edition, only Single-zone Deployment is supported. If Sold Out appears in the upper-right corner of a zone name, this zone does not have sufficient resources. In this case, you must switch to another zone.
Zone of Secondary Node	If you set the Deployment Method parameter to Multi-zone Deployment, you must select the zone in which the secondary RDS instance resides.
Instance Type	The available instance types vary based on the region and database engine version that you select.
Storage Capacity	The storage capacity of the new RDS instance must be greater than or equal to that of the original RDS instance. You can view the storage capacity of the original RDS instance on the Basic Information page of the instance details page. The storage capacity of the read-only RDS instance. The storage capacity is used to store data files, system files, archived log files, and transaction files.

Click Next: Instance Configuration and configure the following parameters.

Parameter	Description
Network Type	Only VPC is supported. You can create a VPC and vSwitch based on your business requirements. Note Make sure that the RDS instance and the ECS instance to be connected reside in the same VPC. Otherwise, they cannot communicate over an internal network.
Resource Group	The name of the resource group to which the RDS instance belongs. You can create a resource group based on your business requirements.

Click Next: Confirm Order.
Confirm the settings in the Parameter Configuration section, configure the Quantity and Subscription Duration parameters, read and select Terms of Service, click Pay Now, and then complete the payment. You must configure the Subscription Duration parameter only when the new RDS instance uses the subscription billing method.
You can go to the Instances page and find the created RDS instance based on the creation time. It requires 1 minutes to 10 minutes to create an RDS instance. You can refresh the page to check whether the RDS instance is created.
Connect to the RDS instance to view the restored databases and tables.

Related operations

You can call the RecoveryDBInstance operation to restore the data of an RDS instance.

FAQ

How much time is required to restore data to a new RDS instance?

Estimated time

The following table describes the estimated time required to restore data to a new RDS instance. The speeds of data backup and restoration are estimated based on the size of uncompressed data.

Note

Backup compression is not supported for RDS instances that run SQL Server Web. This may slow down the speeds of data backup and restoration to less than 100 GB per hour.

Operation	Required	Estimated time	Description
Create and configure an RDS instance	Yes	10 minutes to 15 minutes	The time required varies based on the product type and instance type of the new RDS instance that is used for data restoration.
Back up full data	No	200 GB per hour	If no full backups are performed on the RDS instance within 36 hours, a full backup is performed during the restoration process to balance the time required for data restoration from incremental transaction log backups and full backups. We recommend that you manually perform a full backup before data restoration or initiate a restoration task within 36 hours after the system automatically completes the full backup. This reduces the total amount of time required for restoration. The backup speed may vary based on the region and time period. To obtain more accurate information about backup and restoration performance, refer to the data volume and time for the last full backup.
Restore a full backup on the destination RDS instance	Yes	200 GB per hour	None.
Back up incremental transaction logs on the source RDS instance	Yes	200 GB per hour	Before and after an incremental log backup, a time loss of 2 minutes may occur. The time loss may be caused by backup preparation, closure, and resource allocation.
Apply an incremental transaction log backup file to the destination RDS instance	Yes	200 GB per hour	Before and after an incremental log backup, a time loss of 2 minutes may occur. The time loss may be caused by backup consistency verification.
Restore a database	Yes	Within 2 minutes	Resource consumption: Applying an incremental transaction log backup file is a resource-intensive operation. If a large number of transaction logs are generated for an RDS instance that has small specifications, such as 2 CPU cores and 4 GB of memory, the data restoration speeds decreases. Accelerated Database Recovery option: The Accelerated Database Recovery option is supported for RDS instances that run SQL Server 2019 or later. This reduces the time for database restoration. You can evaluate whether to enable the option based on official Microsoft documentation.

Example

Test instance: The RDS instance has 4 CPU cores and 8 GB of memory, and the volume of data on the RDS instance is 600 GB.

Time required to create and configure an RDS instance: approximately 12 minutes.
Time required to back up full data: approximately 3 hours. The time required is calculated by using the following calculation: Time required = 600 GB/200 GB per hour = 3 hours
Time required to restore a full backup on the destination RDS instance: approximately 3 hours. The time required is calculated by using the following calculation: Time required = 600 GB/200 GB per hour = 3 hours
Time required to back up incremental transaction logs on the source RDS instance: approximately 5 minutes. The time required is calculated by using the following calculation: Time required = 10 GB/200 GB per hour + Time loss of 2 minutes = 5 minutes
Time required to apply an incremental transaction log backup file to the destination RDS instance: approximately 5 minutes. The time required is calculated by using the following calculation: Time required = 10 GB/200 GB per hour + Time loss of 2 minutes = 5 minutes
Time required to restore a database: within 2 minutes

In this example, if no full backups are performed on the RDS instance within 36 hours, the total duration is estimated to be approximately 6 hours and 24 minutes. If a full backup is performed on the RDS instance within 36 hours, the duration is estimated to be approximately 3 hours and 24 minutes.

Restoration suggestions

Maintenance window: We recommend that you perform restoration operations during off-peak hours to minimize the impacts on your workloads.
Long-running transactions: During the restoration process, we recommend that you do not perform long-running transactions, such as creating or rebuilding indexes and archiving data. This helps prevents the time that is required to restore a database from being prolonged.