WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

データベースの復元

by WebSurfer 14. June 2015 20:31

このホームページ / ブログに利用しているホスティングサービス会社から、SQL Server データベースのログファイルのサイズが数 10 GB に肥大化していて破損している可能性があるので対応して欲しいとの連絡がありました。

自分が壊したわけではない(はず)なのですが、ホスティングサービス会社では復元のための作業はやってくれないそうなので、やむを得ず自分でやりました。その手順を備忘録として書いておきます。

まず、ホスティングサービス会社が提供している myLittleAdmin というツールを利用してバックアップを作成し、それをダウンロードします。

myLittleAdmin を利用してバックアップ作成

ファイルサイズが数 10 GB と巨大なので、バックアップファイルの作成とダウンロードにとんでもなく時間がかかるかと思っていましたが、全然そんなことはなかったです。

バックアップファイル (.bak) の作成にはほとんど時間はかかりません。生成された .bak ファイルのサイズは 38,666 KB(GB ではなくて)でした。ダウンロードには、当たり前ですが、ファイルサイズに似合った時間はかかりましたが。

.bak ファイルを C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup に保存。UserDatabase データーベースは、自分の PC の SQL Server 2008 Express に作成済み、アタッチ済みだったものがあったので、それを復元してみました。

復元作業は、SQL Server Management Studio を使って、マイクロソフト公式解説書「一目でわかる Microsoft SQL Server 2008」の 5 章の「6 データベースを復元するには」の手順に従って行いました。

しかし、デフォルト設定のままやると UserDatabase の .bak ファイルが違うという意味(正確なメッセージは忘れました)のエラーが出て復元できません。

本にはデフォルト設定のままでもよさそうに書いてありましたが、[オプション]タブの[復元オプション]の項目で[既存のデータベースを上書きする (WITH REPLACE)(O)]にチェックを入れないとダメでした。

SQL Server のバージョンの違い(ホスティングサービスは 2005、自分の開発環境は 2008)は復元には問題なかったです。

復元されたデータベースのファイルサイズは、エクスプローラで見て UserDatabase.mdf が 39,296 KB、USERDATABASE.LDF が 51,090,368 KB でした。

開発マシンの Web アプリからデータベースに接続し、ざっと内容を見てみたが、正しく復元されていてデータベースファイルには問題がない様子。

問題はやはり、ホスティングサービス会社が言うように、USERDATABASE.LDF が 51,090,368 KB と肥大化していること(だけ?)のようです。

次に、トランザクションログの切捨てを行うため、トランザクションログをバックアップします。(切捨ては、ログファイルに再利用可能な領域を広げるだけで、ファイルサイズを小さくするわけではないそうです)

マイクロソフト公式解説書「一目でわかる Microsoft SQL Server 2008」の 5 章の「3 トランザクションログをバックアップするには」の手順に従って、userdatabaselogfile_20150614.bak というファイル名を指定してバックアップ。バックアップは成功し(正常に実行されましたとのダイアログが出る)、2,954 KB の .bak ファイルが作成されました。

しかし、やはりログファイル USERDATABASE.LDF のサイズは変わりません。ファイルサイズを小さくするために DBCC SHRINKFILE (UserDatabase_log, 5) を実行してみます。

(注:ファイル名には SELECT * FROM sys.database_files で取得できる UserDatabase_log という名前を使わないとダメでした。USERDATABASE.LDF という名前を使うと sys.database_files でファイルが見つからないというエラーになります)

このときは、"クエリが正常に実行されました" とは出るものの、[メッセージ]タブをクリックして見ると、"ログ ファイル 2 (UserDatabase_log) を圧縮できません。ファイルの末尾にある論理ログ ファイルが使用中です" というエラーメッセージが出ます。理由は不明です。(汗)

DBCC SQLPERF(LOGSPACE) を実行してみると以下の結果になりました。ファイルサイズは多少は小さくなったもののまだ数 10 GB のオーダーです。

Log Size (MB): 48474.99
Log Space Used (%): 0.6307064

エクスプローラで見ると、ファイルサイズは、UserDatabase.mdf が 39,296 KB、USERDATABASE.LDF が 49,638,400 KB(DBCC SHRINKFILE 実行前は 51,090,368 KB)で、やはりサイズの縮小に失敗しています。

ここで諦めては何なので、もう一回ログファイルのバックアップからやってみるました。前回作成した userdatabaselogfile_20150614.bak に上書きしたことが異なるだけで、その他の手順は前回と同じです。前回と同様に処理は正常に終わります。.bak ファイルのサイズは 2,954 KB ⇒ 204,048 KB になりました。

DBCC SQLPERF(LOGSPACE) を実行してみると以下の結果となり、Log Size (MB) は同じながら、Log Space Used (%) が一桁下がっています。

Log Size (MB): 48474.99
Log Space Used (%): 0.04509413

DBCC SHRINKFILE (UserDatabase_log, 5) を実行してみると、今度は "圧縮できません" というエラーメッセージは出ません。再度 DBCC SQLPERF(LOGSPACE) を実行してみると以下の結果となり、Log Size (MB) が大幅に下がっていました。

