This article describes how to migrate full backup data to RDS SQL Server 2012/2016.
- 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.
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.
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.
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.
Log on to OSS console.
Click the + sign on the left pane.
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.
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 errors, fix them before the migration.
Only three steps are required to migrate a local database to RDS SQL Server 2012/2016 on the cloud:
Back up local database
Upload the backup file to OSS
Create the migration task
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:
Download the backup script and open it with SSMS.
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.
Run the backup script.
Upload the backup file to your OSS bucket.
It is recommended that you use the ossbrowser tool to upload the backup file to OSS. For more information, see ossbrowser.
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.
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
Log on to the RDS console.
Select the region where the target instance is located.
Click the target instance ID.
On the left-side navigation pane, choose Backup and Recovery.
Click OSS Backup Data Upload at the upper right corner.
If you are using the function for the first time, authorize the RDS offical service account to access OSS:
- Click Authorize in the data import wizard:
- Click Confirm Authorization Policy.
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.
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.
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.
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.
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
Perform a full backup for the database again.
Upload the new database file to OSS.
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.
Solution 1: Set the download link validity period to a larger value (at most 18 hours). See the following figure.
Solution 2: Set the ACL permission of the OSS database backup file to Public Read. See the following figure.
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.
Create an initial account. For details, see steps 1 to 7 in Create account and database for SQL Server 2012 and 2016.
Reset the password of the initial account. For more information, see Reset instance password.
Use the initial account to access the database on the cloud.