ApsaraDB RDS:Migrate data from an ApsaraDB RDS for SQL Server instance to a local SQL Server database

Prerequisites

Before you begin, make sure you have:

• Physical backup files created for the RDS instance. For details, see Physical backup

• Access to the self-managed SQL Server host to run T-SQL commands

How it works

The migration follows four phases:

1. Download the full and incremental physical backup files from the RDS console.

2. Upload and decompress the backup files on the self-managed SQL Server host.

3. Restore the database from the full backup (with NORECOVERY to keep it in a restoring state).

4. Optionally apply the incremental backup, then bring the database online with RECOVERY.

Download backup files

Download the full and incremental physical backup files from the RDS console. For the download steps and link format, see Download data backup files and log backup files.

If the self-managed SQL Server host can reach the RDS instance network, download the backup files directly on the host:

wget "<download-url>"

Replace <download-url> with the download URL of the backup files.

Upload and decompress backup files

1. Upload the downloaded backup files to the self-managed SQL Server host.

2. Decompress the full and incremental physical backup files.

   The full and incremental backup files have the same filename after decompression. Rename them before restoring to avoid confusion. Use the format <Database name>_<Backup method>_<Date>.bak. For example: - testdb_datafull_201901071320.bak — datafull indicates a full backup - testdb_datadiff_201901071330.bak — datadiff indicates an incremental backup

3. Note the file paths. For example:

   • Full backup: /tmp/testdb_datafull_201901071320.bak

   • Incremental backup: /tmp/testdb_datadiff_201901071330.bak

Query logical file names

Log in to the self-managed SQL Server database and run the following command to list the logical file names inside the backup file:

RESTORE FILELISTONLY FROM DISK='/tmp/testdb_datafull_201901071320.bak';
GO

The output shows the logical names for the data and log files. In this example:

• Data file logical name: testdb

• Log file logical name: testdb_log

Restore from full backup

Run the following command to restore the database from the full backup file and specify the target paths for the data and log files:

RESTORE DATABASE testdb FROM DISK = '/tmp/testdb_datafull_201901071320.bak' WITH REPLACE, NORECOVERY, STATS = 10,
MOVE 'testdb' TO '/var/opt/mssql/data/testdb.mdf',
MOVE 'testdb_log' TO '/var/opt/mssql/data/testdb_log.ldf';
GO

After the command completes, the testdb database status shows Restoring.

Path reference:

To find the target paths on your system, check Properties > Files of the destination database.

Apply incremental backup (optional)

Run the following command to apply the incremental backup on top of the full backup restore:

RESTORE DATABASE testdb FROM DISK = '/tmp/testdb_datadiff_201901071330.bak' WITH REPLACE, NORECOVERY, STATS = 10,
MOVE 'testdb' TO '/var/opt/mssql/data/testdb.mdf',
MOVE 'testdb_log' TO '/var/opt/mssql/data/testdb_log.ldf';
GO

After the command completes, the testdb database status shows Restoring.

Bring the database online

Run the following command to finalize the restore and bring the database to an available state:

RESTORE DATABASE testdb WITH RECOVERY;
GO

The testdb database is now online and accessible.