SQL Serverで本番環境のデータをテスト環境にコピーする

2019年3月6日

1.はじめに
1-1.頑張って同じデータのinsert文つくて登録する
1-2.select insertする
1-3.テーブルのデータから、一括でinsert文を作成する
2.SQL Server Management Studioから一括でinsert文作成する

はじめに

本番環境で発生した不具合を、テスト・ローカル環境で再現するときに、本番環境のデータをまるっとコピーしたいという場合があると思います。
そんな時にどうすればコピーできるのか、簡単にコピーする方法はないのか調べました。
方法としては、以下の三つ存在しているかと思います。

  1. 頑張って同じデータのinsert文つくて登録する
  2. select insertでコピーする
  3. テーブルのデータから、一括でinsert文を作成する

1. 頑張って同じデータのinsert文つくて登録する

とてもめんどくさいです。
特に何百、何千件のデータを投入するとなったら果てしなく時間かかります。
データの型とか考えながらinsert文作らなきゃいけないし、できないことはないけどやるべきではないですね。

2. select insertでコピーする

これが一番楽で、スマートなやり方になるかと思います。
ただし、条件が付いていると思います。
例えば、別サーバーでかつ、SQLを実行したユーザーでその別サーバーにアクセスできない場合にはこちらの方法が使えません。
今回の件だと、本番環境とテスト環境であれば、実行できるかもしれませんが、本番環境とローカルの環境だとユーザーとか別になることがほとんどだと思うので、難しいと思います。

3. テーブルのデータから、一括でinsert文を作成する

これであれば、select insertの次に楽な方法だと思います。
さらに制限などもないので、本番からローカルへのデータのコピーも楽に実行することができます。
なので、今回はこの方法にフォーカスしていきます。

SQL Server Management Studioから一括でinsert文作成する

SQL Server Management Studio(以降SSMS)には、テーブルのデータから一括でinsert文を作成する機能があります。
方法は簡単です。
1. SSMSからコピー元(本番環境)となるサーバーに接続する
2. 対象のDBを右クリックして、タスク>スクリプトの生成をクリックする
3. オブジェクトの選択画面が開かれるので、「特定のデータベース オブジェクトを選択」を選択する
4. テーブルのリストを開いて、コピーしたいテーブルを選択し、「次へ」をクリックする
5. 「詳細設定」を開いて、スクリプトを生成するデータの種類を「データのみ」に変更し、「OK」をクリックする
6. ファイル名を任意の名称に変更して、「次へ」をクリックする
7. 選択した内容を確認し、問題なければ「次へ」をクリックする
8. 対象のデータのinsert文が生成されて、保存され完了!

以上になります。
後は、insert文が作成されているので、それをそのままローカルに流してあげればそれでOKになります。

ただ、一点問題があって、この方法だとテーブルのデータをすべて吐き出してしまうんですよね。。。
解決方法としては、一時テーブル作成して、そこに必要なデータをselect insertして、そのテーブルのデータを吐き出すとかだととても効率的になるかと思います。
なので、
select insertと一括でinsert文作成の両方使うのが一番効率的になりますね。