Migrate the full 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 full backup data of a single self-managed database at a time. If you want to migrate the full 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 an on-premises 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, if the self-managed database runs SQL Server 2016 and your RDS instance runs SQL Server 2012, you cannot migrate the full backup data of the self-managed database to your RDS instance.
• Differential and log backup files are not supported.
• The name of the full backup file cannot contain special characters, such as at signs (@) and vertical bars (|). If the name contains 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 full backup file from the OSS bucket. If you modify or delete this role, you must also 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 full backup file from the OSS bucket. If you delete the full backup file before the migration is complete, the migration fails.
• The names of backup files can be suffixed only by using bak, diff, trn, or log. If the full backup file is not generated by using the backup script that is provided in this topic, you must add the bak suffix to the name of the full backup file.
  • bak: indicates a full backup file.
  • diff: indicates a differential backup file.
  • trn or log: indicate a transaction log backup file.

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 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 multiple databases, separate the names of these databases 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 directory that is used to store backup files on 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 on the self-managed database is complete, you must use one of the following methods to upload the full backup file to the OSS bucket:

• Method 1: Use the ossbrowser tool

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

• Method 2: Use the OSS console

  If the size of the full backup file is less than 5 GB, you can upload the full backup file 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 full backup file in resumable mode. For more information, see Multipart upload and resumable upload.

Create a migration task

1. Log on to the ApsaraDB RDS console.
2. In the top navigation bar, select the region where your RDS instance resides.
   
3. Find your RDS instance and click its ID.
4. In the left-side navigation pane, click Backup and Restoration.
5. In the upper-right corner of the page, click Migrate OSS Backup Data to RDS.
   Note If you click Restore DBS Backup Data to RDS, the migration is fast and stable. For more information, see Restore SQL Server backup data to the cloud.
6. 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.
7. 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. Select the backup file that you want to upload to the OSS bucket.
   Cloud Migration Method	Immediate Access (Full Backup): If you want to migrate only a full backup file, select this migration method. For this example, select Immediate Access (Full Backup). 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.
   Consistency Check Mode	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.

8. Click OK.

Wait until the migration task is complete. You can click Refresh to view the latest status of the migration task. If the migration task fails, you can troubleshoot the failure based on the description of the migration task. For more information, see the "Common errors" section of this topic.

View the migration task

If you want to view details about the migration task, go to the Backup and Restoration page and click the Backup Data Upload History tab. By default, this tab displays the migration tasks over the last week.

Common errors

Each migration task record on the Backup Data Upload History tab of the Backup and Restoration page contains a task description. If the migration task fails or an error is reported, you can troubleshoot the failure or error based on the task description. The following common errors may occur:

• A database with the same name as the self-managed database is found on your RDS instance.
  • Error message: The database (xxx) is already exist on RDS, please backup and drop it, then try again.
  • Cause: If an existing database on your RDS instance has the same name as the self-managed database, the migration is not supported. This mechanism is designed to ensure the security of your data.
  • Solution: If you want to overwrite an existing database on your RDS instance, back up the database, delete the database from your RDS instance, and then create and run a migration task again.
• A differential backup file is used.
  • Error message: Backup set (xxx.bak) is a Database Differential backup, we only accept a FULL Backup.
  • Cause: The file that you upload is a differential backup file. It is not a full backup file. The migration solution in this topic supports only full backup files.
• A log backup file is used.
  • Error message: Backup set (xxx.trn) is a Transaction Log backup, we only accept a FULL Backup.
  • Cause: The file that you upload is a log backup file. It is not a full backup file. The migration solution in this topic supports only full backup files.
• The full backup file fails the verification.
  • Error message: Failed to verify xxx.bak, backup file was corrupted or newer edition than RDS.
  • Cause: The full backup file is corrupted, or the self-managed database runs a later SQL Server version than your RDS instance. For example, this error occurs if the self-managed database runs SQL Server 2016 and your RDS instance runs SQL Server 2012.
  • Solution: If the full backup file is corrupted, perform a full backup on the self-managed database again. Then, create and run a migration task again. If the self-managed database runs a later SQL Server version than your RDS instance, select another RDS instance that runs the same or later version than the self-managed database.
• The DBCC CHECKDB statement fails.
  • Error message: DBCC checkdb failed.
  • Cause: The self-managed database encounters allocation or consistency errors.
  • Solution: Execute the following statement on the self-managed database to fix the errors. Then, create and run a migration task again.
    Note If you execute the following statement, your data may be lost.

    DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS

• The remaining storage space of your RDS instance is insufficient.
  • Error message: Not Enough Disk Space for restoring, space left (xxx MB) < needed (xxx MB).
  • Cause: The remaining storage space of your RDS instance is less than the minimum storage space that is required for the full backup file.
  • Solution: Expand the storage capacity of your RDS instance.
• The remaining storage space of your RDS instance is insufficient.
  • Error message: Not Enough Disk Space, space left xxx MB < bak file xxx MB.
  • Cause: The remaining storage space of your RDS instance is less than the minimum storage space that is required for the full backup file.
  • Solution: Expand the storage capacity of your RDS instance.
• No privileged account is created on your RDS instance.
  • Error message: Your RDS doesn't have any init account yet, please create one and grant permissions on RDS console to this migrated database (XXX).
  • Cause: No privileged account is created on your RDS instance. As a result, the migration task cannot find the account that requires authorization. However, the full backup file has been restored to your RDS instance, and the migration task is successful.
  • Solution: Create a privileged account on your RDS instance. For more information, see Create an account and a database for an ApsaraDB RDS instance that runs SQL Server 2012, 2016, 2017 SE, or 2019 SE.

Related operations