Migrate the data of an ApsaraDB RDS for SQL Server instance to a self-managed SQL Server instance - ApsaraDB RDS

This topic describes how to migrate the data of an ApsaraDB RDS for SQL Server instance to a self-managed SQL Server instance by using physical backup files.

Note

You can also use Data Transmission Service (DTS) to migrate the data of an ApsaraDB RDS for SQL Server instance to a self-managed SQL Server instance. For more information, see What is DTS?

Procedure

Download the full and incremental physical backup files of the RDS instance and upload them to the server in which the self-managed SQL Server instance resides. For more information about how to obtain backup files, see Download the data backup files and log backup files of an ApsaraDB RDS for SQL Server instance.
If the server can communicate with the RDS instance, you can run the wget "URL" command to download the backup files. In this command, the URL parameter specifies the URL from which you can download the backup files.
Decompress the full and incremental physical backup files that you downloaded.
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:
- /tmp/testdb_datafull_201901071320.bak: the save path of the full physical backup file
- /tmp/testdb_datadiff_201901071330.bak: the save path of the incremental physical backup file
Log on to the self-managed SQL Server instance. Then, query the full and incremental physical backup files to obtain the logical names of the data and log 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.
```
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
```
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 log on to the self-managed SQL Server instance and obtain the save paths of the data and log files from the file attributes.
After the full physical backup file is loaded, the status of the testdb database indicates that the database is being restored.
Note
If you only want to restore the data of the full physical backup file, go to Step 6. Perform Step 6 only when you want to restore the data of the incremental physical backup file.
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 full physical backup file is loaded, 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
```