Migrate the incremental backup data of a self-managed SQL Server database to an ApsaraDB RDS instance (SQL Server 2012, 2016, 2017, and 2019)

Precautions

• The migration solution in this topic is at the database level. It allows you to migrate the incremental backup data of a single self-managed database at a time. If you want to migrate the incremental backup data of multiple or all self-managed databases at a time, we recommend that you use the instance-level migration solution. For more information, see Migrate data from a self-managed SQL Server instance to an ApsaraDB RDS for SQL Server instance.
• The migration from a later SQL Server version to an earlier SQL Server version is not supported. For example, the self-managed database runs SQL Server 2016 and your RDS instance runs SQL Server 2012. In this case, you cannot migrate the incremental backup data of the self-managed database to your RDS instance.
• The names of the backup files cannot contain special characters, such as at signs (@) and vertical bars (|). If the names of the backup files contain special characters, the migration fails.
• After you authorize the service account of your RDS instance to access the OSS bucket, a role named AliyunRDSImportRole is created in Resource Access Management (RAM). Do not modify or delete this role. If you modify or delete this role, you cannot download the backup files from the OSS bucket. In addition, if you modify or delete this role, you must re-authorize the service account of your RDS instance by using the migration wizard.
• The migration does not carry over the accounts of the self-managed database. After the migration is complete, you must create accounts on your RDS instance by using the ApsaraDB RDS console.
• Before the migration is complete, do not delete the backup files from the OSS bucket. If you delete the backup files before the migration is complete, the migration fails.
• The names of the backup files can be suffixed only by using bak, diff, trn, or log. If the backup files are not generated by using the backup script that is provided in this topic, you must add one of the following suffixes to the file names:
  • bak: indicates a full backup file.
  • diff: indicates a differential backup file.
  • trn or log: indicates a transaction log backup file.

Migration process

The following flowchart shows the migration process on a timeline.

The preceding migration provides an example of how to minimize downtime. Your application can continue to run, and you do not need to stop your application until the last log backup. In this example, the downtime of your application does not exceed 5 minutes.

Back up the self-managed database

1. Download the backup script file. Then, open the file by using SQL Server Management Studio (SSMS).
2. Configure the following parameters.

   Parameter	Description
   @backup_databases_list	Specify the name of the self-managed database that you want to back up. In the other scenarios, this parameter also allows you to specify multiple databases. If you specify more than one database name, separate these database names with semicolons (;) or commas (,).
   @backup_type	Specify the type of backup that you want to perform. Valid values: FULL: full backup DIFF: differential backup LOG: log backup
   @backup_folder	Specify the local directory that is used to store the backup files of the self-managed database. If the specified directory does not exist, ApsaraDB RDS automatically creates the directory.
   @is_run	Specify whether to perform a backup or a check. Valid values: 1: specifies to perform a backup. 0: specifies to perform a check.

3. Run the backup script.

Upload the full backup file to the OSS bucket

After the full backup and the log or differential backup are complete, you must use one of the following methods to upload the backup files to the OSS bucket:

• Method 1: Use the ossbrowser tool

  We recommend that you use the ossbrowser tool to upload the backup files. For more information, see ossbrowser.

• Method 2: Use the OSS console

  If the sizes of the backup files are less than 5 GB, you can upload the backup files by using the OSS console. For more information, see Upload objects.

• Method 3: Use the OSS API

  You can call an OSS API operation to upload the backup files in resumable mode. For more information, see Multipart upload and resumable upload.

Create a migration task

1. Visit the RDS instance list, select a region above, and click the target instance ID.
2. In the left-side navigation pane, click Backup and Restoration.
3. In the upper-right corner of the page, click Migrate OSS Backup Data to RDS.
4. In the Import Guide wizard, click Next twice.
   Note If you run the OSS-based migration wizard for the first time, you must authorize the service account of your RDS instance to access the OSS bucket. In this case, you must click Authorize and complete the authorization. Otherwise, the OSS Bucket drop-down list in the Import Data step is empty.
5. In the Import Data step, configure the following parameters.

   Parameter	Description
   Database Name	Enter the name of the destination database on your RDS instance. The destination database stores the data that is migrated from the self-managed database. The name of the destination database must be different from the name of the self-managed database Note The name of the destination database must meet the requirements of open source SQL Server.
   OSS Bucket	Select the OSS bucket that stores the full backup file.
   OSS Subfolder Name	Enter the name of the OSS subfolder that stores the full backup file.
   OSS File	Specify the full backup file that you want to import. You can click the search icon to search for the full backup file by using a prefix-based fuzzy match. ApsaraDB RDS displays the name, size, and update time of the full backup file.
   Cloud Migration Method	Select Immediate Access (Full Backup). Immediate Access (Full Backup): If you want to migrate only a full backup file, select this migration method. In this case, the following parameter settings take effect in the CreateMigrateTask operation: BackupMode = FULL and IsOnlineDB = True. Access Pending (Incremental Backup): If you want to migrate a full backup file and a differential or log backup file, select this migration method. In this case, the following parameter settings take effect in the CreateMigrateTask operation: BackupMode = UPDF and IsOnlineDB = False.

6. Click OK.

After the migration task is complete, you can click Refresh to view the latest status of the migration task.

Import the log or differential backup file

After the full backup file of the self-managed database is imported into your RDS instance, you must import the log or differential backup file.

