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

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

Prerequisites

Physical backup files have been created for the ApsaraDB RDS for SQL Server instance. For more information, see Physical backup.

Note

You can also use Data Transmission Service (DTS) to migrate physical backup files or snapshot backup files from the cloud to a self-managed SQL Server instance (with ApsaraDB RDS for SQL Server as the source and self-managed SQL Server as the destination).

Procedure

Download the full and incremental physical backup files of the ApsaraDB RDS for SQL Server instance.
If the destination server can access the source instance, you can also use wget "URL" to download the backup files. The URL parameter specifies the download URL of the backup files.
Upload the downloaded backup files to the server where your self-managed SQL Server is located, and decompress the full and incremental physical backup files.
Note
The full and incremental physical backup files have the same name after decompression. We recommend that you rename the files in the following format: <Database name>+<Backup method>+<Date>. Examples:
- testdb_datafull_201901071320.bak, where datafull indicates a full backup.
- testdb_datadiff_201901071330.bak, where datadiff indicates an incremental backup.
Obtain the decompressed full and incremental physical backup files. Examples of the file save paths:
- Full backup file save path: /tmp/testdb_datafull_201901071320.bak
- Incremental backup file save path: /tmp/testdb_datadiff_201901071330.bak
Log on to your local self-managed SQL Server database and execute the following command to query the logical file names in the backup files:
```
RESTORE FILELISTONLY FROM DISK='/tmp/testdb_datafull_201901071320.bak';
GO
```
In this example, the logical name of a data file is testdb and that of a log file is testdb_log.
Restore the database from the full backup file and specify the save paths of the data and log files.
Important
If you only want to restore the data of the full physical backup file, after completing this step, go directly to Step 7. Perform Step 6 only when you want to restore the data of the incremental physical backup file.
```
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 is executed, the status of the testdb database indicates that the database is being restored.
Note
- /var/opt/mssql/data/testdb.mdf is the save path of the data file, and testdb.mdf is the logical name of the data file.
- /var/opt/mssql/data/testdb_log.ldf is the save path of the log file, and testdb_log.ldf is the logical name of the log file.
You can view the save paths of the data and log files in the Properties > Files of the destination database.

(Optional) Restore the database from the incremental backup file and specify the save paths of 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 the command is executed, the status of the testdb database indicates that the database is being restored.

Execute the following statement to restore the database to the available state:
```
RESTORE DATABASE testdb WITH RECOVERY;
GO
```