Eksportuj (0) Drukuj
Rozwiń wszystko
EN
Ta zawartość nie jest dostępna w wymaganym języku. Wersja w języku angielskim znajduje się tutaj.

How to migrate SQL Server database files and schema between virtual machines in Azure using data disks

Updated: March 12, 2014

This topic explains how to migrate SQL Server database files and schema between virtual machines in Azure by using data disks. You might want to follow this migration path in one of the following two conditions:

  • You create a virtual machine by using the platform image SQL Server Evaluation Edition and the evaluation period is expired.

  • You want to move your databases to another virtual machine for any other business reasons.

This topic assumes that:

The following steps provide the basic workflow when migrating databases between virtual machines in Azure:

  1. Copy the data (.mdf, .ndf), log (.ldf), and backup (.bak) files to an additional data disk in the source virtual machine. Before copying the data (.mdf, .ndf) and log (.ldf) files, take the user databases offline and stop the instance of SQL Server for system databases.

  2. Detach the data disk from the source virtual machine. For more information, see How to detach a data disk from a virtual machine.

  3. Attach the data disk to the destination virtual machine. For more information, see How to attach a data disk to a virtual machine.

  4. Log on to the destination virtual machine to locate your database files in the destination virtual machine.

  5. Attach or restore your data and backup files to an instance of the database in the destination virtual machine.

Migrating user databases between virtual machines in Azure

There are several ways to migrate user databases between virtual machines in Azure. This section explains how to use attach/detach and backup/restore operations specifically. In addition, this section assumes that you have already followed the basic workflow that is given earlier in this topic.

  • Database Detach and Attach: You can copy the data (.mdf, .ndf), and log (.ldf) files in the destination virtual machine and then attach the user databases in the destination virtual machine. When performing database detach and attach operations, you can use the Data Compression feature to help compress the data inside a database, and to help reduce the size of the database. In addition, you can use separate compression and decompression tools on the detached files. For limitations when using detach and attach operations, see Option 3. Detach and Attach.

  • Database Backup and Restore: If the version of SQL Server in the destination virtual machine is the same or higher than the version of SQL Server in the source virtual machine, you can copy a database backup file to the destination virtual machine and then restore the database in the destination virtual machine. For more information, see Restore a database to a new location. For limitations, see Option 2. Backup and Restore. When backing up a database, you can control backup compression for an individual database, file, or log backup. For more information, see Backup Compression and Back up and Restore of SQL Server Databases.

There are also other tools or operations that you can use when migrating databases between virtual machines, such as Option 1: Data-tier Applications .BACPAC or .DACPAC files or Option 4. Other SQL Server Techniques.

Note: We recommend that you use the attach/detach operations when moving large user databases. When you attach a database onto another server instance or backup your databases to another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the other server instance. For more information, see Manage Metadata When Making a Database Available on Another Server Instance.

Migrating system databases between virtual machines in Azure

Just like user databases, we recommend that you keep your system databases in the attached disks in the source virtual machine as well. Using SQL Server Management Studio, you can perform a full backup of all your system databases and restore them in the new destination virtual machines.

The following links provide a detailed list of important considerations that you might consider when backing up and restoring system databases:

See Also

Zawartość społeczności

Dodaj
Pokaż:
© 2014 Microsoft