ApsaraDB RDS:Migrate incremental backup data (for SQL Server 2008 R2 with cloud disks and SQL Server 2012 or later)

How it works

The migration runs in two phases:

1. Full data migration: Back up the source database, upload the full backup file to an OSS bucket, and restore it to the RDS instance via the ApsaraDB RDS console.

2. Incremental phase: Repeatedly back up and upload log or differential backup files to narrow the data gap. When the last log backup is under 500 MB, stop writes to the source database, upload the final log backup, then open the destination database.

Prerequisites

Self-managed SQL Server instance:

• Uses the FULL recovery model. The SIMPLE recovery model does not support transaction log backups, which are required for incremental migration.

• Run DBCC CHECKDB on the source database and confirm that no allocation errors or consistency errors are reported:

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

RDS instance:

• Runs SQL Server 2012 or later, or SQL Server 2008 R2 with cloud disks.

• No existing database on the RDS instance shares the same name as the source database, and no unattached database files with the same name exist. A naming conflict causes the restore to fail.

• Available storage is greater than the size of the data files to migrate. Expand the storage capacity before starting if needed.

Authorization:

The ApsaraDB RDS service account must have access to your OSS bucket. To verify and grant authorization:

1. On the Backup and Restoration page of your RDS instance, click Migrate OSS Backup Data to RDS.

2. In the Import Guide wizard, click Next twice to reach step 3. Import Data.

3. Check the lower-left corner of the page. If You have authorized RDS official service account to access your OSS is displayed, authorization is complete. If not, click the Authorization URL link to grant authorization.

If using a Resource Access Management (RAM) user, the following additional requirements apply:

• The RAM user has the AliyunOSSFullAccess and AliyunRDSFullAccess permissions. See Manage OSS permissions using RAM and Manage ApsaraDB RDS permissions using RAM.

• Your Alibaba Cloud account has created a custom access policy and attached it to the RAM user:

  {
      "Version": "1",
      "Statement": [
          {
              "Action": [
                  "ram:GetRole"
              ],
              "Resource": "acs:ram:*:*:role/AliyunRDSImportRole",
              "Effect": "Allow"
          }
      ]
  }

Usage notes

Migration timeline example

The following example shows how to complete the migration with less than 5 minutes of application downtime.

Step 1: Back up the source database

1. Download the backup script and open it in SQL Server Management Studio (SSMS).

2. Set the following parameters:

   Parameter	Description
   @backup_databases_list	Name of the source database. Separate multiple database names with semicolons (;) or commas (,).
   @backup_type	Backup type: FULL (full backup), DIFF (differential backup), or LOG (log backup).
   @backup_folder	Directory on the self-managed instance to store backup files. The directory is created automatically if it does not exist.
   @is_run	1 to perform the backup; 0 to run a check only.

3. Run the backup script. A .bak file is generated regardless of the backup type.

Step 2: Upload backup files to OSS

Backup files must be in an OSS bucket in the same region as your RDS instance. Same-region transfer uses the internal network, avoiding internet egress fees and improving upload speed.

