はじめに
以前、同じサーバー(同一のSQL Serverインスタンス)間でデータベースの複製を行いました。
その際に バックアップ(コピー元) → 復元(コピー先) という手順で非常に簡単に複製することができました。
しかし、今回異なるサーバー間で複製しようとしたらハマったので備忘録として残しておきます。
環境
サーバーA：コピー元のデータベース(データベースAとします)がある
サーバーB：ここにデータベースAを複製したい
※サーバーA,BともにSQL Server Management Studio (SSMS) がインストールされている前提です
試したこと
コピー元サーバーでの操作
コピー元サーバーのSSMSからデータベースAで右クリック ⇒ タスク ⇒ バックアップ
コピーしたい データベース を選択 ⇒ バックアップの種類 を 完全 とする
バックアップ先 を選択し OK
指定のバックアップ先のフォルダに.bakファイルが作成されるためバックアップは完了
コピー先サーバーでの操作
コピー先サーバーのSSMSからコピー先としたいデータベースを右クリック ⇒ タスク ⇒ 復元 ⇒ Fileおよびファイルグループ
先ほど作成した.bakファイルを 復元元デバイス として選択し、 OK
同じサーバー(同一のSQL Serverインスタンス)間のコピーではこれで問題なく復元ができるはずです。
しかし異なるサーバー間だとエラーとなりました。
エラー内容
以下発生したエラー内容です。
サーバー '{コピー先サーバ名}' の復元に失敗しました。 (Microsoft.SqlServer.SmoExtended)
Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo)
バックアップ セットは、既存のデータベース '{コピー先データベース名}' 以外のデータベースのバックアップを保持しています。
RESTORE DATABASE が異常終了しています。 (Microsoft SQL Server、エラー: 3154)
どうやら.bakファイル内には既存のデータベース (コピー先データベース)とは異なるバックアップを保持している(当たり前といえば当たり前だが。)とのことです。
試しにコピー先データベース名を、コピー元データベース名と同一にして再度実行してみましたが、同じエラーが発生しました。
原因
調べてみるとどうやら、バックアップ機能では様々な制約があり、異なるサーバー間ではうまくいかないことがあるそうです。
まあそもそも”バックアップ”という名のついている機能なので、基本的には同じデータベースが障害などでクラッシュしたときの復元に使用する前提の機能だからだと考えています。
解決策
いろいろな方法があると思いますが、以下の方法が簡単でした。
まずコピー作業を、 テーブル定義 のコピーと中身の データ のコピーの２段階に分けます。
テーブル定義 はスクリプト機能を使用
データ はエクスポート機能を使用
一つずつ解説します。
テーブル定義のコピー
コピー元のデータベースを右クリック ⇒ タスク ⇒ スクリプトの生成
スクリプトの生成ウィザードが表示されるため、特定のデータベースオブジェクトを選択 を押下するとオブジェクトが選択できるようになるので、すべて選択 を押下しチェックを入れる
次にスクリプトの保存方法を決めます。
これはお好みの方法でどうぞ。
ここでは スクリプトファイルとして保存 を選択しています。
次に、詳細 ボタンを押下し詳細オプションを表示させます。
ここで、インデックスのスクリプトを作成 をTrueにしておきます。
ほとんどのテーブルでは検索速度を高めるためのインデックスが設定されていると思いますので、それも一緒に作成するようにしておきます。
そして生成されたスクリプトをコピー先のデータベースで実行すると
コピー元のテーブルやストアドなどが新たに作成されます。
ただし、このままではテーブル定義のみのコピーであり、中身のデータは空っぽのため次に解説するエクスポート機能でデータのコピーを実施します。
データのコピー
コピー元のデータベースを右クリック ⇒ タスク ⇒ エクスポート
エクスポートウィザードが表示されるため、コピー元とコピー先の情報を入力し指示通りにエクスポートを実施します。
以上の操作でデータベースそのもののコピーが完了します。
まとめ：
異なるサーバー間でのデータベース移行ではバックアップ機能は制約があり使えない。
スクリプト機能とエクスポート機能を使用すると簡単にデータベースのコピーができることがわかりました。