SQL ServerでDBの復元に失敗する

バックアップしたDBを別のパソコンに復元しようとしたら次のエラーが発生して失敗した。
DBの互換性レベルは、SQL Server 2017(140)にしている。

バックアップ元は、SQL Server 2019
復元先は、SQL Server 2017

System.Data.SqlClient.SqlError: データベースはバージョン 15.00.2000 を実行中のサーバーにバックアップされました。このバージョンは、このサーバー (バージョン 14.00.3192 を実行) とは互換性がありません。バックアップをサポートしているサーバーでデータベースを復元するか、またはこのサーバーと互換性のあるバックアップを使用してください。 (Microsoft.SqlServer.SmoExtended)

どうもSQL Serverのバージョンが違うと復元できないようです。
バージョン違いのバックアップしたDBを復元をするには、同じバージョンのSQL Serverをインストールするしかないっぽい。

DBの復元手順

仮想環境を立ち上げて、SQL Serverをインストールする。
SSMSからDBを復元して、復元したDBを選択して右クリックからの[タスク]－[スクリプトの生成]を実行する。
"特定のデータベースオブジェクトを選択"からテーブルを選択する。
スクリプトは"ファイルに保存"を選択して"単一ファイル"に出力する。

SQL Server 2019からOPTIMIZE_FOR_SEQUENTIAL_KEYというコマンドが追加されており、このままではSQL Server 2017で使用できないので出力したスクリプトファイルからOPTIMIZE_FOR_SEQUENTIAL_KEYを削除した。

開発環境側で同じDB名を作成しておき、このスクリプトを実行してテーブルを作成する。
exp_table.bat, exp_table.vbsを用意し、仮想環境側でexp_table.batを実行する。
直下にexp_impフォルダが作成され、フォルダの中のrun_exp.batを実行する。
exp_imp\dataフォルダにエクスポートしたデータが出力される。
exp_impフォルダごと開発環境にコピーして、exp_impフォルダの中のrun_imp.batを実行する。

参考URL 【SQL Server】bcpを使用したインポートとエクスポート - 小物SEのメモ帳

exp_table.bat

CScript .\exp_table.vbs

exp_table.vbs

Dim FROM_DB_SERVER
Dim FROM_DB_NAME
Dim FROM_DB_USER
Dim FROM_DB_PASSWORD
Dim TO_DB_SERVER
Dim TO_DB_NAME
Dim TO_DB_USER
Dim TO_DB_PASSWORD
Dim strRootPath
'==========
'DB接続情報(バックアップ元)
FROM_DB_SERVER   = "PC-A\HOGE_SQL"
FROM_DB_NAME     = "HOGE_DB"
FROM_DB_USER     = "sa"
FROM_DB_PASSWORD = "P@ssw0rd"
'DB接続情報(復元先)
TO_DB_SERVER     = "PC-B\DEV_SQL"
TO_DB_NAME       = "HOGE_DB"
TO_DB_USER       = "sa"
TO_DB_PASSWORD   = "P@ssw0rd"
'==========

'データ出力先
strRootPath = GetFolderPath()
Call CreateFolder(strRootPath & "\exp_imp")
Call CreateFolder(strRootPath & "\exp_imp\data")

'batファイルを作成(run_exp.bat, run_imp.bat)
Call CreateBatchFile

WScript.Quit

Sub CreateBatchFile()

    Dim adoConn
    Dim adoComm
    Dim adoRS
    Dim objFso
    Dim expTs
    Dim impTs
    Dim strBuff

    Set adoConn = CreateObject("ADODB.Connection")

    Dim strConnect
    strConnect = "Provider=SQLOLEDB;" & _
                 "Data Source=" & FROM_DB_SERVER & ";" & _
                 "Initial Catalog=" & FROM_DB_NAME & ";" & _
                 "User ID=" & FROM_DB_USER & ";" & _
                 "Password=" & FROM_DB_PASSWORD
    adoConn.ConnectionString = strConnect
    adoConn.Open

    Set adoComm = CreateObject("ADODB.Command")
    adoComm.ActiveConnection = adoConn
    adoComm.CommandTimeout = 0
    adoComm.CommandType = 1

    Set objFso = CreateObject("Scripting.FileSystemObject")

    adoComm.CommandText = "SELECT name AS TABLE_NAME FROM sys.tables"

    Set adoRS = adoComm.Execute

    If adoRS.EOF = False Then

        Set expTs = objFso.CreateTextFile(strRootPath & "\exp_imp\" & "run_exp.bat")
        Set impTs = objFso.CreateTextFile(strRootPath & "\exp_imp\" & "run_imp.bat")

        Dim strTableName
        Do While Not adoRS.EOF
            strTableName = adoRS("TABLE_NAME").Value
            
            'run_exp.bat フォーマット
            strBuff = "bcp " & strTableName & " format nul -c -x -f .\data\" & strTableName & "_fmt.xml -t, -S " & FROM_DB_SERVER & " -U " & FROM_DB_USER & " -P " & FROM_DB_PASSWORD & " -d " & FROM_DB_NAME
            expTs.WriteLine (strBuff)
            'run_exp.bat データCSV
            strBuff = "bcp " & strTableName & " out .\data\" & strTableName & ".csv -S " & FROM_DB_SERVER & " -U " & FROM_DB_USER & " -P " & FROM_DB_PASSWORD & " -d " & FROM_DB_NAME & " -f .\data\" & strTableName & "_fmt.xml -t, -o .\data\" & strTableName & "_output.log"
            expTs.WriteLine (strBuff)
            
            'run_imp.bat データCSV
            strBuff = "bcp " & strTableName & " in .\data\" & strTableName & ".csv -S " & TO_DB_SERVER & " -U " & TO_DB_USER & " -P " & TO_DB_PASSWORD & " -d " & TO_DB_NAME & " -f .\data\" & strTableName & "_fmt.xml -t, -o .\data\" & strTableName & "_input.log"
            impTs.WriteLine (strBuff)
            
            adoRS.MoveNext
        Loop

        expTs.Close
        Set expTs = Nothing

        impTs.Close
        Set impTs = Nothing

        adoRS.Close
        Set adoRS = Nothing
    End If

    Set objFso = Nothing

    Set adoComm = Nothing

    adoConn.Close
    Set adoConn = Nothing

End Sub

Function GetFolderPath()

    Dim objFso

    Set objFso = CreateObject("Scripting.FileSystemObject")

    GetFolderPath = objFso.GetParentFolderName(WScript.ScriptFullName)

    Set objFso = Nothing

End Function

Sub CreateFolder(strFolder)

    Dim objFso

    Set objFso = CreateObject("Scripting.FileSystemObject")

    If objFso.FolderExists(strFolder) = False Then
        Call objFso.CreateFolder(strFolder)
    End If

    Set objFso = Nothing

End Sub

ssCAROのブログ

色んなとこで見つけたプログラムのメモ置き場っぽい

SQL ServerでDBの復元に失敗する

DBの復元手順