DB を移行してデータが本当に同じかどうかを検証しました。
移行は SQL Server 2008 -> SQL Server 2016 です。
方針
全件データをファイルに掃き出してハッシュ値を比べることにしました。
- DBのテーブル一覧を取得する
- 各テーブルのデータを全件取得するSQLを作成する
- 新旧DBの各テーブルのデータをファイルに掃き出す
- ファイルのハッシュ値を比べて等しいことを確認する
スクリプト
1． DBのテーブル一覧を取得する
SQL は以下です。
SQL Server では sys.object からテーブル情報を取得出来ます。
type = ‘U’ でユーザーテーブルを指定します。
USE [TEST_TABLE]
SELECT name FROM sys.objects WHERE type = 'U' AND name LIKE 'TEST_%' ORDER BY name;あとは sqlcmd で SQL を実行します。
-o で結果を出力することができます。
$hostName = [System.Environment]::MachineName
$userName = "sa"
$password = "password"
$sql = "getTableNames.sql"
$csv = "tableNames.csv"
sqlcmd -S $hostName -U $userName -P $password -i $sql -o $csv結果は以下のようになります。
データベース コンテキストが 'TEST' に変更されました。
name
-------------------------------------------------------
TEST_USER
...
TEST_ROLE
(xxx 行処理されました)2． 各テーブルのデータを全件取得する SQL を作成する
1．でテーブル名一覧を取得したので各テーブルのデータを全件取得する SQL を作成します。
テンプレを用意してテーブル名を埋め込み SQL を発行するようにしました。
USE [TEST]
DECLARE @sql nvarchar(max);
DECLARE @tblName nvarchar(max);
SET @tblName = N'{TABLE_NAME}';
SET @sql = N'SELECT * FROM ' + @tblName;
exec sp_executesql @sqlテーブル名一覧は 1. から用意します。
$template = "D:\Sample\SQL\template.sql"
$sqlOutDir = "D:\Sample\SQL\Test"
$tableNames = (
"TEST_USER",
...
"TEST_ROLE"
)
$tableNames | % { (Get-Content -Path $template) -replace "{TABLE_NAME}", $_ | Out-File -Encoding String -FilePath ("{0}\{1}.sql" -f $sqlOutDir, $_) }3． 新旧 DB の各テーブルのデータをファイルに掃き出す
新旧 DB それぞれで 2．で作成した SQL を実行します。
# 2．で作成した SQL が置いてあるパス
$sqlDir = "D:\Sample\SQL\Test"
# 全件データの出力先
$outDir = "D:\Sample\Result\New"
# $outDir = "D:\Sample\Result\Old"
Get-ChildItem -Path $sqlDir -Filter *.sql | % { Execute-Sql -Sql ("{0}\{1}" -f $sqlDir, $_.Name) -FileName ($_.Name -split ".")[0] -OutDir $outDir }
function Execute-Sql(){
Param(
$Sql,
$FileName,
$OutDir
)
$hostName = [System.Environment]::MachineName
$userName = "sa"
$password = "password"
$csvName = ("{0}\{1}.csv" -f $OutDir, $FileName)
sqlcmd -S $hostName -U $userName -P $password -i $Sql -o $csvName -s ","
}4．ファイルのハッシュ値を比べて等しいことを確認する
$str = "ファイル,比較結果,新DB,旧DB`n"
Get-ChildItem -Path $RelResultDir -Filter *.csv | ForEach-Object { $str += Get-ComparedResultStr -FileName ($_.Name -split "\.")[0] -NewFileDir ("{0}\{1}" -f $RelResultDir, $_.Name) }
$str | Out-File "CompareResult.csv" -Encoding default
funciton Get-ComparedResultStr(){
Param(
$NewDataDir,
$FileName
)
# 新DBのハッシュ値
$NewHash = (Get-FileHash -Path $NewDataDir).Hash
# 旧DBのデータを格納しているディレクトリに切替
$OldDataDir = $NewDataDir -replace "New", "Old"
# 旧DBのハッシュ値
$OldHash = (Get-FileHash -Path $OldDataDir).Hash
# CSVのデータ行
return ("{0},{1},{2},{3}`n" -f $FileName, ($NewHash -eq $OldHash), $NewHash, $OldHash)
}比較結果は以下のようになります。
ファイル,比較結果,新DB,旧DB
TEST_USER,True,A8BD1EB1209E732645D9DB396A7EA969DB26EF105090FC8C2FB2C4E9E0A28A7E,A8BD1EB1209E732645D9DB396A7EA969DB26EF105090FC8C2FB2C4E9E0A28A7E
...
TEST_ROLE,True,DFE6BB21EEE7ED304D72F02B77974ADA00F8E8958A477045CAE0461B3A4EFF11,DFE6BB21EEE7ED304D72F02B77974ADA00F8E8958A477045CAE0461B3A4EFF11これでスクリプトで正しくデータ移行できたか検証することができました。
