This topic describes how to migrate the data of an ApsaraDB RDS for SQL Server database to an on-premises SQL Server database by using a physical backup file.

You can also use Alibaba Cloud Data Transmission Service (DTS) to migrate the incremental data of an ApsaraDB RDS for SQL Server database to an on-premises SQL Server database.

Procedure

  1. Download the full and incremental physical backup files of the source database and upload them to the server where the destination database resides.

    For more information, see Download the data backup files and log backup files of an RDS SQL Server instance.

    If the server can communicate with the RDS instance on which the source database resides, you can run the wget "url" command to download the files. In this command, the url parameter specifies the URL from which you can download the files.

  2. 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
  3. 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
  4. Log on to the on-premises SQL Server console. Then, query the full and incremental physical backup files to obtain the logical names of the data and log files in the source database.
    restore filelistonly from disk='/tmp/testdb_datafull_201901071320.bak'  
    go

    In this example, the logical names of data and log files are testdb and testdb_log, respectively.

    Data and log files
  5. Load the full 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
    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 destination database 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, skip step 6. Perform step 6 only when you want to restore the data of the incremental physical backup file.
  6. Load the incremental physical backup file.
    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 incremental physical backup file is loaded, the status of the testdb database indicates that the database is being restored.

  7. Restore the testdb database.
    restore database testdb with recovery  
    go

    After the restoration is complete, the status of the testdb database indicates that the database is properly running.