Migrate ApsaraDB RDS for SQL Server to on-premises SQL Server-ApsaraDB RDS(RDS)-阿里云帮助中心

This topic describes how to migrate data from an ApsaraDB RDS for SQL Server instance to a self-managed SQL Server instance. You must download the full and incremental physical backup files from the ApsaraDB RDS console, upload them to the server that hosts your self-managed SQL Server instance, decompress the files, and restore the database.

Prerequisites

Ensure you have a complete physical backup file of your ApsaraDB RDS for SQL Server instance.

Note

You can also use Data Transmission Service (DTS) to migrate physical backup files or snapshots. In DTS, configure ApsaraDB RDS for SQL Server as the source and your self-managed SQL Server instance as the destination.

Procedure

Download the full and incremental physical backup files from your ApsaraDB RDS for SQL Server instance.

If the target server can access the source instance, you can also download the backup files using the wget "URL" command. Replace "URL" with the backup file download URL.
Upload the downloaded backup files to the server that hosts the self-managed SQL Server instance, and decompress the full and incremental backup files.
Note
After decompression, the full and incremental backup files have the same file name. For easier maintenance, we recommend that you rename the files in the format <Database name>_<Backup method>_<Date>. For example:
- testdb_datafull_201901071320.bak, where datafull indicates a full backup.
- testdb_datadiff_201901071330.bak, where datadiff indicates an incremental backup.
Note the paths of the decompressed full and incremental backup files. This topic uses the following paths as an example:
- Path to the full backup file: /tmp/testdb_datafull_201901071320.bak
- Path to the incremental backup file: /tmp/testdb_datadiff_201901071330.bak
Log on to the self-managed SQL Server instance and run the following statement to query the logical file names in the backup file:
```
RESTORE FILELISTONLY FROM DISK='/tmp/testdb_datafull_201901071320.bak';
GO
```
The query result shows that the logical file name of the data file is 'testdb' and the logical file name of the log file is 'testdb_log'.
Restore the database from the full backup file and specify the storage locations for the data and log files.

Important
If you need to restore an incremental backup, proceed to Step 6. If you are only restoring the full backup, skip to Step 7.
```
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 you run this statement, the testdb database is in the restoring state.
Note
- /var/opt/mssql/data/testdb.mdf is the path to the data file, and testdb.mdf is the logical file name of the data file.
- /var/opt/mssql/data/testdb_log.ldf is the path to the log file, and testdb_log.ldf is the logical file name of the log file.
You can view the data file and log file paths in the Properties > Files section of the target database.

(Optional) Restore the database from the incremental backup file and specify the storage locations for the data and log files.

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 you run this statement, the testdb database is in the restoring state.

Run the following statement to recover the database and bring it online.
```
RESTORE DATABASE testdb WITH RECOVERY;
GO
```