All Products
Search
Document Center

Restore an on-premises SQL Server database to ApsaraDB for SQL Server

Last Updated: Jul 24, 2020

Database Backup (DBS) allows you to back up an on-premises SQL Server database to Alibaba Cloud. You can also use DBS to restore data from an on-premises SQL Server database to an ApsaraDB for SQL Server database. This realizes data migration from independent software to a software as a service (SaaS) platform. This topic describes how to restore data from an on-premises SQL Server database to an ApsaraDB for SQL Server database.

Prerequisites

Background information

DBS allows you to restore data from an on-premises SQL Server database to an ApsaraDB for SQL Server instance, an on-premises database, or a user-created database that is hosted on an Elastic Compute Service (ECS) instance.
We recommend that you restore data to an ApsaraDB for SQL Server database for better performance in restoration speed, convenience, and security. The following table describes the differences among the three types of destination databases.

Destination database location Description
ApsaraDB for SQL Server instance
  1. You do not need to build an environment for the destination database. You can use DBS to create an ApsaraDB for SQL Server instance that the source database is restored to. The default version of the instance to be created is the earliest Enterprise Edition of ApsaraDB for SQL Server that is compatible with the source database version. The earliest version of ApsaraDB for SQL Server that supports database restoration is ApsaraDB for SQL Server 2012 Enterprise Edition.
  2. Alibaba Cloud VPC offers a network environment where you can restore databases in a quick manner.
User-created database on an ECS instance
  1. You must build an environment for the destination database.
  2. Alibaba Cloud VPC offers a network environment where you can restore databases in a quick manner.
On-premises database
  1. You must build an environment for the destination database.
  2. Poor network conditions may lower the restoration speed.

Scenarios

  • DBS supports physical backup of data from an on-premises SQL Server database to an ApsaraDB for SQL Server instance. This feature is applicable to the following scenarios:

    • You can restore an on-premises SQL Server database to a specific time point to an ApsaraDB for SQL Server instance. This realizes disaster recovery and enables you to easily restore databases after misoperation.
    • You can back up historical data of an on-premises SQL Server database to an ApsaraDB for SQL Server instance. This frees up the storage space of the on-premises SQL Server database.
    • After you restore data from a database to a new database, you can perform certain offline and slow queries on the new database so that online business will not be affected.
    • DBS allows you to restore databases to a specific time point. Therefore, in financial scenarios, you can perform statistical analysis on business data of a specific time point.
  • After you use DBS to restore data to an ApsaraDB for SQL Server instance for disaster recovery, the ApsaraDB for SQL Server instance can be retained for long-term use. You can also release the instance to reduce costs.

Procedure

  1. Log on to the DBS console.

  2. In the left-side navigation pane, click Backup Schedules.

  3. On the Backup Schedules page, find the target backup schedule and click Manage in the Actions column.

  4. On the Configure Task page that appears, click Restore Database in the upper-right corner.

  5. In the Set Time Restored To step in the Create Restore Task wizard, set the parameters as described in the following table and click Next.

    Section Parameter Description
    None Task Name The name of the restore task. DBS automatically generates a task name. We recommend that you set an informative name for easy identification. You do not need to use a unique task name.
    Set Time Restored To Time Range Available to Restore The time range available for restoring backup sets. DBS displays the available time range automatically.
    Restore To The time point that you want to restore the source database to. The value must be within the time range that is specified by the Time Range Available to Restore parameter.
    Configure Destination Database DB Instance Mode The type of the destination database instance. In this example, set this parameter to New(Recommend) to create an ApsaraDB for SQL Server instance.
    The ApsaraDB for SQL Server instance to be created has the following default properties:
    • The default version of the instance to be created is the earliest Enterprise Edition that is compatible with the source database version. The earliest version of ApsaraDB for SQL Server that supports database restoration is ApsaraDB for SQL Server 2012 Enterprise Edition. For example, if the source database version is SQL Server 2008, the default version of the instance to be created is ApsaraDB for SQL Server 2012 Enterprise Edition. If the source database version is SQL Server 2016 Standard, the default version of the instance to be created is ApsaraDB for SQL Server 2016 Enterprise Edition.
    • The default billing method of the instance to be created is pay-as-you-go.
    Database LocationThe location of the destination database. By default, this parameter is set to RDS Instance.
    Instance RegionThe region where you want to create the instance. By default, this parameter is set to China (Hangzhou). You can set this parameter based on your location to enable real-time database query.
    VPCThe VPC where the destination database resides.
    DB InstanceClassThe specification of the instance to be created. Four specifications are available: four cores and 16 GB memory, eight cores and 32 GB memory, 16 cores and 64 GB memory, and 32 cores and 128 GB memory. The default specification is four cores and 16 GB memory. For more information about specifications and prices, see the pricing page.
    Storage SpaceThe storage space of the instance to be created. The default storage space is 500 GB. You can set this parameter based on your actual needs.

    Create Restore Task SQL Server to RDS

  6. Set Restore Mode to Restore Entire Instance and click Precheck.

  7. After the restore task is created, click Restore Tasks in the left-side navigation pane. On the page that appears, you can view the task ID and restore progress of the restore task.
    Restore Tasks

  8. After the restore task is completed, you can log on to the ApsaraDB for RDS console to view the ApsaraDB for SQL Server instance you created.

    For more information about ApsaraDB for SQL Server databases, see Quick start of ApsaraDB for SQL Server.