新しいバージョンのSQL Serverのデータベースを古いバージョンのSQL Serverに復元する方法

2021年11月1日

今使っている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の上位バージョンのデータベースを下位バージョンに復元する方法について記事にしました。

結構気づかない機能なので状況に応じてぜひ活用してください。

(Visited 3,560 times, 696 visits today)