Log Size (MB): 5.117188
Log Space Used (%): 14.37023

DBCC SHRINKFILE (UserDatabase_log, 5) の 5 というのは、ファイルサイズが 5MB になるよう領域を開放することを意味するので、ほぼ期待通りの結果です。

何故一回目がうまく行かなかったのかは謎です。(汗)

開発環境でこの記事をポストして正常に表示されることを確認してから、SQL Server Management Studio で .bak ファイルを作成し、myLittleAdmin を利用して本番環境の UserDatabase を復元・・・を試みましたが、"デバイス 'xxx.bak' のメディア ファミリが正しい形式ではありません。SQL Server はこのメディア ファミリを処理できません" というエラーメッセージが出て失敗に終わります。(涙)

心当たりがないか、ホスティングサービス会社に問い合わせ中です。SQL Server 2008 で作成したバックアップファイルで SQL Server 2005 のデータベースの復元は間違いなくできるのでしょうか? (バージョンの違いの件は、事前にホスティングサービス会社に問合せていて、「恐らく大丈夫」とのことでしたが・・・)


【2015/6/16 追記】

本番環境の UserDatabase の復元に失敗する理由は、やはり SQL Server のバージョンの違いでした。

MSDN ライブラリの記事 復元と復旧の概要 (SQL Server) の「注意」に "SQL Server 2008 のバックアップを以前のバージョンの SQL Server で復元することもできません" とはっきり書いてありました。

ホスティングサービス会社のサポート担当の人によると、彼ら自身で実際に SQL Server 2008 R2 で作業して復元は可能なことは確認したとのことでしたが、それは SQL Server 2008 R2 では更新等何も行わなかったからで、更新を行うと同じエラーとなって失敗に終わるそうです。

結局、自分の開発マシンに SQL Server 2005 Developer Edition をインストールして、上に書いたのと同様な手順で復元作業を行った結果、myLittleAdmin の復元ウィザードで SQL Server 2005 Std のデータベースの復元ができました。

ただし、修復用の .bak ファイルを作った時のログファイルのサイズは 5MB 程度だったのが、時間と共に増えていっているのが気がかりです。

myLittleAdimin で SELECT * FROM sys.database_files クエリを実行して調べたログファイルの size は、復元した時: 5MB ⇒ その約 11 時間後: 48MB ⇒ その約 1 時間後: 64MB ⇒ その約 1 時間後: 70MB と増えていくのが解せません。

ちなみに、データベースファイルの size は 4912(x 8 = 39,294 KB)でローカル環境から変化していません。また、復元した UserDatabase に対して DBCC CHECKDB を実行してみましたが、エラーなしという結果でした。

何かまだ問題があるような気がしています。ホスティングサービス会社に、サーバーに障害や設定上の問題等はないか確認依頼中です。

上に、「何故一回目がうまく行かなかったのかは謎です」と書きましたが、SQL Server 2005 を使って作業した時も同じで、その原因はログファイルが壊れていて、そこが修復できていないからではないかという気もしています。


【2015/6/17 追記】

上の【2015/6/16 追記】に書きましたように、ホスティングサービス会社にサーバーに障害や設定上の問題等はないか確認をお願いしていましたが問題ないとの返答がありました。

昨日はデータベースの更新は行ってないのにログファイルの size がどんどん増えていくのが解せなかったのですが、今日 size を調べてみたところ落ち着いてきていました。もう少し様子を見て、まだ問題があるようでしたらまたここに書くことにします。

Tags:

SQL Server

既定のインスタンスの名前は?

by WebSurfer 17. November 2012 09:47

SQL Server の「既定のインスタンス」のインスタンス名は何でしょうか?

下の画像を見てください。「既定のインスタンス」としてインストールした SQL Server 2005 Developer Edition のインスタンス名を Transact-SQL の SERVERPROPERTY 関数で取得した結果です。結果は MSSQLSERVER ではなく NULL になります。

SERVERPROPERTY 関数の実行結果

MSDN ライブラリ SERVERPROPERTY (Transact-SQL) によると、既定インスタンスの場合 NULL になるそうです。上の画像の通りですね。つまり「既定のインスタンス」には名前はないということのようです。

ちなみに、デフォルト設定でインストールした Express 版(「名前付きインスタンス」になる)で同様にインスタンス名を取得すると、結果は期待したとおり SQLEXPRESS になります。

SQL Server Management Studio で調べることもできます。[オブジェクトエクスプローラー]⇒[サーバーのプロパティ]⇒[接続のプロパティ]と進んで、その画面の「インスタンス名」欄を見ると、既定のインスタンスの場合は以下の画像のように空白になっているはずです。

既定のインスタンスの名前(空白)

という訳で、実験では、「既定のインスタンス」には名前はないという結果になりました。

ところが、それを裏付ける文書が見つかりません。唯一見つけたのが、MSDN ライブラリ 名前付きインスタンスの使用 で、"インスタンスには、既定のインスタンス (名前が指定されていないインスタンス) と名前付きインスタンスがあります。"(英文では "An instance is either the default, unnamed instance, or it is a named instance. ")と書いてあります。

