SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2005 以降でシステムテーブルを直接更新する方法

leave a comment

このところ SQL Server をインストール以外触っておらず、データベース管理者としてのスキルが右肩下がりです…。
このままでは SQL Server のデータベース管理者としてお先真っ暗となってしまいますので、小さなことでもコツコツと
投稿していきたいと思います。

思い立った初回は SQL Server 2005 以降でシステムテーブルを直接更新する方法を投稿したいと思います。

SQL Server 2000 までは sp_configure で [allow updates オプション] を設定することでシステムテーブルを
直接更新することが可能でした。

SQL Server 2005 以降の Books Online では allow update オプションの説明は以下のように記載されています。
# 今回は SQL Server 2008 で検証をしていますが、2005 でも同様の動作となります。

このオプションは sp_configure ストアド プロシージャにまだ含まれていますが、
その機能を SQL Server で使用することはできません。設定しても何の影響もありません。
SQL Server 2005 以降のバージョンでは、システム テーブルの直接更新はサポートされていません。

allow updates オプションを変更すると、RECONFIGURE ステートメントが失敗します。
allow updates オプションへの変更は、すべてのスクリプトから削除する必要があります。

?

実際に SQL Server 2008 で? allow updates を設定すると以下の実行結果となります。

sp_configure ‘allow updates’, 1
GO
RECONFIGURE
GO

[実行結果]

構成オプション ‘allow updates’ が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。
メッセージ 5808、レベル 16、状態 1、行 1
システム カタログへのアドホック更新はサポートされていません。

?

RECONFIGURE をするとエラーとなります。
RECONFIGURE WITH OVERRIDE することで設定ができます。

この状態で、sysdatabases のようなシステムテーブルに更新をかけると以下のようなエラーとなります。
# SQL Server 2005 以降では sysdatabases は下位互換のシステムビューで実体は master.sys.sysdbreg だったりしますが。

update sys.sysdatabases set status = status|32768 where dbid = 5

[実行結果]

メッセージ 259、レベル 16、状態 1、行 1
システム カタログへのアドホック更新は許可されません。

?

SQL Server 2005 以降でシステムテーブルを更新するには以下の手順を実行して SQL Server に接続する必要があります。

  1. SQL Server をシングルユーザーモードで起動
  2. 専用管理者接続 (DAC) で接続

[シングルユーザーモードで起動する方法]

シングルユーザーモードで起動する前に、SQL Server 関連のサービスをすべて停止しておきます。
シングルユーザーモードはその名の通り、一人しか接続ができないモードですので SQL Server Agent や、
SSRS / SSIS / SSAS が起動していると、それらのサービスが SQL Server に接続してしまい、データベースエンジンクエリで
接続ができなくなってしまいます。
シングルユーザーモードで起動した後に、接続をしようとして エラー 18461 が発生する場合は、SQL Server 以外の
関連サービスを停止して確認をした方がよいと思います。

image

シングルユーザーモードはコマンドプロンプトで SQL Server を実行することで起動できます。

cd <インスタンスのプログラムディレクトリ>
sqlservr.exe ?m ?s <インスタンス名>

[実行例]
cd “c:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLBinn”
sqlservr.exe ?m ?s SQL2008

?

[DAC で接続]

DAC で接続する場合は接続時にサーバー名に [ADMIN:] をつけて接続をします。
今回はサーバーで直接作業をしています。
# リモートで DAC を使用することも可能ですがその場合は別の設定をする必要があります。

image

これでシステムテーブルを更新する準備は完了です。
allow updates は SQL Server 2005 以降では無効なオプションですので、設定をしなくてもシステムテーブルを
更新することが可能です。

試しにシステムテーブルを直接更新してデータベースをエマージェンシー (緊急) モードに設定したいと思います。
sysdatabases / sys.database はビューですので、各ビューの実体である sysdbreg テーブルを直接更新しています。
# use mssqlsystemresource を実行してリソースデータベース上で更新をしようとするとエラーとなりますので
  カレントデータベースは master データベース等で実行する必要があります。

update master.sys.sysdbreg set status = status|32768 where id = 5

[実行結果]

警告: システム テーブル ID 28 がデータベース ID 1 で直接更新されました。
キャッシュの一貫性が維持されていない可能性があります。SQL Server を再起動してください。

(1 行処理されました)

# ビット演算で status にエマージェンシーモードに対応するビットを有効にしています。

下が SQL 実行後の状態になります。
ALTER DATABASE ではなく、システムテーブルの直接更新でエマージェンシーモードに切り替わっています。

image

確認ができたのでシステムテーブルを更新できる状態にして以下の SQL を実行して正常な状態に戻しておきます。

update master.sys.sysdbreg set status = 0 where id = 5

?

以上でシステムテーブルの更新方法は終了です。
使うことがあるかはわかりませんが、奥の手として知っておくと便利な時があるかも知れません。

Share

Written by Masayuki.Ozawa

11月 4th, 2009 at 3:26 pm

Posted in SQL Server

Leave a Reply