Migrate RDS for SQL Server data to the local SQL Server database

Last Updated: Feb 16, 2017

RDS for MySQL supports the migration of cloud data to the local database by using physical backup files.

Operation procedure

  1. Download the full and incremental physical backup files of RDS and upload the files to the target server.

    For details about how to obtain the backup file, refer to Downloading Backup Data.

    If the target server can access the source instance, you can use wegt "url" to download the backup file.url indicates the backup file download address.

  2. After download, decompress the full physical backup file and incremental physical backup file.

    A backup file is named in the format of database name+backup type+date and time+task ID.bak, of which backup type may be one of the following:

    • datafull: Specifies full backup, such as rdsumu2myfzbeai1_datafull_201402250050_2250050.bak.

    • datadiff: Specifies incremental backup, such as rdsumu2myfzbeai1_datadiff_201402260050_2260050.bak.

    • log: Specifies log backup, such as rdsumu2myfzbeai1_log_201402260050_2260050.bak.

  3. Obtain the decompressed full backup file and incremental backup file. This example assumes that the backup files are stored in the following paths:

    • Path to the full backup file: d:\backup\rdsumu2myfzbeai1_datafull_201402250050_2250050.bak

    • Path to the incremental backup file: d:\backup\rdsumu2myfzbeai1_datadiff_201402260050_2260050.bak

  4. Log on to the local SQL Server Console and query the logical names of the RDS files based on the backup files.

    1. restore filelistonly from disk='d:\backup\rdsumu2myfzbeai1_datafull_201402250050_2250050.bak'
    2. go

    The system displays the following information, where the logical name of the data file is data1 and that of the log file is log:

    Logical Names of Files

  5. Load the full backup file.

    1. restore database rdsumu2myfzbeai1 from disk='d:\backup\rdsumu2myfzbeai1_datafull_201402250050_2250050.bak' with replace,norecovery,stats=10,
    2. move 'data1' to 'd:\database\rdsumu2myfzbeai1\data\data1.mdf',
    3. move 'log' to 'd:\database\rdsumu2myfzbeai1\log\log.ldf'
    4. go

    Parameters description:

    • d:\database\rdsumu2myfzbeai1\data is the data address, and data1.mdf is the logical name of the data file

    • d:\database\rdsumu2myfzbeai1\log is the log address, and log.ldf is the logical name of the log file

      After the script is executed, the database rdsumu2myfzbeai1 will be in Recovering state.

      NOTE: If you only want to recover full backup data, skip Step 6 and proceed to Step 7. If you also want to recover incremental backup data, perform Step 6.

  6. Load the incremental backup file.

    1. restore database rdsumu2myfzbeai1 from disk='D:\backup\rdsumu2myfzbeai1_datadiff_201402260050_2260050.bak' with replace,norecovery,stats=10,
    2. move 'data1' to 'd:\database\rdsumu2myfzbeai1\data\data1.mdf',
    3. move 'log' to 'd:\database\rdsumu2myfzbeai1\log\log.ldf'
    4. go

    After the script is executed, the database rdsumu2myfzbeai1 will be in Recovering state.

  7. Recover the database.

    1. restore database rdsumu2myfzbeai1 with recovery
    2. go

    After the script is executed, the database rdsumu2myfzbeai1 will be available.

Thank you! We've received your feedback.