This topic describes how to migrate full backup data from OSS to an RDS for SQL Server instance in any of the following editions:

  • RDS for SQL Server 2012/2016 Web Edition
  • RDS for SQL Server 2012 Enterprise Basic Edition
  • RDS for SQL Server 2012/2016 Standard or Enterprise Edition
  • RDS for SQL Server 2017 Enterprise AlwaysOn Edition

For instructions on how to migrate data to RDS for SQL Server 2008 R2 Enterprise High-availability Edition, see Migrate full backup data to RDS for SQL Server 2008 R2.

Restrictions

  • Backup file version

    Backup data of new SQL Server versions cannot be migrated to old SQL Server versions. For example, you cannot migrate data from SQL Server 2016 to SQL Server 2012.

  • Backup file type

    Differential backup files and log backup files are not supported.

  • Backup file suffix
    The backup file suffix must be bak, diff, trn, or log. If your backup file is not generated by using the script provided in this topic, use one of the following suffixes:
    • bak: indicates a full backup file.
    • diff: indicates a differential backup file.
    • trn or log: indicates a transaction log backup file.
  • Backup file name

    The name of a full backup file cannot contain special characters, such as the at sign (@) and vertical bar (|). Otherwise, the migration will fail.

Precautions

  • AliyunRDSImportRole

    After you authorize the RDS official service account to access OSS, the system creates the role AliyunRDSImportRole in the RAM system. Do not modify or delete the role. Otherwise, the backup upload cannot succeed, and you need to perform the authorization on the wizard again.

  • Backup file deletion from OSS

    Before the backup restoration is complete, do not delete the backup file from OSS.

Prerequisites

  • The target RDS instance has sufficient storage space.

    If the instance does not have sufficient storage space, expand the storage space of the instance before the migration.

  • A database with the same name as the database to be migrated does not exist in the target instance.

    You do not need to create a target database before the migration. This is different from the requirement stated in Migrate full backup data to RDS for SQL Server 2008 R2.

    If the target RDS instance already has a database whose name is the same as that of a database to be migrated, back up and delete the database in the target RDS instance before creating a migration task.

  • A superuser account has been created on target RDS instance.

    We recommend that you create a superuser account for the target RDS instance on the console before the migration. If the target RDS instance does not have a superuser account, the migration can succeed but you cannot access the database unless you take measures by following the instructions provided in Common errors at the end of this topic.

    For information about how to create a superuser account, see Create databases and accounts for an RDS for SQL Server 2012 or 2016 instance or Create databases and accounts for an RDS for SQL Server 2017 instance.

  • An OSS bucket that is in the same region as the target RDS instance has been created.

    If such an OSS bucket does not exist, you can create one by completing the following steps:

    1. Log on to OSS console.
    2. Click the + sign in the left pane.

    3. Set the bucket name, region, storage class, and ACL permission, and click OK. (Ensure that the bucket is in the same region as the target RDS for SQL Server instance so that the bucket can be selected in subsequent steps.)
  • Run DBCC CHECKDB.

    Run DBCC CHECKDB(‘xxx’) on the local database and ensure that the result is as follows, with no allocation errors or consistency errors:

    ...
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxx'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    If DBCC CHECKDB shows any errors, fix them before the migration.

Procedure

Only three steps are required to migrate a local database to an RDS for SQL Server 2012/2016/2017 instance:

  1. Back up the local database.
  2. Upload the backup file to OSS.
  3. Create a migration task.

Back up the local database

Before performing a full backup of the local database, stop writing data into the database. Data written into the database during the backup will not be backed up.

You can perform a full backup by using your own method or following these steps:

  1. Download the backup script and open it with SSMS.
  2. Set the following parameters as needed.
    Configuration item Description
    @backup_databases_list The database to be backed up. If you want to back up multiple databases, separate the database names by using semicolons (;) or commas (,).
    @backup_type The backup type. Values are as follows:
    • FULL: full backup
    • DIFF: differential backup
    • LOG: log backup
    @backup_folder The local folder that stores the backup file. It will be automatically created if it does not exist.
    @is_run Whether to perform a backup. Values are as follows:
    • 1: Perform a backup.
    • 0: Only perform a check.
  3. Run the backup script.

Upload the backup file to OSS

  • 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 file is smaller than 5 GB, you can use the OSS console to upload it. For more information, see Upload an object.

  • Method 3: Use an OSS API.

    If you require automatic migration, use an OSS API to perform an upload that can be paused and resumed. For more information, see Multipart upload.

