This topic describes how to migrate the full backup data of an SQL Server database to ApsaraDB for MyBase by using backup sets.
- The version of the self-managed SQL Server database is SQL Server 2008 R2.
- 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 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 (%), or at signs (@). Otherwise, the migration fails.
- Open the SQL Server Management Studio (SSMS) client and log on to the source database.
- Execute the following statement to check the recovery model of the database:
go select name, case recovery_model when 1 then 'FULL' when 2 then 'BULD_LOGGED' when 3 then 'SIMPLE' end model from sys.databases where name not in ('master','tempdb','model','msdb'); go
- Execute the following statements to set the recovery model of the database to Full:
ALTER DATABSE dbtest SET RECOVERY FULL; go ALTER DATABASE dbtest SET AUTO_CLOSE OFF; goNote
- If the value of the model in the returned result in Step 2 is Full, skip this step.
- After the recovery model is set to Full, a large amount of log information is generated for the SQL Server database. Make sure that your disk space is sufficient.
- Execute the following statements to back up the database:
use master; go BACKUP DATABASE dbtest to disk = 'd:\backup\backup.bak' WITH COMPRESSION, INIT; go
- Execute the following statement to verify the integrity of the backup file:
USE master GO RESTORE FILELISTONLY FROM DISK = N'D:\backup\backup.bak';Note
- If a result is returned, the backup file is valid.
- If an error message is returned, perform Step 4 again.
- Execute the following statement to restore the recovery model for the database:
ALTER DATABASE dbtest SET RECOVERY SIMPLE; goNote If the original recovery model of the database is Full, skip this step.
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 2008 R2. 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.
- Upload the full backup file to OSS. Note
- For more information about how to upload a file that is smaller than 5 GB, see Upload files to OSS.
- For more information about how to upload multiple files or a file that is larger than 5 GB, see Resumable upload. For more information about how to use the graphical management tool ossbrowser, see ossbrowser.
- Obtain the URL of the backup file.
- Log on to the OSS console. In the left-side navigation pane, click Buckets. In the bucket list, find the bucket where the backup file is stored. Click the bucket name to go to the bucket details page.
- On the Files page, click the name of the backup file for the destination database. The details panel appears.
- Click Copy File URL.
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 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 the DBCC CHECKDB result or the
database restoration result 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.