今使っているSQL Serverのデータベースを下位バージョンのSQL Serverのデータベースとして復元(リストア)する方法について覚書。
例えば、SQL Server 2019のデータベースをSQL Server 2016のデータベースとして復元したいときなど。
下位バージョンへの復元
上位のバージョンのSQL Serverでバックアップしたファイルから下位バージョンのSQL Serverへ復元は基本的にできません。
復元しようとすると以下のようなエラーメッセージが表示されます。
上位のバージョンのデータベースを下位バージョンのデータベースとして復元する方法
通常SQL Serverのデータベースをバックアップする際は、[タスク] > [バックアップ]の機能を使ってバックアップをファイルとして作成しますが、上位のバージョンのSQL Serverでデータベースをバックアップする場合は「スクリプトの生成」機能を使用します。
以下に手順を記載します。
復元したいデータベースのバックアップ
[Microsoft SQL Server Management Studio]を起動し、下位バージョンに復元したいデータベースを右クリックし、[タスク] > [スクリプトの生成]をクリックします。
[スクリプトの生成]画面が表示されるので、[次へ]をクリックします。
[オブジェクトの選択]画面が表示されるので、[データベース全体とすべてのデータベース オブジェクトのスクリプトを作成]をオンにして[次へ]をクリックします。
[スクリプト作成オプションの設定]画面の[スクリプトの保存方法の指定。]から[詳細設定]ボタンをクリックします。
[スクリプト作成の詳細オプション]画面が表示されます。
[スクリプト作成の詳細オプション]画面のオプションから[サーバーのバージョン互換のスクリプト]のドロップダウンリストを表示して、復元したい下位バージョンのSQL Serverのバージョンを選択します。
ここでは、SQL Server 2019のデータベースをSQL Server 2016に復元すると仮定して「SQL Server 2016」を選択します。
さらにオプションから[スクリプトを作成するデータの種類]のドロップダウンリストを表示して「スキーマとデータ」を選択します。
[スクリプト作成の詳細オプション]画面の[OK]ボタンをクリックし、[スクリプト作成オプションの設定]画面に戻ります。
[スクリプトの保存方法の指定。]から[スクリプト ファイルとして保存]をオンにし、[ファイ名]の[...]ボタンをクリックします。
[スクリプト ファイルの場所]画面が表示されるので、任意のフォルダに任意のファイル名を指定して[保存]ボタンをクリックします。
ここでは、例として"northwind.sql"というファイル名で保存します。
[スクリプト作成オプションの設定]画面に戻るので[次へ]をクリックします。
設定した内容の確認画面が表示されるので[次へ]をクリックします。
スクリプトの生成処理が開始され、「結果」がすべて「成功」になっていることを確認したら[完了]をクリックします。
スクリプトファイルの保存先に指定したフォルダを確認し、スクリプトファイルが保存されていることを確認します。
下位バージョンのSQL Serverへのデータベースの復元
[Microsoft SQL Server Management Studio]を起動し、復元したい下位バージョンのSQL Serverに接続します。
保存したスクリプトファイルをダブルクリックします。
以下のように[Microsoft SQL Server Management Studio]にスクリプトファイルが開かれます。
表示されているスクリプトの以下の2行のパスを復元したい下位バージョンのSQL Serverの環境に合わせて修正します。
基本的には、以下のようになると思います。(XXは、バージョン番号)
SQL Serverのインストールパス¥MSSQLXX.MSSQLSERVER¥MSSQL¥DATA¥データベース名.mdf
SQL Serverのインストールパス¥MSSQLXX.MSSQLSERVER¥MSSQL¥DATA¥データベース名.ldf
になるかと思います。
ちなみに私の環境では、DドライブのProgramDataフォルダにインストールされているので、修正したパスは以下のようになります。
D:¥ProgramData¥Microsoft SQL Server¥MSSQL13.MSSQLSERVER¥MSSQL¥DATA¥データベース名.mdf
D:¥ProgramData¥Microsoft SQL Server¥MSSQL13.MSSQLSERVER¥MSSQL¥DATA¥データベース名.ldf
※SQL Server 2016のバージョン番号は、13です。
修正したらツールバーの[実行]ボタンをクリックします。
以下のように「(X 行処理されました)」など赤文字のエラーメッセージ以外が表示されて実行が終了、ステータスバーに「クエリが正常に実行されました」と表示されればOKです。
[Microsoft SQL Server Management Studio]の[データベース]フォルダを選択して[F5]キーを押して表示を最新化すればデータベースが復元されていることが確認できます。
IDENTITY(自動採番)列が定義されたテーブルのデータについて
今回の復元方法の場合、スクリプトの実行によりデータベースのスキーマ、データが復元されます。
テーブルのデータはInsert文によってデータが挿入されます。
通常IDENTITY列に値を指定してInsertすると以下のようなエラーメッセージが表示されてデータの挿入ができません。
「IDENTITY_INSERT が OFF に設定されているときは、テーブル 'TEST' の ID 列に明示的な値を挿入できません。」
例えば、テーブルのレコードのIDENTITY列に欠番があるような状態となっている場合、欠番は欠番として飛び飛びの番号でデータが挿入されるのか？
Insertによるデータの挿入でIDENTITY列の番号が振り直されたりしないのか？と疑問に思うかもしれないですが、生成されたスクリプトで「SET IDENTITY_INSERT」をONに指定してInsertしているので明示的な値を指定してInsertされるようになっています。
ということで特に不整合が発生したりはしないようです。あたりまえか。
まとめ
SQL Serverの上位バージョンのデータベースを下位バージョンに復元する方法について記事にしました。
結構気づかない機能なので状況に応じてぜひ活用してください。
コメント