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

Limits

Version of backup files

Migrating data from a backup file of a later version to a database of an earlier version is not supported. For example, you cannot migrate data from SQL Server 2016 to ApsaraDB RDS SQL Server 2012.

Type of backup files

Migrating data by using differential backup files or log files is not supported.

Suffix of backup files

The names of backups files can only end with .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.

Name of backup files

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 the instance.

Precautions

AliyunRDSImportRole

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 the RDS instance. If you modify or delete this role, you must re-authorize your RDS account.

Name of backup files

The names of backup files cannot contain special characters such as at signs (@) or vertical bars (|).

Backup files in OSS

Do not delete backup files stored in OSS before the migration task is completed.

Prerequisites

Instance storage

Make sure that the ApsaraDB RDS SQL Server instance has enough storage space. If not, you must increase the space before migration.

The names of databases in the ApsaraDB RDS SQL Server instance must be unique.

You do not need to create the destination database in the instance as described in Migrate data from an on-premises database to ApsaraDB RDS SQL Server 2008 R2 using full backup files.

If the instance contains a database that has the same name as the source database, you must back up and delete it from the instance before migration.

Create a privileged account for the destination instance

We recommend that you create a privileged account for the destination instance in the ApsaraDB for RDS console. Skip this step if you already have a privileged account. If the destination instance does not have a privileged account, the backup files in OSS can also be migrated to the instance, but you cannot access the destination database. You must refer to the Common error message at the lower part of the page.

For more information about how to create a privileged account, see Create databases and accounts for an ApsaraDB for RDS instance running SQL Server 2019, 2017, 2016, or 2012

Create an OSS bucket

Create an OSS bucket that is in the same region as the destination instance. Skip this step if you already have an OSS bucket. The method to create an OSS bucket is as follows:

1. Log on to the OSS console.
2. Click the plus sign + to the right of Buckets in the left-side navigation pane.
3. Configure Bucket Name, Region, Storage Class, and Access Control List, and click OK. (Make sure the OSS bucket and the ApsaraDB RDS SQL Server instance are in the same region. Otherwise, the backup files cannot be selected.)

Execute the DBCC CHECKDB statement

Execute the DBCC CHECKDB('xxx') statement in the on-premises database to make sure that there are no allocation or consistency errors. 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.

If the output contains any error messages, you must fix the on-premises database to prevent migration failure.

Procedure

You can migrate your on-premises database to an ApsaraDB RDS SQL Server 2012, 2016, or 2017 instance in the following three steps:

1. Back up the on-premises database
2. Upload the backup file to OSS
3. Create the migration task

Back up the on-premises database

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.

You can perform a full backup by using any feasible methods or by using the following method:

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

   Parameter	Description
   @backup_databases_list	The databases to be backed up. Separate multiple databases with semicolons (;) or commas (,).
   @backup_type	The type of the backup. Valid values: FULL: full backup DIFF: differential backup LOG: log backup
   @backup_folder	The local directory that stores the backup file. A directory will be automatically created if not specified.
   @is_run	Specifies whether to perform a backup. Valid values: 1: performs a backup. 0: only performs a check.

3. Run the backup script.

Upload the backup file to OSS

Upload the backup file to your OSS bucket after the on-premises database is backed up.

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 upload the file in the OSS console. For more information, see Upload an object.

Method 3: Call OSS API operations

If you want to complete the migration unattended, you can use the OSS API to perform a multipart or 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 upper-left corner of the page, select the region where the destination instance is located.
3. Click the ID of the instance to go to the Basic Information page.
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. If you are using the feature for the first time, you must perform the following steps to authorize the RDS account to access OSS:
   1. Click Authorize on the Import data step of the Import Guide wizard, as shown in the following figure.
   2. Go to the Cloud Resource Access Authorization page and click Confirm Authorization Policy to complete the authorization.

7. On the Import data step of the Import Guide wizard, configure the following parameters and click OK to upload the backup file to OSS.

   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 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 the instance. In this operation, select Immediate Access, and the parameter BackupMode is set to FULL, the parameter IsOnlineDB is set to True in the CreateMigrateTask operation. Access Pending (Incremental Backup): You can use a full backup file and a differential or log file to incrementally migrate your database to the instance. By default, this option is selected, and the parameter BackupMode is set to UPDF , 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 be opened. By default, this option is selected, and the parameter CheckDBMode is set to AsyncExecuteDBCheck in the CreateMigrateTask operation.

   

   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 the "Common errors" section in this topic.

View migration records

You can view migration records over a period of time. The procedure is as follows:

On the Backup and Restoration page, click the Backup Data Upload History tab. By default, migration records of the last seven days are displayed. You can also modify the time range to view the records over a specific period of time.

Common errors

Each restoration record contains the task description and can be used to troubleshoot error causes. Common error messages are as follows:

The database with the same name already exists in the instance.

• Error message: The database (xxx) is already exist on RDS, please backup and drop it, then try again.

• Cause: To ensure data security, you cannot migrate an on-premises database to an ApsaraDB RDS SQL Server instance that contains a database that has the same name as the on-premises database.

• Solution: If you need to overwrite the database in the instance with the on-premises database, you must back up the database, delete it from the instance, and migrate the on-premises database to the instance.

The file is a differential backup file.

• Error message: Backup set (xxx.bak) is a Database Differential backup, we only accept a FULL Backup.

• Cause: You uploaded a differential 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.

Failed to verify the backup file.

• 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 version than the ApsaraDB RDS SQL Server instance. For example, if you migrate an on-premises database of SQL Server 2016 to an ApsaraDB RDS 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 version is unmatched, you must use an instance of the same or later version. For example, you can migrate an on-premises database of SQL Server 2012 to an ApsaraDB RDS SQL Server 2016 instance.

DBCC CHECKDB fails

• Error message: DBCC checkdb failed

• Cause: Consistency errors and allocation errors occur in your databases.

• Solution:

  1. Execute the following statement in the on-premises database. 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

  2. Perform a full backup for the on-premises database.
  3. Upload the full backup file to the OSS bucket.
  4. Migrate the backup file in the ApsaraDB for RDS console.

The space is insufficient.

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

• Cause: The unused space of the 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 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 the ApsaraDB RDS SQL Server 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:

  1. Create a privileged account. For more information, see Create databases and accounts for an ApsaraDB for RDS instance running SQL Server 2019, 2017, 2016, or 2012.
  2. Log on to the destination database with the privileged account and authorize other accounts.

Related API operations