逆に、「既定のインスタンス」のインスタンス名は MSSQLSERVER という記述はいたるところで目にします。例えば、MSDN ライブラリ インスタンスの構成 には "既定のインスタンス名は MSSQLSERVER です。"(英文では "The default instance name is MSSQLSERVER.")とはっきり書いてあったりします。

多数決(笑)だと、後者(名前は MSSQLSERVER)に軍配が上がるようですが、実際どっちが本当なんでしょう?

自分は前者(名前はない)が正しく、MSSQLSERVER というのはサービス名であって、インスタンス名ではないと勝手に思っています。

----- 2012/11/18 追記 -----

MSSQLSERVER を指定して接続にトライ

試しに「既定のインスタンス」にインスタンス名 MSSQLSERVER を指定して接続にトライしてみました。"接続文字列が有効ではありません [87]" というエラーメッセージが出て接続に失敗します。

左のサムネイル画像をクリックすると原寸大の画像が表示されますので、エラーメッセージの詳細などはそれを見てください。

原寸大の画像の上のほうに示されているように、コンピュータ名 (papiko-pc) のみを指定した場合は接続に成功します。

という訳で、やはり「既定のインスタンス」にはインスタンス名はないのではと思います。

Tags:

SQL Server

SQLEXPRESS は「名前つきインスタンス」名

by WebSurfer 12. November 2012 21:53

SQL Server の Express 版をインストールすると、デフォルトでは「名前つきインスタンス」となり、インスタンス名は SQLEXPRESS になります。

知ってました? 実は、自分は勘違いしていまして、SQLEXPRESS は Express 版の「既定のインスタンス」の名前だとばかり思っていました。(汗)

そうではなくて、一台のコンピューターにインストールできる「既定のインスタンス」は一つだけで、通常版/Express 版を問わず無名になるそうです。

時々 MSSQLSERVER という名前を目にしますが、どうやらそれはサービス名で、インスタンス名ではなさそうです。ちなみに、「名前つきインスタンス」のサービス名は MSSQL$<インスタンス名> になります。

詳しくは以下のページが参考になると思います。

名前付きインスタンスの使用

Windows サービス アカウントの設定

SQL Server 2012 Express のインストール

「既定のインスタンス」と「名前つきインスタンス」の主な違いは、名前の有無の他に、TCP/IP を利用してのリモート接続の際にポートが固定 (TCP 1433) になるか動的になるかです。

「名前つきインスタンス」の場合は、SQL Server の起動時に使用可能なポートが動的に割り当てられるので、接続には SQL Server Browser が必要になります。

ただし、「既定のインスタンス」が存在しなければ、Express 版を「名前付きインスタンス」SQLEXPRESS としてインストールしても、固定ポート (TCP 1433) を使うように設定を変更すれば SQL Server Browser を使用せずに接続することができます。

その場合、気をつけなければいけないのが接続文字列で、プロバイダに SqlClient を使用し SQLEXPRESS という名前の「名前つきインスタンス」に接続する場合は以下のいずれかに設定します。(2015/7/25 誤記訂正)

Data Source=tcp:<server name>
Data Source=tcp:<server name>,1433
Data Source=tcp:<server name>\SQLEXPRESS,1433

ちなみに、Data Source=tcp:<server name>\SQLEXPRESS とするとエラーになります。その理由は、サーバー名\インスタンス名で接続をした場合には、UDP 1434 (SQL Browser サービス) に接続して、指定したインスタンス名のポート番号を取得し、その後対象ポートに接続をする動作になるからだそうです。

また、プロバイダが Microsoft OLE DB Provider for SQL Server の場合、Data Source=tcp:<server name> ではエラーになるという報告もありますので注意してください。

詳しくは MSDN Forum のスレッド「SQLServer2008Expressにリモート接続できない」の Masayuki.Ozawa さんの書き込みを見てください。

Express 版のリモート接続のための設定は以下のページが参考になると思います。

SQL Server 2008 Express にリモート接続

上のページに、"リモート接続の場合、Windows 7/Vista では、Windows 認証を利用することはできません。" とありますが、Active Directory ドメイン環境でドメインアカウントを使用すれば Windows 認証は使用できます。

ワークグループ環境でも、以下のページに書いてある設定をすれば Windows 認証が使えるそうです(未検証です)。

ワークグループ環境にあるSQL SERVER 2005 EXPRESSでWINDOWS認証を使ってネットワーク接続する際の注意点

上の記事がリンク切れになると困るので、要点だけ書いておくと、サーバー / クライアント両方に同一 Windows ユーザーアカウントを設けて SQL Server の認証・承認の権限を与えるのはもちろん、ファイルの共有を無効にしておくことだそうです。(共有を有効にすると、サーバーに Guest としてアクセスしているとみなされるからだそうです。XP の話なので、Vista, 7, 8 でも同じかどうかは不明です)

Tags: ,

SQL Server

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  September 2021  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar