edit-icon download-icon

Migrate data to ApsaraDB for RDS SQL Server 2012/2016

Last Updated: Mar 28, 2018

This article describes how to migrate full backup data to RDS SQL Server 2012/2016.

Applicable versions

  • Basic series (single-node)
    • RDS SQL Server 2012 Web, Enterprise
    • RDS SQL Server 2016 Web, Enterprise
  • High-availability series (dual-node)
    • RDS SQL Server 2012 Standard, Enterprise
    • RDS SQL Server 2016 Standard, Enterprise

For instructions on how to migrate data to RDS SQL Server 2008 R2 Enterprise (high-availability series), see Migrate data to ApsaraDB for RDS for SQL Server 2008 R2 version.

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 and log backup files are not supported.

Backup file suffix

The backup file suffix must be bak, diff, trn, or log. If the backup file is not generated using the script provided in this article, use one of the following suffix:

  • 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 full backup file name cannot contain certain special characters, such as @ or |; 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.

Delete backup file from OSS

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

Prerequisites

Instance capacity

Ensure that RDS SQL Server instance has sufficient storage space. Upgrade the space if needed.

A database with the same name is not allowed in target instance

You do not need to create a target database in advance. This is different from the requirement stated in Migrate data to ApsaraDB for RDS for SQL Server 2008 R2 version.

If a database with the same name already exists in the target instance, back up and delete the database before creating the migration task.

Create initial account on target instance

It is recommended that you create an initial account for the target instance on the console in advance. If the target instance does not have an initial account, the migration also succeeds but you cannot access the database unless you take measures by referring to Common Errors at the end of this article.

For information on how to create an initial account, see steps 1 to 7 in Create account and database for SQL Server 2012 and 2016.

Prepare OSS bucket

Create an OSS bucket that is in the same region as the target instance if you do not have one.

  1. Log on to OSS console.

  2. Click the + sign on the left pane.

    1

  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 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 has no allocation errors or consistency errors.

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

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

Procedure

Only three steps are required to migrate a local database to RDS SQL Server 2012/2016 on the cloud:

  1. Back up local database

  2. Upload the backup file to OSS

  3. Create the migration task

Back up local databse

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 in a way you are used to or by following these steps:

  1. Download the backup script and open it with SSMS.

  2. Modify the following parameters as needed:

    • @backup_databases_list: databases to be backed up. Separate multiple databases with semicolon or comma.

    • @backup_type: backup type. Values are as follows:

      • FULL: full backup

      • DIFF: differential backup

      • LOG: log backup

    • @backup_folder: 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: Perform checking only.

  3. Run the backup script.

Upload backup file to OSS

Upload the backup file to your OSS bucket.

Method 1: Use ossbrowser

It is recommended 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 unattended migration, use an OSS API to perform an upload that can be paused and resumed. For more information, see Multipart upload

Create migration task

  1. Log on to the RDS console.

  2. Select the region where the target instance is located.

  3. Click the target instance ID.

  4. On the left-side navigation pane, choose Backup and Recovery.

  5. Click OSS Backup Data Upload at the upper right corner.

  6. If you are using the function for the first time, authorize the RDS offical service account to access OSS:

    1. Click Authorize in the data import wizard:

    2

    1. Click Confirm Authorization Policy.

    3

  7. In step 3 of the data import wizard, set the following parameters and click OK to generate the OSS backup file upload task.

    • Database Name: target database name on the target instance

    • OSS Bucket: OSS bucket that stores the backup file

    • OSS Subfolder Name: Inlcude bucket subfolders if any. Separate subfolders of different levels with a slash (/), for example, Dir1/Dir2/Dir3. Skip this field if it is not involved.

    • OSS File: Click the magnifier icon on the right. You can perform a fuzzy search of the backup file prefix. The file names, sizes, and update time are displayed. Select the backup file you need.

    You can click Refresh to view the latest status of the migration task. If the migration fails, view the task description and recify faults by referring to Common errors at the end of this article.

View migration records

On the Backup and Recovery 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.

4

Common errors

Each migration record has a task description, which helps you identify the failuer 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: A existing database with the same name is not allowed on the target instance. This prevents you from mistakenly overwriting a database.

  • Solution: If a database with the same name already exists on the target instance, perform a full backup of the database on the console and delete the database before the migration.

Differential backup file

  • 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 file

  • Error message: Backup set (xxx.trn) is a Transaction Log backup, we only accept a FULL Backup.

  • Error cause: The 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 if 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 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 error

  • Error message: DBCC checkdb failed

  • Error cause: DBCC CheckDB failure indicates that the local database has errors.

  • Solution:

    1. Use the following command to fix the local database (Note: 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.

OSS download link expires

This error only happens to the RDS SQL 2008 R2 high-availability edition.

  • Error message: Failed to download backup file since OSS URL was expired.

  • Error cause: The OSS download link has expired, so the backup file download fails.

  • Solutions:

    • Solution 1: Set the download link validity period to a larger value (at most 18 hours). See the following figure.

      5

    • Solution 2: Set the ACL permission of the OSS database backup file to Public Read. See the following figure.

      6

      Note: The backup file with the Public Read ACL permission is always downloadable without an expiration date of the download link. To prevent security risks, set the ACL permission to Private after migrating the file.

Insufficent space 1

  • Error message: Not Enough Disk Space for restoring, space left (xxx MB) < needed (xxx MB)

  • Error cause: The remaining space on the instace is insufficient for the migration.

  • Solution: Upgrade the storage space of the instance.

Insufficent space 2

  • Error message: Not Enough Disk Space, space left xxx MB < bak file xxx MB

  • Error cause: The remaining space on the instace is smaller than the backup file size.

  • Solution: Upgrade the storage space of the instance.

No initial 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 initial account, the migration still succeeds, but the migration task does not know which user to authorize.

  • Solution:

    1. Create an initial account. For details, see steps 1 to 7 in Create account and database for SQL Server 2012 and 2016.

    2. Reset the password of the initial account. For more information, see Reset instance password.

    3. Use the initial account to access the database on the cloud.

Thank you! We've received your feedback.