名前付きインスタンスで取得したシステムデータベースのバックアップを既定のインスタンスにリストアする方法をまとめてみたいと思います。
今回はバックアップの取得先とリストア先で照合順序 / SQL Server のマイナーバージョンまで含めたバージョン情報は同一の状態としています。
# SQL Server のバージョンがマイナーバージョンレベルでも異なっていると、システムデータベースのリストアはできませんので。
本投稿では、SQL Server 2014 がインストールされた、[SQL2014] という名前付きインスタンスで取得したシステムデータベースのバックアップを他の環境に構築された既定のインスタンスにリストアをします。
システムデータベースのリストアについては以下の技術文書に情報が記載されています。
システム データベースのバックアップと復元 (SQL Server)
master データベースについてはリストア方法が通常のデータベースとは少し異なるため
master データベースの復元 (Transact-SQL)
を参考にリストアを行います。
バックアップの取得については、
BACKUP DATABASE master TO DISK='master.bak' WITH COPY_ONLY, COMPRESSION, STATS = 10 BACKUP DATABASE model TO DISK='model.bak' WITH COPY_ONLY, COMPRESSION, STATS = 10 BACKUP DATABASE msdb TO DISK='msdb.bak' WITH COPY_ONLY, COMPRESSION, STATS = 10
のようなクエリで取得します。
バックアップ時に既存のバックアップのチェーンに影響が出ないように、COPY_ONLY を指定しています。
取得したバックアップをリストア先にコピーし、リストア作業を実施します。
最初に master データベースのリストアを行うために、シングルユーザーモードで起動するため、以下のコマンドを実行します。
net stop MSSQLSERVER "C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinnsqlservr.exe" -sMSSQLSERVER -m
新しく開いたコマンドプロンプトで以下のコマンドを実行して master データベースをリストアします。
# 今回は [C:Backup] にバックアップファイルを保存しています。
sqlcmd RESTORE DATABASE master FROM DISK='C:Backupmaster.bak' WITH REPLACE GO
これで [master] データベースのリストアは完了です。
再度、以下のコマンドを実行して SQL Server を起動してみます。
"C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinnsqlservr.exe" -sMSSQLSERVER -m
現在の状態だと、リストアをしてもエラーが出力され、SQL Server を起動することができません。
今回は [SQL2014] という名前付きインスタンスで取得したバックアップをリストアしているのですが、この場合、
[C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATA]
にシステムデータベースが格納されています。
既定のインスタンスの場合は、
[C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA]
にシステムデータベースが格納されているため、master データベースをリストアしたタイミングでシステムデータベースの格納先が変わりエラーとなり、インスタンスを起動することができません。
そこで、以下のコマンドを実行して、リストア前に使用していた model / msdb をコピーします。
mkdir "C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATA" copy "C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA*" "C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATA"
これで SQL Server を起動することができます。
次に master 以外のシステムデータベースを移動します。
# システムデータベースの移動は システム データベースの移動 を参照して下さい。
ALTER DATABASE model MODIFY FILE ( NAME = 'modeldev', FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmodel.mdf') ALTER DATABASE model MODIFY FILE ( NAME = 'modellog', FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmodellog.ldf') ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBData', FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAMSDBData.mdf') ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBLog', FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAMSDBLog.ldf') ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempdev', FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAtempdb.mdf') ALTER DATABASE tempdb MODIFY FILE ( NAME = 'templog', FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAtemplog.ldf')
SQL Server のサービスを再起動することで、既定のインスタンスの場所に残しておいたシステムデータベースのファイルを読み込むようになりますので、このタイミングでコピーして作った [C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATA] が削除可能となります。
システムデータベースの移動が終了したら以下のコマンドでシステムデータベースをリストアします。
RESTORE DATABASE model FROM DISK = N'C:Backupmodel.bak' WITH REPLACE, STATS = 10 RESTORE DATABASE msdb FROM DISK = N'C:Backupmsdb.bak' WITH REPLACE, STATS = 10
この状態ですと、イベント ID 8355 の Service Broker のエラーが msdb で発生しているかと思います。
これを解決するためには、以下の作業を実行します。
シングルユーザーモードで SQL Server を起動 "C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinnsqlservr.exe" -sMSSQLSERVER -m 別のコマンドプロンプトで以下を実行 sqlcmd.exe ALTER DATABASE [msdb] SET ENABLE_BROKER GO
これでシステムデータベースのリストアは完了です。
この状態ではサーバー名がバックアップ取得元の情報となっていますので以下を実行してサーバー名を変更しておきます。
# SQL Server のスタンドアロン インスタンスをホストするコンピューターの名前変更 を参考に
select @@servername EXEC sp_dropserver '<@@servernameで確認したサーバー名>' EXEC sp_addserver '<現在のサーバー名>',local 上記が終わったら SQL Server のサービスを再起動
管理ユーザーで接続ができない場合などはシングルユーザーモード (-m) で起動することで Administrators グループのユーザーは接続できるかと思いますので、この方法でログインして sysadmin ロールのユーザーをメンテナンスするとよいかと。
SQL Server 2008 R2 では透過的データ暗号化 (TDE) により暗号化したデータベースを使用していない場合でも、エラー 15466 [号化解除中にエラーが発生しました。] が発生し、[ALTER SERVICE MASTER KEY FORCE REGENERATE] でキーを再生成し、エラーが出ないようにする等の対応をすることがありましたが 2014 では出ていませんでした。
ほかにも
USE [master] GO CREATE LOGIN [NT SERVICEMSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICEMSSQLSERVER] GO CREATE LOGIN [NT SERVICESQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICESQLSERVERAGENT] GO
というようなクエリでサービスアカウント系の調整をする必要があったりしますが、この辺は都度対応していくとよいかと。
SQL Server エージェントのジョブの所有者情報にサーバー名の情報が入っていることがありますので、この場合は SQL Server Agent のジョブを開いた状態で [オブジェクトエクスプローラーの詳細] を開き、ジョブをスクリプト化し、
出力されたスクリプトのサーバー名を変更してジョブを作り直すといった対応が必要となることがありますが、この辺は異なるサーバーにリストアした場合の対応と同じかと。
データコレクションのジョブの再設定などは、Remove associated data collector jobs が参考になるかと。
リストアができるかが少し気になったので軽く試してみました。