1. Visit the RDS instance list, select a region above, and click the target instance ID.
2. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Backup Data Upload History.
3. Find the destination database and click Upload Incremental Files. In the dialog box that appears, configure the parameters, select the log or differential backup file, and then click OK.
   Note

   • If you have multiple log backup files, you must use the same method to upload these log backup files one by one.
   • Make sure that the size of the last log or differential backup file does not exceed 500 MB. This minimizes the time that is required to complete the migration.
   • Before the last log backup file is generated, you must stop data writes to the self-managed database. This ensures data consistency between the self-managed database and the destination database on your RDS instance.

Open the destination database

After you import all the backup files into the destination database on your RDS instance, the destination database is in the In Recovery or Restoring state. If your RDS instance runs the High-availability Edition, the destination database is in the In Recovery state. If your RDS instance runs the Basic Edition, the destination database is in the Restoring state. In these cases, you cannot perform read or write operations on the destination database. Before you can perform read and write operations, you must open the destination database.

1. Visit the RDS instance list, select a region above, and click the target instance ID.
2. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Backup Data Upload History.
3. Find the destination database and click Open Database.
4. In the dialog box that appears, select a consistency check mode and click OK.
   Note ApsaraDB RDS provides the following consistency check modes:

   • Asynchronous DBCC: The DBCC CHECKDB statement is executed after the destination database is opened. This reduces the time that is required to open the destination database and minimizes the downtime of your application. If the destination database is large, a long period of time is required to execute the DBCC CHECKDB statement. Therefore, if your application is sensitive to downtime but insensitive to the result of the DBCC CHECKDB statement, we recommend that you select this consistency check mode. In this case, the following parameter setting takes effect in the CreateMigrateTask operation: CheckDBMode = AsyncExecuteDBCheck.
   • Synchronous DBCC: The DBCC CHECKDB statement is executed at the same time when the destination database is opened. If you want to identify consistency errors between the self-managed database and the destination database based on the result of the DBCC CHECKDB statement, we recommend that you select this consistency check mode. In this case, the following parameter setting takes effect in the CreateMigrateTask operation: CheckDBMode = SyncExecuteDBCheck. However, the time that is required to open the destination database increases.

View details about the imported backup files

If you want to view details about the backup files that are imported by using a migration task, perform the following operations: Open the Backup and Restoration page. Click the Backup Data Upload History tab. Find the migration task and click View File Details. In the dialog box that appears, view details about the imported backup files.

Common errors

For more information about the common errors that may occur during the migration of full backup data, see Migrate full backup data to ApsaraDB RDS for SQL Server 2012, 2014, 2016, 2017, or 2019. During the migration of incremental backup data, you may encounter the following errors:

• The destination database cannot be opened.
  • Error message: Failed to open database xxx.
  • Cause: Some advanced features are enabled for the self-managed database. However, these advanced features are not supported by your RDS instance. For example, the self-managed database runs an Enterprise Edition of SQL Server and your RDS instance runs a Web edition of SQL Server. In this case, if the data compression and partition features are enabled for the self-managed database, this error is reported when you open the destination database.
  • Solution:
    • Disable the advanced features for the self-managed database, back up data again, and then migrate the data by using OSS.
    • Purchase an RDS instance that runs the same SQL Server edition as the self-managed database. Then, migrate the data of the self-managed database to the purchased RDS instance.
• The log sequence numbers (LSNs) in the backup chain are not consecutive.
  • Error message: The log in this backup set begins at LSN XXX, which is too recent to apply to the database.RESTORE LOG is terminating abnormally.
  • Cause: The LSNs in the log or differential backup file are different from the LSNs in the previous backup file that is used for the restoration.
  • Solution: Select the log or differential backup file whose LSNs are the same as the LSNs of the previous backup file that is used for the restoration.
• The DBCC CHECKDB statement cannot be executed in asynchronous mode.
  • Error message: asynchronously DBCC checkdb failed: CHECKDB found 0 allocation errors and 2 consistency errors in table 'XXX' (object ID XXX).
  • Cause: After data is restored to your RDS instance with the Asynchronous DBCC consistency check mode selected, ApsaraDB RDS executes the DBCC CHECKDB statement. If the destination database fails the consistency check, consistency errors occur in the self-managed database.
  • Solution:
    • Execute the following statement on the destination database:

      DBCC CHECKDB (DBName,REPAIR_ALLOW_DATA_LOSS)

      Note If you use this statement to fix the error, data may be lost.
    • Execute the following statement on the self-managed database to fix the error, and then perform the migration again:

      DBCC CHECKDB (DBName,REPAIR_ALLOW_DATA_LOSS)

• The selected backup file is a full backup file.
  • Error message: Backup set (xxx) is a Database FULL backup, we only accept transaction log or differential backup.
  • Cause: After the full backup file is restored to your RDS instance, you can select and restore only a log or differential backup file. If you select a full backup file again, this error is reported.
  • Solution: Select and restore a log or differential backup file.
• The number of specified self-managed databases exceeds the upper limit.
  • Error message: The database (xxx) migration failed due to databases count limitation.
  • Cause: If the number of specified self-managed databases exceeds the upper limit, this error is reported.
  • Solution: Migrate the data of excess self-managed databases to another RDS instance. Otherwise, delete unnecessary databases from the current RDS instance.

Related operations