This topic describes how to migrate full backup files from Object Storage Service (OSS) to ApsaraDB RDS for SQL Server.

Prerequisites

  • Your RDS instance must run one of the following SQL Server versions and RDS editions:
    • SQL Server 2017 EE on RDS Cluster Edition
    • SQL Server 2012 SE, 2012 EE, 2014 SE, 2016 SE, 2016 EE, 2017 SE, and 2019 SE on RDS High-availability Edition
    • SQL Server 2012 SE, 2012 Web, and 2016 Web on RDS Basic Edition
    Note For more information about how to migrate on-premises databases to instances that run SQL Server 2008 R2 on RDS High-availability Edition, see Migrate full backup data to ApsaraDB RDS for SQL Server 2008 R2.
  • Your RDS instance has sufficient storage space. If not, you must increase the space before migration.
  • The names of databases in your RDS instance must be unique.
  • A privileged account is created for your RDS instance. For more information, see Create databases and accounts for an ApsaraDB for RDS instance running SQL Server 2012, 2016, 2017 SE, or 2019.
  • An Object Storage Service (OSS) bucket is created in the region where your RDS instance is created. For more information, see Create buckets.
  • Ensure that the system does not report allocation errors and consistency errors by executing the DBCC CHECKDB statement.
    Note If the check succeeds, the following messages are returned:
    ...
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxx'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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

Note Before you perform a full backup for on-premises databases, make sure that you have stopped writing data. The data written during the backup process is not backed up.
  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.
    Select a region
  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.

Related operations

Operation Description
CreateMigrateTask Restores the backup files from OSS to an ApsaraDB for RDS instance.
CreateOnlineDatabaseTask Opens a database to which a migration task needs to migrate data on an ApsaraDB for RDS instance.
DescribeMigrateTasks Queries the migration tasks of an ApsaraDB for RDS instance.
DescribeOssDownloads Queries the backup files that a migration task needs to migrate to an ApsaraDB for RDS instance.