import com.aliyun.oss.*;
import com.aliyun.oss.common.auth.*;
import com.aliyun.oss.common.comm.SignVersion;
import com.aliyun.oss.internal.Mimetypes;
import com.aliyun.oss.model.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class Demo {

    public static void main(String[] args) throws Exception {
        // Replace with the endpoint for your bucket's region.
        String endpoint = "https://oss-cn-hangzhou.aliyuncs.com";
        // Credentials are read from environment variables OSS_ACCESS_KEY_ID and OSS_ACCESS_KEY_SECRET.
        EnvironmentVariableCredentialsProvider credentialsProvider = CredentialsProviderFactory.newEnvironmentVariableCredentialsProvider();
        String bucketName = "examplebucket";
        // Full object path within the bucket, excluding the bucket name.
        String objectName = "exampledir/exampleobject.txt";
        // Full local path of the backup file to upload.
        String filePath = "D:\\localpath\\examplefile.txt";
        // Region identifier for the bucket, for example "cn-hangzhou".
        String region = "cn-hangzhou";

        ClientBuilderConfiguration clientBuilderConfiguration = new ClientBuilderConfiguration();
        clientBuilderConfiguration.setSignatureVersion(SignVersion.V4);
        OSS ossClient = OSSClientBuilder.create()
                .endpoint(endpoint)
                .credentialsProvider(credentialsProvider)
                .clientConfiguration(clientBuilderConfiguration)
                .region(region)
                .build();

        try {
            // Initiate the multipart upload.
            InitiateMultipartUploadRequest request = new InitiateMultipartUploadRequest(bucketName, objectName);
            ObjectMetadata metadata = new ObjectMetadata();
            if (metadata.getContentType() == null) {
                metadata.setContentType(Mimetypes.getInstance().getMimetype(new File(filePath), objectName));
            }
            request.setObjectMetadata(metadata);
            InitiateMultipartUploadResult upresult = ossClient.initiateMultipartUpload(request);
            String uploadId = upresult.getUploadId();

            List<PartETag> partETags = new ArrayList<PartETag>();
            // Each part is 1 MB; adjust based on your file size and network conditions.
            final long partSize = 1 * 1024 * 1024L;

            final File sampleFile = new File(filePath);
            long fileLength = sampleFile.length();
            int partCount = (int) (fileLength / partSize);
            if (fileLength % partSize != 0) {
                partCount++;
            }

            // Upload each part sequentially.
            for (int i = 0; i < partCount; i++) {
                long startPos = i * partSize;
                long curPartSize = (i + 1 == partCount) ? (fileLength - startPos) : partSize;
                UploadPartRequest uploadPartRequest = new UploadPartRequest();
                uploadPartRequest.setBucketName(bucketName);
                uploadPartRequest.setKey(objectName);
                uploadPartRequest.setUploadId(uploadId);
                InputStream instream = new FileInputStream(sampleFile);
                instream.skip(startPos);
                uploadPartRequest.setInputStream(instream);
                // The last part can be smaller than 100 KB; all other parts must be at least 100 KB.
                uploadPartRequest.setPartSize(curPartSize);
                // Part numbers range from 1 to 10,000.
                uploadPartRequest.setPartNumber(i + 1);
                UploadPartResult uploadPartResult = ossClient.uploadPart(uploadPartRequest);
                partETags.add(uploadPartResult.getPartETag());
                instream.close();
            }

            // Complete the upload. OSS assembles all parts in order.
            CompleteMultipartUploadRequest completeMultipartUploadRequest =
                    new CompleteMultipartUploadRequest(bucketName, objectName, uploadId, partETags);
            CompleteMultipartUploadResult completeMultipartUploadResult = ossClient.completeMultipartUpload(completeMultipartUploadRequest);
            System.out.println("Upload successful, ETag: " + completeMultipartUploadResult.getETag());

        } catch (OSSException oe) {
            System.out.println("OSS rejected the request: " + oe.getErrorMessage()
                    + " (Code: " + oe.getErrorCode() + ", Request ID: " + oe.getRequestId() + ")");
        } catch (ClientException ce) {
            System.out.println("Client error communicating with OSS: " + ce.getMessage());
        } finally {
            if (ossClient != null) {
                ossClient.shutdown();
            }
        }
    }
}

Step 3: Create a migration task

1. Go to the Instances page. In the top navigation bar, select the region of your RDS instance, then click the instance ID.

2. In the left navigation pane, click Backup and Restoration.

3. At the top of the page, click Migrate OSS Backup Data to RDS.

4. In the Import Guide wizard, click Next twice to reach the Import Data step.

   If this is your first time using the migration wizard, click Authorization and complete the authorization process. Without authorization, the OSS Bucket drop-down list remains empty.

5. Configure the following parameters, then click OK.

   Parameter	Description
   Database name	Name of the destination database on your RDS instance. Must not conflict with any existing database or unattached database files on the instance.
   OSS bucket	The OSS bucket containing your full backup file.
   OSS file	Click the magnifying glass icon to search for the backup file by filename prefix. Results show the filename, size, and last modified time.
   Cloud migration method	Select Access Pending (Incremental Backup). This keeps the database in a restoring state so incremental backups can be applied (BackupMode = UPDF, IsOnlineDB = False). Do not select Immediate Access (Full Backup) — that option opens the database immediately after the full backup and does not accept incremental backups (BackupMode = FULL, IsOnlineDB = True).

6. Click Refresh to monitor the task status. If the task fails, see Troubleshooting.

Step 4: Import log or differential backup files

After the full backup is restored, apply incremental backups to close the data gap.

