This topic describes how to migrate full backup data from an SQL Server database to ApsaraDB for MyBase by using backup sets.
- The self-managed SQL Server database runs one of the following versions:
- SQL Server 2017 Enterprise Edition.
- SQL Server 2012 Standard Edition, 2012 Enterprise Edition, 2014 Standard Edition, 2016 Standard Edition, 2016 Enterprise Edition, 2017 Standard Edition, and 2019 Standard Edition.
- SQL Server 2012 Enterprise Edition, Basic Edition, 2012 Web Edition, and 2016 Web Edition.
- The storage capacity of ApsaraDB for MyBase is sufficient. If the storage capacity of the destination cluster is insufficient, expand the storage capacity of the cluster before the migration. For more information, see Modify the configuration of an instance.
- An Object Storage Service (OSS) bucket is created. Make sure that the OSS bucket and the SQL Server instance in the ApsaraDB for MyBase cluster are deployed in the same region. For more information about how to create an OSS bucket, see Create buckets.
You can migrate only one database at a time. If you want to migrate multiple or all the databases at a time, we recommend that you use the instance-level migration solution. For more information, see Migrate data from a self-managed SQL Server instance to an ApsaraDB RDS for SQL Server instance.
Step 1: Perform a full backup for the on-premises database
- Make sure that no data is being written to the on-premises database. The data written during the backup process is not backed up.
- Full backup files can be migrated to ApsaraDB for MyBase. Differential backup files
or log backup files cannot be migrated to ApsaraDB for MyBase. For more information
about differential backup files, see Differential backup files. When you back up the on-premises database, make sure that the backup file is suffixed
- bak: specifies a full backup file.
- diff: specifies a differential backup file.
- trn or log: specifies a log backup file of transactions.
- The names of full backup files cannot contain special characters. For example, do
not use forward
slashes (/), asterisks (*), ampersands (&), dollar signs ($), percent signs (%), and at signs (@). Otherwise, the migration fails.
- Download the backup script file.
- Use SQL Server Management Studio (SSMS) to develop the backup script and configure
the following parameters:
backup_databases_list: The database to be backed up. If you want to back up multiple databases, separate the names of the databases with semicolons (;) or commas (,).
backup_type: The backup type. FULL indicates full backup. DIFF indicates differential backup. LOG indicates log backup.
is_run: The operation that is performed for backup. 1: performs a backup. 0: performs no backup but a check.
- Run the backup script.
Step 2: Upload the backup file to OSS
- Grant the database service account the permissions to access OSS. For more information, see Create an account and a database for an ApsaraDB RDS instance that runs SQL Server 2012, 2016, 2017 SE, or 2019 SE. After the permissions are granted, the system creates a role named AliyunRDSImportRole on the RAM Roles page.
- Do not modify or delete the AliyunRDSImportRole role. Otherwise, the migration fails because the backup file cannot be downloaded. If you modify or delete the role, you must grant the database service account the permissions to access OSS again.
- Method 1 (recommended): Upload the file by using the ossbrowser tool
- Download, install, and then log on to ossbrowser.
- Create and manage buckets and directories.
- Upload the file to the specified bucket or directory.
Note For more information about how to upload the file to the specified bucket or directory, see Upload backup files by using the ossbrowser tool.
- Method 2: Upload the file in the OSS console
If the backup file is smaller than 5 GB, you can upload the backup file in the OSS console. For more information, see Upload files to OSS.
- Method 3: Upload the file by calling OSS API operations
You can implement multipart upload and resumable upload for the backup file by calling OSS API operations. For more information, see Upload backup files by calling OSS API operations.
Step 3: Migrate the backup file to the destination database
- Log on to the ApsaraDB for MyBase console.
- In the top navigation bar, select the deployment region.
- Find the dedicated cluster where the instance is deployed and click Details in the Actions column.
- In the left-side navigation pane, click Instances. On the page that appears, click the ID of the instance to which the backup file is migrated.
- On the instance configuration page, click Backup and Restoration in the left-side navigation pane, and then click Migrate OSS Backup Data to RDS.
- In the Import Guide wizard, double-click next step and configure the following parameters.
Parameter Description Database Name The name of the destination database. OSS Bucket Select the OSS bucket where the backup file is stored. OSS Subfolder Name Enter the name of the subfolder where the backup file is stored. OSS File Select the backup file that you want to migrate to the destination database. You can enter the prefix of the backup file name, and click the search icon to search for the backup file in fuzzy match mode. Cloud Migration Method Select Immediate Access (Full Backup).Note
- Immediate Access (Full Backup): Select this option if only one full backup file needs to be migrated. Specify the
following parameters in
BackupMode = FULL
IsOnlineDB = True
- Access Pending (Incremental Backup): Select this option if you need to migrate full backup files, log backup files, or
differential backup files. Specify the following parameters in
BackupMode = UPDF
IsOnlineDB = False
Consistency Check Mode
- Synchronization is essential to your workloads. Therefore, we recommend that you select
Synchronize DBCC. When the destination database is opened, the system runs the DBCC CHECKDB command.
If you enable Synchronize DBCC, the time required to open the database increases.
A larger database requires a longer period of time to run the DBCC CHECKDB command.
Configure the following setting in
CheckDBMode = SyncExecuteDBCheck.
- If your business is sensitive to service downtime and synchronization does not affect
your business, we recommend that you select Asynchronize DBCC. When the database is opened, the system does not run the DBCC CHECKDB command. After
the database is opened, the system runs the DBCC CHECKDB command. If you enable Asynchronize
DBCC, the time that is required to open the database is reduced. This way, the service
downtime is reduced. Configure the following setting in
CheckDBMode = AsyncExecuteDBCheck.
- Immediate Access (Full Backup): Select this option if only one full backup file needs to be migrated. Specify the following parameters in
- Click OK.
What to do next
- Click Refresh to view the latest status of the data migration task.
- On the Backup and Restoration page, click the Backup Data Upload History tab to view the details about the migration task. By default, the details page displays the records of the previous week.