This topic describes how to migrate data from an on-premises database to an ApsaraDB RDS SQL Server 2008 R2 instance using full backup files.

SQL Server 2008 R2 instances allow you to easily migrate data to the cloud. You only need to use Microsoft's official backup feature to back up full data on your own database, and then upload the backup files to Alibaba Cloud Object Storage Service (OSS) to fully migrate the data to the specified database of ApsaraDB for RDS through the RDS console. The backup feature of Microsoft that is fully compatible with ApsaraDB RDS SQL Server, along with OSS, greatly improve the cloud migration efficiency.

Prerequisites

You have created a destination database in your ApsaraDB RDS SQL Server instance. For more information about how to create a database, see Create databases and accounts for an RDS instance in SQL Server 2008 R2.

Note The destination database and the on-premises database must have the same name.

Billing

When you migrate data to Alibaba Cloud, no additional fees are incurred on ApsaraDB for RDS, but certain fees are incurred on OSS, as shown in the following figure.

  • No fees are incurred when you upload backup files to OSS.
  • Fees are incurred when you store backup files in OSS. For more information, see Pricing.
  • If you migrate backup files from OSS to ApsaraDB for RDS over your intranet, no fees are incurred. If you migrate over the Internet, OSS incurs fees for outbound traffic over the Internet. For more information, see Pricing.
    Note The ApsaraDB for RDS instance and OSS bucket can only communicate over an intranet only when they reside in the same region. Therefore, you must upload the backup file to the OSS bucket that is in the same region as the destination ApsaraDB for RDS instance.

Procedure

  1. Back up the on-premises database. The procedure is as follows:
    1. Start the Microsoft SQL Server Management Studio (SSMS) client.
    2. Log on to the database to be migrated.
    3. Execute the following statements to check the recovery model:
      use master;
      go
      select name, case recovery_model
      when 1 then 'FULL'
      when 2 then 'BULD_LOGGED'
      when 3 then 'SIMPLE' end model from sys.databases
      where name not in ('master','tempdb','model','msdb');
      go

      Check the model value of the on-premises database.

      • If the model value is not FULL, perform Step iv.

      • If the model value is FULL, perform Step v.

    4. Execute the following statements to set the recovery model to FULL:
      ALTER DATABASE [dbname] SET RECOVERY FULL;
      go
      ALTER DATABASE [dbname] SET AUTO_CLOSE OFF;
      go
      Note When the recovery model is set to FULL, more logs are generated. Make sure you have sufficient disk space.
    5. Execute the following statements to back up the source database (the backup file is named filename.bak):
      use master;
      go
      BACKUP DATABASE [testdbdb] to disk ='d:\backup\filename.bak' WITH COMPRESSION,INIT;
      go
    6. Execute the following statements to check the integrity of the backup file:
      USE master
       GO
       RESTORE FILELISTONLY 
         FROM DISK = N'D:\Backup\filename.bak';

      The displayed result indicates that:

      • If a result set is returned, the backup file is valid.
      • If an error is returned, the backup file is invalid. Perform Step v to back up again.
    7. Execute the following statements to reset the recovery model:
      ALTER DATABASE [dbname] SET RECOVERY SIMPLE;
      go
      Note If the recovery model of your database is FULL and Step iv is not performed, skip this step.
  2. Upload the backup file of your on-premises database to OSS and obtain the URL of the file. The procedure is as follows:
    1. Upload the backup file to OSS.
      • For more information about how to upload a file that is smaller than 5 GB, see Upload an object.
      • For more information about how to upload multiple files or a file that is larger than 5 GB, see Multipart upload and resumable upload. For more information about how to use the graphical management tool ossbrowser, see Quick start.
    2. In the left-side navigation pane of the OSS console, select the bucket to which you have uploaded the backup file.
    3. Click the Files tab.
    4. Select the backup file.
    5. In the View Details page, modify Validity Period. We recommend that you specify the value to 28800 seconds, that is, 8 hours.
      Note The URL of the backup file is required when you restore the file from OSS to ApsaraDB for RDS. The data migration will fail when the validity period of the URL expires. Therefore, we recommend that you specify the parameter value to the maximum one, namely, 28800 seconds.
    6. Click Copy File URL. By default, the URL contains the public endpoint of the file.
    7. If you migrate data over an intranet, you must replace the public endpoint with the internal endpoint in the URL. The internal endpoint varies according to the network type and region. For more information, see Regions and endpoints.
      For example, the URL of the backup file is
      http://rdstest-yanhua.oss-cn-shanghai.aliyuncs.com/testmigraterds_20170906143807_FULL.bak?Expires=1514189963&OSSAccessKeyId=TMP.AQGVf994YTPfArSpw78uix2rdGBi-dPe_FzQSLwOLP7MVlR-XXXX
      You must replace the public endpoint oss-cn-shanghai.aliyuncs.com with the internal endpoint oss-cn-shanghai-internal.aliyuncs.com.
  3. Restore data to ApsaraDB for RDS using the backup file in OSS.
    1. Log on to the ApsaraDB for RDS console.
    2. In the upper-left corner of the page, select the region where the destination instance is located.Select a region
    3. Click the ID of the instance to go to the Basic Information page.
    4. In the left-side navigation pane, select Databases. The Databases page appears.
    5. Find the destination database and click Migrate Backup Files from OSS in the corresponding Actions column.
    6. In the Import Guide wizard, read the prompt message and click Next. The Upload the backup files to OSS page appears.
    7. Read the prompt message and click Next. The Import data page appears.
    8. Enter the URL of the backup file in the OSS URL of the Backup File field.
      Note ApsaraDB RDS SQL Server 2008 R2 only supports one-time full backup file migration.
    9. Click OK.
    10. In the left-side navigation pane, select Database Migration to Cloud. The migration task list appears.
    11. Find the target migration task. If Task Status is Success, the data is migrated to the database in your ApsaraDB for RDS instance. If the migration task is not in the Success state for a long time period, you can click View File Details in the corresponding Actions column to view the cause. After the problem is solved, you must perform the preceding restoration procedure again.

Related API operations

Operation Description
CreateMigrateTask Restores the backup files from OSS to the RDS instances.
CreateOnlineDatabaseTask Opens a database when migrating backup data to RDS.
DescribeMigrateTasks Queries the list of migration tasks.
DescribeOssDownloads Queries the details of the backup data files which are uploaded to OSS.