1. Go to the Instances page, select your region, and click the instance ID.

2. In the left navigation pane, click Backup and Restoration, then click the Cloud Migration Records of Backup Data tab.

3. Find the destination database and click Upload Incremental Files in the Task actions column. Select the incremental file and click OK.

Repeat this step for each log backup file, uploading them in chronological order.

Keep the last log or differential backup file under 500 MB. Stop all writes to the source database before generating the final backup to ensure data consistency.

Step 5: Open the database

After all backup files are imported, open the destination database to make it available for reads and writes.

1. Go to the Instances page, select your region, and click the instance ID.

2. In the left navigation pane, click Backup and Restoration, then click the Cloud Migration Records of Backup Data tab.

3. Find the destination database and click Open Database in the Task actions column.

4. Select a consistency check mode and click OK.

   Mode	Behavior	Use when
   Asynchronous DBCC	Opens the database immediately, then runs DBCC CHECKDB in the background. Minimizes downtime. (CheckDBMode = AsyncExecuteDBCheck)	The application is sensitive to downtime but the consistency check result is not immediately critical.
   Synchronous DBCC	Runs DBCC CHECKDB before opening the database. Increases time to open. (CheckDBMode = SyncExecuteDBCheck)	Consistency check results are required before the database goes live.

Step 6: View migration records

Go to Backup and Restoration > Cloud Migration Records of Backup Data to review all migration tasks. Click View File Details in the Task actions column to see the status and details of every backup file associated with a task.

After migration is complete, ApsaraDB RDS automatically backs up the database according to the automatic backup policy configured for your instance. To trigger an immediate backup, run a manual backup.

Troubleshooting

For errors that occur during full backup migration, see Troubleshooting in this topic.

The following errors are specific to incremental backup migration.

The destination database fails to open

*Is the source SQL Server edition higher than the target RDS edition?*

Error message: Failed to open database xxx.

The self-managed SQL Server instance uses features (such as data compression or table partitioning) not supported by the target RDS SQL Server edition. For example, if the source runs Enterprise Edition and the destination runs Web Edition, Enterprise-only features cause this error.

Solutions:

• Disable the unsupported features on the self-managed instance, back up the database again, and retry the migration.

• Switch to an RDS instance that runs the same SQL Server edition as the source. See Create and use an ApsaraDB RDS for SQL Server instance and Features supported by different SQL Server versions and editions.

LSN mismatch in the backup chain

*Were incremental backups uploaded out of chronological order?*

Error message: The log in this backup set begins at LSN XXX, which is too recent to apply to the database. RESTORE LOG is terminating abnormally.

The log sequence numbers (LSNs) in the incremental backup file do not continue from the previous backup. This happens when incremental files are applied to the wrong base backup or uploaded out of order.

Solution: Upload incremental backup files in strict chronological order, matching the sequence in which they were created.

Consistency errors found after Asynchronous DBCC

*Did DBCC CHECKDB report consistency errors on the destination database?*

Error message: asynchronously DBCC checkdb failed: CHECKDB found 0 allocation errors and 2 consistency errors in table 'XXX' (object ID XXX).

The background consistency check found errors in the destination database, indicating corruption in the source data.

Solutions:

• Run the following on the destination database to repair errors (this may result in data loss):

  Important

  This statement may cause data loss. Use only if acceptable.

  DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS)

• Alternatively, fix the source database first, then re-migrate:

  DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS)

A full backup file was selected when an incremental file is expected

*Did you select a full backup file in the Upload Incremental Files step?*

Error message: Backup set (xxx) is a Database FULL backup, we only accept transaction log or differential backup.

After the full backup is restored, only log or differential backup files are accepted in the Upload Incremental Files step.

Solution: Select a log or differential backup file.

Database count limit exceeded

*Has the RDS instance reached its maximum number of databases?*

Error message: The database (xxx) migration failed due to databases count limitation.

Solution: Migrate to another RDS instance, or delete databases that are no longer needed.

RAM user permission issues

*Is the OK button dimmed when configuring the migration task?*

The RAM user likely does not have the required permissions. Verify that AliyunOSSFullAccess, AliyunRDSFullAccess, and the custom AliyunRDSImportRole policy are all granted (see Prerequisites).

*Does the RAM user get a no permission error when granting the AliyunRDSImportRole permission?*

API reference