patagonの日記： [コンピュータ]detach, attach, SQL Serverのデータベース移動,サーバのデータ移行,サーバ移行

(データベース)サーバの準備が間に合わなかったので暫定のサーバでリリースしたシステムがあった。やっと本来のサーバが入ったとのことでシステムを暫定サーバから新しいサーバへ載せ替える。データベースを指定すれば、実際の移行は資源を準備する技術部門や運用部門でやってもらえるかと思っていたらそうじゃないらしい。もちろん移行前後のデータ、システム確認はするつもりだったが。開発側で手順書やクエリーを作って運用部門はそれを単にタイプインするだけだって。かといって標準的な移行方法も技術部門からも示されない。あーあ、やりますか。昨日の夕方と今日の午後、調べたりテスト環境で色々試してみた。

概要 SQL Server を実行しているコンピュータ間でデータベースを移動する方法(Microsoft文書番号:314546)にあるように、「バックアップと復元」や「Sp_detach_db ストアド プロシージャと Sp_attach_db ストアド プロシージャ(detach,attach)」、「データのインポートとエクスポート (SQL Server データベース間のオブジェクトとデータのコピー)」 等の方法があるようだ。かなり前にちょっと打ち合わせしていて、「detach,attach」で行うように技術部門が申請しているので、これで行う。今回は古いサーバ、新しいサーバともSQL Server 2005。直接は関係ないが移行とアップグレード(SQL Server 2005 機能紹介)あたりも読んでおくといいかもしれない。 SQL Server のデタッチとアタッチ機能を使用して SQL Server データベースを新しい場所に移動する方法(Microsoft文書番号:224071)が詳しい。

1. 簡単に言うと古い(暫定)サーバ上でdetachでデータベースをSQL Serverシステムから切り離す(宣言をする)。
2. 次に古いサーバ上のデータベースを構成する物理ファイル(*.mdf,*.ndf, *.ldf)を新しいサーバ上にコピーする(ドライブやフォルダ名、ファイル名は変更可能。同じでも良い。通常はドライブ、フォルダは変えてもファイル名は変えないことが多いだろう)。
3. 新しいサーバ上でattachでデータベースを構成する(ことにする)物理ファイルをSQL Server上に取り付ける(ことを宣言する)。ここは力をかけて押し込むような感じと言ったほうがいいかもしれない。

実際には上記の0番には古いサーバでテーブル毎にレコード件数を表示して保存、4番には新しいサーバでテーブル毎にレコード件数を表示して保存、5番でレコード件数の比較確認。6番で業務システム的な確認が入ったりすると思う。他にはユーザというかログインの移行・設定も必要。 古いサーバでデータベースを再び使うには(こちらでも)attachが必要。

クエリー(SQL Server管理画面(*)のGUIでも出来るけど) データベースの場所やファイル名は、SQL Serverの管理画面からデータベースを右クリックしてプロパティで見るか、以下をSQL Server管理画面のクエリウィンドウから実行すれば得られる。さらにはnantoka.sqlというようにファイルに格納し実行してもいい。拡張子sqlはSQL Serverに関連付けられているだろうから、SQL Server管理画面が表示されると思う。そこで(構文チェック後)実行。 *:SQL Server 2005はManagement Studio、SQL Server 2000はEnterpriseManagerだっけ。

-- データベースの場所やファイル名を表示する。データベース名が「SAMPLE_DB」の場合
use SAMPLE_DB
go
sp_helpfile
go

-- detachの例。データベース名は「SAMPLE_DB」
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SAMPLE_DB', @keepfulltextindexfile=N'true'
GO

上記にも書いたが、ドライブやフォルダ名、ファイル名は変更可能。同じでも良い。通常はドライブ、フォルダは変えてもファイル名は変えないことが多いだろう。ファイル名を変えた場合はattachで指定するファイル名に物理ファイル名を変えておくこと。運用によってはPrimary部分、Data部分、Index部分、Log部分という分け方でない場合もあるだろう。またドライブやフォルダ(パス)を分けている場合もあるだろう。

-- attachの例。データベース名は「SAMPLE_DB」
EXEC sp_attach_db @dbname = N'SAMPLE_DB',
      @filename1 = N'E:\MSSQL\Data\SAMPLE_DB_Primary.MDF',
      @filename2 = N'E:\MSSQL\DBData\SAMPLE_DB_Data.NDF',
      @filename3 = N'E:\MSSQL\DBIndex\SAMPLE_DB_Index.NDF',
      @filename4 = N'E:\MSSQL\Log\SAMPLE_DB_Log.LDF'

その他 古いサーバ上でdetach前にテーブル毎にレコード件数、新しいサーバでattach後にレコード件数を確認する(よね？)。 しかし

SELECT COUNT(*) from TABLE_NAME001
SELECT COUNT(*) from TABLE_NAME002
…
SELECT COUNT(*) from TABLE_NAMEnnn

のようにはやってられない。クエリー(スクリプト)を書くのが大変。仮にクエリーを作ったとしても、実行結果はSELECT毎に表示されるから結果はテーブル数(一度に実行した)の分表示されて、結果を保存しようとしたら、テーブル数分保存しなくちゃいけない(SQL Serverだと結果をCSVで保存できる)。また結果の確認も大変。 調べているとSQL Serverの「sysobjects」が使えることが分かった。SQLSERVER で、テーブル名とデータ件数を一発で取得するSQLって書けるでしょうか？ - 人力検索はてな の回答2を参考とした(*)。このままだとテーブル名が順に並んでないので見難い。これを修正したものが(といっても単にORDER BYを入れただけ)以下のもの。 *:後で気づいたがこのクエリーは後述の河端氏が作成したものと同じ。

select o.name as テーブル名, i.rows as 件数
from sysindexes i, sysobjects o
where
o.xtype = 'U'
-- ユーザ定義テーブル名のみを取得するため以下の1行を追加(2007/07/31)
and o.name <> 'sysdiagrams'
-- 追加終わり
and o.id = i.id
and i.indid < 2
order by o.name;

実行結果 TABLE_NAME001,0(件) TABLE_NAME002,1856(件) … TABLE_NAMEnnn,256158(件) クエリーを書くのも楽。実行結果も1度に表示されるので、結果の保存も1つでいい。また結果の確認もしやすい。今回、移行するのは3DBありそれぞれ60以上、100以上、100以上のテーブルがあるのでこれが分かってよかった。一行一行SELECT文書けないし、書いても間違う。また実行結果の保存も確認も時間・負荷的に大変だろう。 注意 count(*)とrowsの取得値に誤差がある場合があるらしい。 SQL Server ユーザーグループ システム構築ML 2003年1月17日 河端氏の「テーブルの行数を取得する ! count(*) を使わずに。」から始まる投稿 システム構築ML2003年1月を中心とした投稿一覧