【SQL Server】移行後のDBと移行前のDBのデータが同じかどうかを確認してみた

新旧DBのなかみが同じことを確認してみた

DBを移行してデータが本当に同じかどうかを検証してみた.
SQL Server 2008 -> SQL Server 2016.

方針

全件データをファイルに掃き出してハッシュ値を比べることにした.
もっとスマートなやり方知ってる人いたら誰か教えて!

  1. DBのテーブル一覧を取得する
  2. 各テーブルのデータを全件取得するSQLを作成する
  3. 新旧DBの各テーブルのデータをファイルに掃き出す
  4. ファイルのハッシュ値を比べて等しいことを確認する

スクリプト

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

テーブル名一覧はコピペで用意しちゃった.

$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

これでスクリプトで正しくデータ移行できたか検証できた.