SQL Server においてサーバー間の全データ移行の手順

環境

SQL Server

• SQL Server 2019
• SQL Server 2012

データ移行の方法とそれぞれのメリット・デメリット

SQL Server のデータベースのデータを他のサーバーの SQL Server に一括で移行する方法は以下の数種類が存在します。 それぞれ移行の際のメリット・デメリットがありますので、環境の応じて最適な方法を選んでください。

前提条件

• それぞれのサーバーに SQL Server と SQL Server Management Studio がインストールされているものとします。
• 今回の Tips はあえて SQL Server のバージョンを異なるものにしています。

サンプルデータの作成

データ移行の説明の際、以下のテーブル構成のデータベースで行います。

データ移行元

SQL Server 2012

• テーブル1 (列：キー, 値)
• テーブル2 (列：キー, 値, 備考)
• テーブル3 (列：キー, 値)

以下はデータベース作成スクリプトです。パスは適時変えてください。

USE [master]
GO

CREATE DATABASE [SampleDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SampleDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SampleDatabase.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SampleDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SampleDatabase_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

以下はテーブル作成スクリプトです。

USE [SampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル1](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル2](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
	[備考] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル2] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル3](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル3] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

以下はレコード追加スクリプトです。

USE [SampleDatabase]
GO
INSERT [dbo].[テーブル1] ([キー], [値]) VALUES (1, N'1aaa')
GO
INSERT [dbo].[テーブル1] ([キー], [値]) VALUES (2, N'1bbb')
GO
INSERT [dbo].[テーブル2] ([キー], [値], [備考]) VALUES (1, N'2aaa', N'2remarks')
GO
INSERT [dbo].[テーブル2] ([キー], [値], [備考]) VALUES (2, N'2bbb', N'2remarks')
GO
INSERT [dbo].[テーブル3] ([キー], [値]) VALUES (1, N'3aaa')
GO
INSERT [dbo].[テーブル3] ([キー], [値]) VALUES (2, N'3bbb')
GO

データ移行先

SQL Server 2019

• テーブル1 (列：キー, 値)
• テーブル2 (列：キー, 値)

各テーブルのレコードは空です。

以下はデータベース作成スクリプトです。パスは適時変えてください。

USE [master]
GO

