Migrate full backup data to ApsaraDB RDS for SQL Server 2012,2014, 2016, 2017, or 2019

Precautions

• You can migrate only one database to ApsaraDB RDS for SQL Server at a time. If you need to migrate multiple or all databases in an instance to ApsaraDB RDS for SQL Server, we recommend that you use the instance-based data migration feature. For more information, see Migrate data from an on-premises SQL Server instance to an ApsaraDB RDS for SQL Server instance.
• Migrating data from a database to a database of an earlier version is not supported. For example, you cannot migrate data from SQL Server 2016 to SQL Server 2012.
• Migrating data by using incremental backup files or log files is not supported.
• The names of backup files cannot contain special characters such as at signs (@) or vertical bars (|). Otherwise, the on-premises database cannot be migrated to your RDS instance.
• After you have granted the access permissions, the system creates a role named AliyunRDSImportRole in RAM. Do not modify or delete this role. Otherwise, you will not be able to download the backup files when you migrate data to your RDS instance. If you modify or delete this role, you must re-authorize your RDS account.
• Do not delete backup files stored in OSS before the migration task is complete.
• The names of backups files can end with only .bak, .diff, .trn, or .log. If you do not use the script in this topic to generate a backup file, you must use the following suffixes:
  • bak: indicates a full backup file.
  • diff: indicates a differential backup file.
  • trn and log: indicate a transaction log backup.

Back up the on-premises database

1. Download the backup script and open it with SQL Server Management Studio (SSMS).
2. Modify the following parameters:

   Parameter	Description
   @backup_databases_list	The databases that you want to back up. Separate them with semicolons (;) or commas (,).
   @backup_type	The type of the backup. Valid values: FULL: full backup DIFF: incremental backup LOG: log backup
   @backup_folder	The directory that stores the backup files on your RDS instance. If you do not specify this parameter, a directory will be automatically created.
   @is_run	Specifies whether to perform a backup. Valid values: 1: performs a backup. 0: only performs a check.

3. Execute the backup script.

Upload the backup file to OSS

After the on-premises database is backed up, you can use one of the following methods to upload the backup file to your OSS bucket:

• Method 1: Use ossbrowser

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

• Method 2: Use the OSS console

  If the backup files are smaller than 5 GB in size, upload them by using the OSS console. For more information, see Upload an object.

• Method 3: Call OSS API operations

  You can use the OSS API to perform a resumable upload. For more information, see Multipart upload and resumable upload.

Create the migration task

1. Log on to the ApsaraDB for 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.
6. Click Next twice until the Import Data step appears.
   Note If you are migrating backup data from OSS to your RDS instance for the first time, ApsaraDB for RDS prompts you to authorize the OSS access permission to your Alibaba Cloud account. Click Authorize. On the Cloud Resource Access Authorization page, click Confirm Authorization Policy to complete the authorization.
7. Configure the following parameters.

   Parameter	Description
   Database Name	Enter the name of the destination database in the destination instance. Note The name of the database must meet the requirements of SQL Server.
   OSS Bucket	Select the OSS bucket to store the backup file.
   OSS Subfolder Name	Enter the name of the OSS subfolder that you use to store the backup file.
   OSS File	Enter the prefix of the backup file name and click the search icon. A list of files is displayed. The list contains the name, size, and update time of each file that matches the query. Select the backup file that you need to restore.
   Cloud Migration Plan	Immediate Access (Full Backup): You can use a full backup file to migrate your database to your RDS instance. In this plan, select Immediate Access, and the parameter BackupMode is set to FULL and IsOnlineDB is set to True in the CreateMigrateTask operation. Access Pending (Incremental Backup): You can use a full backup file and an incremental or log file to incrementally migrate your database to your RDS instance. The parameter BackupMode is set to UPDF and IsOnlineDB is set to False in the CreateMigrateTask operation.
   Consistency Check Mode	Asynchronous DBCC: If the database contains a large volume of data, the DBCC CHECKDB statement will take a long time to execute. The DBCC CHECKDB statement is asynchronously executed after the database is opened to decrease the time spent on opening the database and minimize downtime. If your application requires a short downtime, and the result of DBCC CHECKDB does not affect your business, we recommend that you select Asynchronous DBCC. The parameter CheckDBMode is set to SyncExecuteDBCheck in the CreateMigrateTask operation. Synchronous DBCC: If you need to execute the DBCC CHECKDB statement to identify consistency errors, we recommend that you select Synchronous DBCC. In this case, the database takes more time to open. The parameter CheckDBMode is set to SyncExecuteDBCheck in the CreateMigrateTask operation.

8. Click OK.

After the cloud migration task is complete, you can click Refresh to view the latest status of the migration task. If the migration fails, you can view the task description and troubleshoot errors by referring to Common errors.

View migration records

On the Backup and Restoration page, click the Backup Data Upload History tab. By default, migration records in the last seven days are displayed.

Common errors

Each restoration record contains the task description and can be used to troubleshoot error causes. The following content describes common error messages:

• The database with the same name already exists on your RDS instance.
  • Error message: The database (xxx) is already exist on RDS, please backup and drop it, then try again.
  • Cause: The database with the same name already exists in your RDS instance. To ensure data security, you can only migrate an on-premises database whose name is different from those of the databases on your RDS instance.
  • Solution: If you need to overwrite the database in your RDS instance with the on-premises database, you must back up the database, delete it from your RDS instance, and migrate the on-premises database to your RDS instance.
• The file is an incremental backup file.
  • Error message: Backup set (xxx.bak) is a Database Differential backup, we only accept a FULL Backup.
  • Cause: You uploaded an incremental backup file. The full migration only supports full backup files.
• The file is a log backup file.
  • Error message: Backup set (xxx.trn) is a Transaction Log backup, we only accept a FULL Backup.
  • Cause: You uploaded a log backup file. The full migration only supports full backup files.
• The verification of the backup file fails.
  • Error message: Failed to verify xxx.bak, backup file was corrupted or newer edition than RDS.
  • Cause: The backup file is damaged or the on-premises database is in a later SQL Server version than your RDS instance. For example, if you migrate an on-premises database of SQL Server 2016 to an SQL Server 2012 instance, the error message is displayed.
  • Solution: If the backup file is damaged, you must perform a full backup for the on-premises database and migrate again. If the database engine version is unmatched, you must use an instance of the same or later version.
• DBCC CHECKDB fails.
  • Error message: DBCC checkdb failed.
  • Cause: Allocation errors or consistency errors occur in the on-premises database.
  • Solution: Execute the following statement in the on-premises database.
    Note Your data may be lost when you use this statement to fix errors.

    DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS

• The space is insufficient.
  • Error message: Not Enough Disk Space for restoring, space left (xxx MB) < needed (xxx MB)
  • Cause: The unused space of your RDS instance is insufficient for migrating the backup file to the instance.
  • Solution: Increase the instance space.
• The space is insufficient.
  • Error message: Not Enough Disk Space, space left xxx MB < bak file xxx MB
  • Cause: The unused space of your RDS instance is smaller than the backup file.
  • Solution: Increase the instance space.
• No privileged account exists.
  • 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: There is no privileged account in your RDS instance, and the database permissions are not granted to any account. However, the task is successful because the backup file is restored to the destination instance.
  • Solution: Create a privileged account. For more information, see Create databases and accounts for an ApsaraDB for RDS instance running SQL Server 2012, 2016, 2017 SE, or 2019.

Related operations