Create a migration task

  1. Log on to the RDS console.
  2. Select the region where the target instance is located.
  3. Find the the target instance and click its ID.
  4. In the left-side navigation pane, click Backup and Restoration.
  5. Click OSS Backup Data Upload in the upper right corner.
  6. Optional. If you are using the function for the first time, authorize the RDS official service account to access OSS:
    1. In the Import data step of the Import Guide, click Authorize.

    2. Click Confirm Authorization Policy.

  7. Set the following parameters and click OK to generate an OSS backup file upload task.
    Configuration item Description
    Database Name The name of the target database in the target instance.
    OSS Bucket The OSS bucket that stores the backup file.
    OSS Subfolder Name The name of the subfolder where the backup is located.
    OSS File Click the magnifier icon next to this field. You can perform a fuzzy search with the backup file prefix. The file names, sizes, and update time are displayed. Select the backup file you need.
    Cloud Migration Plan
    • Immediate Access (Full Backup): If you have only the full backup file, select Immediate Access.
    • Access Pending (Incremental Backup): If you have a full backup file and a differential or log backup file, select this option.
    Consistency Check Mode
    • Synchronous DBCC: Perform DBCC check only after the database is opened. This reduces service downtime because DBCC check takes a long time if the database is large. If you are sensitive to service downtime and do not care about the DBCC check result, select this option.
    • Asynchronous DBCC: If you want to use DBCC check to find out consistency errors of your source database, select this option. Note that this option lengthens the time it takes to open the database.

    You can click Refresh to view the latest status of the migration task. If the migration fails, view the task description and rectify faults by following the instructions provided in Common errors at the end of this topic.

View migration records

View migration records as follows:

On the Backup and Restoration page, click Backup Data Upload History. Migration records of the past week are displayed by default. You can change the query time range as needed.

Common errors

Each migration record has a task description, which helps you identify the failure cause. Common errors are as follows:

  • Database with the same name already exists
    • Error message: The database (xxx) is already exist on RDS, please backup and drop it, then try again.
    • Error cause: An existing database with the same name is not allowed in the target instance. This prevents you from mistakenly overwriting a database.
    • Solution: If a database with the same name already exists in the target instance, perform a full backup of the database on the console and delete the database before the migration.
  • Differential backup files
    • Error message: Backup set (xxx.bak) is a Database Differential backup, we only accept a FULL Backup.
    • Error cause: The migration supports only full backup files rather than differential backup files.
  • Transaction log backup files
    • Error message: Backup set (xxx.trn) is a Transaction Log backup, we only accept a FULL Backup.
    • Error cause: Full migration supports only full backup files rather than log backup files.
  • Backup file verification fails
    • Error message: Failed to verify xxx.bak, backup file was corrupted or newer edition than RDS.
    • Error cause: The verification fails because the backup file is damaged or the local SQL Server version is later than the target RDS SQL Server version. For example, the verification fails if the migration is from SQL Server 2016 to SQL Server 2012.
    • Solution: If the backup file is damaged, perform a full backup again to generate a new backup file. If the local SQL Server version is later than the target RDS SQL Server version, change the target RDS SQL Server version.
  • DBCC CHECKDB errors
    • Error message: DBCC checkdb failed
    • Error cause: DBCC CheckDB failure indicates that the local database has errors.
    • Solution:
      1. Run the following command to fix the local database (this may cause data loss):
        DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS
      2. Perform a full backup for the database again.
      3. Upload the new database file to OSS.
      4. Perform the migration again on the RDS console.
  • Insufficient space 1
    • Error message: Not Enough Disk Space for restoring, space left (xxx MB) < needed (xxx MB)
    • Error cause: The remaining space on the instance is insufficient for migration.
    • Solution: Expand the storage space of the instance.
  • Insufficient space 2
    • Error message: Not Enough Disk Space, space left xxx MB < bak file xxx MB
    • Error cause: The remaining space on the instance is smaller than the backup file size.
    • Solution: Expand the storage space of the instance.
  • No superuser account
    • 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).
    • Error cause: If the RDS instance has no superuser account, the migration still succeeds, but the migration task does not know which user to authorize.
    • Solution:
      1. Create a superuser account. For details, see Create accounts and databases (SQL Server 2012 or 2016).
      2. Reset the password of the superuser account. For more information, see Reset the instance password.
      3. Use the superuser account to access the database on the cloud.

APIs

API Description
CreateMigrateTask Used to create a migration task.
CreateOnlineDatabaseTask Used to open a database.
DescribeMigrateTasks Used to query the list of migration tasks.
DescribeOssDownloads Used to view details about files in a migration task.