CREATE DATABASE [SampleDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SampleDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SampleDatabase.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'SampleDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SampleDatabase_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

以下はテーブル作成スクリプトです。

USE [SampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル1](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル2](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

データベースのバックアップと復元によるデータ移行の方法

この方法は移行元サーバーでデータベースをファイルとして丸ごとバックアップし、移行先サーバーにコピーして取り込む方法となります。 そのため、テーブル構成もすべて移行元と同じものが移行先に設定されます。

また、この方法は SQL Server の新しいバージョンから古いバージョンへの移行はできません。(バージョン差が小さければできることもあります)

手順

まず移行元で SQL Server Management Studio を起動します。フォルダについては SQL Server のバージョンに合わせて選択してください。

対象のサーバーにログインします。SQL Server Management Studio を使う PC は他の PC でも問題ありませんが、本 Tips では全て自身のサーバーで操作しています。

オブジェクト エクスプローラーから対象のデータベースを右クリックし「タスク」の「バックアップ」を選択します。

右下にあるバックアップ先一覧に初期表示されているバックアップパスを削除します。

削除したら追加ボタンをクリックします。

パス変更ボタンをクリックします。

初期状態で「Backup」フォルダが選択されていると思いますので、下のファイル名にバックアップするファイル名を入力して OK ボタンをクリックします。 ファイル名は任意なので分かりやすい名前にしてください。

OK ボタンをクリックします。

一覧に追加されていることを確認し OK ボタンをクリックします。

以下のフォルダにバックアップファイルが作成されているので、このファイルを移行先のサーバーに持っていきます。 フォルダパスについては SQL Server のバージョンやインスタンス名によって変わるので適時確認してください。

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup

移行先サーバーにコピーした後の状態です。SQL Server の Backup フォルダに入れた方が読み込みやすいので以下のフォルダに入れています。 こちらも SQL Server のバージョンやインスタンス名によって変わるので適時確認してください。

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup

移行先のサーバーで SQL Server Management Studio を起動します。ここでは自身のサーバーにログインしています。

オブジェクト エクスプローラーから移行先のデータベースを右クリックし、「タスク」「復元」「データベース」を選択します。

左のページの選択で「全般」が選択されていることを確認し、右の「ソース」から「デバイス」を選択、右にある参照ボタンをクリックします。

追加ボタンをクリックします。

持ってきたファイルを選択します。

左のページの選択から「オプション」を選択し、以下の２つにチェックをいれます。 この２つにチェックを入れると、他の何らかのシステムがデータベースに接続中でも強制的に入れ替えることが可能です。

• 既存のデータベースを上書きする
• 接続先データベースへの既存の接続を閉じる

ただし「接続先データベースへの既存の接続を閉じる」にチェックを入れている状態で復元に失敗すると元のデータベースが使用できなくなる場合がありますので注意してください。

チェックをいれたら OK ボタンをクリックします。

テーブル構成やレコードが完全に移行されていることを確認できます。

ちなみに、新しいバージョンの SQL Server のデータベースを古いバージョンの SQL Server に復元しようとすると以下のエラーが表示されます。 この方法によるデータ移行を行う場合は、同じバージョン同士か、新しいバージョンへの移行が基本となります。

データを SQL (スクリプト) として出力し、移行先のデータベースで実行する方法

テーブル内のレコード全てを insert 文として出力する方法です。 SQL 文はほぼ共通なので SQL Server のバージョンに関わらず、場合によっては他の種類のデータベースにも適用することが可能です。

ただしレコードを１つ１つ insert 文に書き出すのでファイルサイズが大きくなります。 そのためレコード数が増えていくと使い物にならないため、件数が少ない場合に使える方法となっています。

また、移行元のテーブルに合わせて SQL が生成されるため、移行先のテーブル構成と異なる場合は使えません。 一応テーブルの作成も SQL に含めることができますが、その場合は移行先でいったんテーブルを削除する必要があります。

手順

まず移行元で SQL Server Management Studio を起動します。フォルダについては SQL Server のバージョンに合わせて選択してください。

対象のサーバーにログインします。SQL Server Management Studio を使う PC は他の PC でも問題ありませんが、本 Tips では全て自身のサーバーで操作しています。

対象のデータベースを右クリックして「タスク」「スクリプトの生成」を選択します。

「次へ」をクリックします。

どのテーブルのデータを移行するかを選択することができます。ここでは全てを選択するか特定のテーブルを選択してください。

出力されるのは SQL なので「ファイル」「クリップボード」「ウィンドウに表示」のいずれかを選択可能です。 選択したら右上の「詳細設定」ボタンをクリックします。

全般グループにある「スクリプトを生成するデータの種類」から「データのみ」を選択します。

あとは「次へ」を選択していって確定します。すると SQL が生成されることを確認できると思います。 後はこの SQL を移行先で実行するだけです。

ただの insert 文ですので、データを同じにしたい場合はあらかじめ移行先で全レコードを削除しておく必要があります。

テーブル構成も移行する

テーブル構成も移行する場合は、先ほどの「スクリプトを生成するデータの種類」の選択肢を「スキーマとデータ」に設定します。

すると以下のようにテーブルの作成スクリプトも生成されます。 これを利用する場合は移行先のテーブルをいったん削除してから実行する形となります。

また、データベースも対象に含めている場合はデータベースの作成スクリプトも生成されます。

「データのインポートおよびエクスポート」を使用したデータ移行方法

SQL Server をインストールすると一緒に「データのインポートおよびエクスポート」というツールもインストールされます。 これを使用すると別サーバーに対してテーブルのデータを移行することができます。

このツールによる移行では古いバージョンの SQL Server にもデータを移行できるのがメリットです。 やや使い勝手は難しいですが、１００万件程度のデータでも数分レベルで移行可能なので大容量のデータ移行にも向いています。 ただし、移行元と移行先のサーバーはプライベートネットワークで接続されている必要があり、ネットワークが全く異なるサーバー間では使えませんので注意してください。

前提条件

このツールを使用する PC からそれぞれの SQL Server にアクセスできるように SQL Server 認証、または Windows 認証を構築してください。

本 Tips では移行元のサーバーからこのツールを使用しています。

手順

スタートメニューから「データのインポートおよびエクスポート」を選択します。 SQL Server のフォルダに含まれていますが、SQL Server のバージョンによってフォルダ名が異なりますので、適時探してください。

次へをクリックします。

移行元のデータベースを選択します。今回は移行元のサーバーで実行しているので Windows 認証で通すことができます。

次に移行先データベースの接続設定を行います。別サーバーにあるので SQL Server 認証を使用しています。

「１つ以上のテーブルまたはビューからデータをコピーする」にチェックをいれて次へボタンをクリックします。

移行するテーブルを選択します。この一覧にはビューも表示されてしまうので、ビューにはチェックしないようにします。

移行元と移行先のテーブル名が同じ場合は、移行先のテーブルが自動選択されます。 また、列名が同じ場合は列名のマッピングも自動的に設定されます。

ここの設定で注意する点としては以下のものがあります。

• プライマリキーが自動付番の場合は、「マッピングの編集」から「ID 挿入を許可する」にチェックを入れないと移行に失敗する
• 移行先にテーブルがない場合はテーブルが作成される
• 移行先テーブルに同じ列名がない場合は移行されません。そのまま移行しないか、移行先列を変更します。
• レコードは初期設定では追加となります。入れ替えにする場合は事前に移行先でレコードを削除するか、「マッピングの編集」から「変換先テーブル内の行を削除する」にチェックを入れます

実際には上記を考慮しなければいけませんが、本 Tips では移行先にデータがなく、自動付番もないので何も設定せずに進みます。

「すぐに実行する」にチェックが入っていることを確認して「次へ」をクリックします。

「完了」をクリックします。

データ移行が始まります。すべての移行が完了したら閉じます。

移行先のデータベースを確認すると存在しなかったテーブルが作成されていることを確認できます。 また、既存のテーブルについては列が増えたりしていないことを確認できます。

データが移行されていることを確認できます。テーブル2 の備考列についてはマッピングしていないので移行されていません。