◆◇◇◆━━━━━━━━━━━━━━━━━━━━2009.10.28━━━・‥…
えす！エス！レスキュー SQL Server
-どっぷりリファレン中！！-
・‥…━━━━━━━━━━━━━━━━━━━━━Vol. 4━━━━◆◇◇◆
<<目次>>
■SQL Server リファレン中！！ その４
■お知らせ・・・○ご質問について
■編集者より
◎バックナンバーココから！！
http://www.insight-tec.com/mailmagazine/ssql/index.html
┏─────────────────────────────────┓
┃SQL Server リファレン中！！ ┃
┗─────────────────────────────────┛
======================================================================
アジェンダ
======================================================================
今回のメルマガでは以下の内容をお伝えします。
・データ復旧とトランザクションログ
・完全復旧モデルのデータベース作成
・トランザクションログのバックアップ
・バックアップからの復旧
======================================================================
データ復旧とトランザクションログ
======================================================================
データベースにおいて、データの復元はキーポイントとなります。
そのデータの復元において、データベース自体のバックアップ
およびトランザクションログのバックアップは必要なデータとなります。
トランザクションログには『操作記録』が入っているため、
最後に取ったバックアップ以降のトランザクションログがあれば、
そのトランザクションログにある『操作記録』から処理を実行させ、
元の状態に戻すことが出来るからです。
SQL Serverには復旧モデルが3パターン存在しており、
復旧モデルによってトランザクションログが必須でない場合もあります。
<< 復旧モデル >>
・完全復旧モデル
バックアップとトランザクションログのバックアップがあれば、
データの完全復旧が可能なモデルであり、
また、特定時点への復元が実行可能。
トランザクションログは、データベースやトランザクションログ自体の
バックアップが実行されるまで再利用待ちの状態になり、
ログの切捨ては行われない。
# 参照：トランザクション ログの切り捨て
# http://msdn.microsoft.com/ja-jp/library/ms189085.aspx
・一括ログ復旧モデル
このモデルは、一括データインポートなど、
特定の大規模一括操作パフォーマンスを向上させるために
存在するモードで、このモデルの場合、トランザクションログには、
最小限の操作しか記録されないため、完全復旧には向かない。
一括でインポートなどにするとき等、
トランザクションログが無くても良い場合に、
一時的にこの復旧モデルにするといった使用方法が推奨されている。
# 参照：一括ログ復旧モデルでのバックアップ
# http://msdn.microsoft.com/ja-jp/library/ms190692.aspx
・単純復旧モデル
最新のバックアップおよび差分バックアップからの復旧モデルで、
バックアップが必須となる。
トランザクションログは自動的に切り捨てられ、
バックアップ以降に行われた操作が消失する。
そのためこの復旧モデルの場合は、ログを使った復元はほぼ不可能。
# 参照：ファイル復元の実行 (単純復旧モデル)
# http://msdn.microsoft.com/ja-jp/library/ms190388.aspx
今回の検証では、完全復旧モデルで、完全バックアップと
トランザクションログからのデータ復元を行います。
前半部分ではトランザクションログの運用を検証し、
データベースやトランザクションログのバックアップを行わないと、
SQLがエラーになることを確認し、ログのバックアップ方法を行います。
後半部分では、実際にバックアップしたデータベースと
トランザクションログからデータを復元する事を行います。
======================================================================
完全復旧モデルのデータベース作成
======================================================================
今回使用するデータベースの作成を行います。
前回と違い、ログファイルのサイズや増加、最大サイズを指定しています。
また、作成したminiデータベースの復旧モデルを完全復旧モデルにする
ALTER DATABASE文が追加されています。
<< SQL文 >>
/********************************************************************/
CREATE DATABASE mini ON
PRIMARY
(
NAME = N'miniPri',
FILENAME = N'C:\SqlServerData\mini1.mdf' ,
SIZE = 3MB ,
FILEGROWTH = 1KB
),
FILEGROUP mini2 DEFAULT
(
NAME = N'mini2',
FILENAME = N'C:\SqlServerData\mini2.mdf' ,
SIZE = 512KB ,
FILEGROWTH = 1KB
),
FILEGROUP mini3
(
NAME = N'mini3',
FILENAME = N'C:\SqlServerData\mini3.mdf' ,
SIZE = 512KB ,
FILEGROWTH = 1KB
)
LOG ON
(
NAME = N'log',
FILENAME = N'C:\SqlServerData\mini.ldf',
SIZE = 512KB ,
MAXSIZE = 1MB,
FILEGROWTH = 1KB
)
GO
ALTER DATABASE [mini] SET RECOVERY FULL;
GO
use [mini]
CREATE TABLE [tb1](
[id] [decimal](18, 0) NOT NULL,
[col1] [nvarchar](4000) NULL,
[col2] [nvarchar](max) NULL,
[col3] [nvarchar](max) NULL,
CONSTRAINT [pktb1] PRIMARY KEY CLUSTERED
(
[id] ASC
)ON mini2
) ON mini2
GO
/********************************************************************/
# ALTER DATABASE
# http://msdn.microsoft.com/ja-jp/library/bb522682.aspx
作成したminiデータベースの復旧モデルを、
下記SQLを使って確認してみます。
<< SQL文 >>
/********************************************************************/
SELECT
name,
create_date,
state_desc,
recovery_model_desc,
log_reuse_wait,
log_reuse_wait_desc
FROM
sys.databases
WHERE
database_id = DB_ID(N'mini');
/********************************************************************/
# sys.databases
# http://msdn.microsoft.com/ja-jp/library/ms178534.aspx
<< 実行結果 >>
/********************************************************************/
name create_date state_desc recovery_model_desc
---- ----------------------- ---------- -------------------
mini 2009-10-07 11:54:40.613 ONLINE FULL
log_reuse_wait log_reuse_wait_desc
-------------- -------------------
0 NOTHING
/********************************************************************/
recovery_model_descにFULLと表示され、完全バックアップの復旧モデルで
miniデータベースが設定されている事が分かります。
======================================================================
トランザクションログのバックアップ
======================================================================
トランザクションログの再利用待機状態を知るためには、
先ほどの実行結果に出力されている、「log_reuse_wait」に着目します。
log_reuse_waitはトランザクションログの再利用待機状態を表しています。
データベース作成直後では、log_reuse_waitが0＝NOTHINGの状態で、
再利用待機状態にあるログがない事が分かります。
この状態からデータを2000件追加し、
トランザクションログの情報を確認してみます。
<< SQL文 >>
/********************************************************************/
use [mini]
GO
BEGIN
DECLARE @count int;
Truncate table tb1;
BEGIN TRANSACTION;
BEGIN TRY
select @count = case when MAX(id) IS NULL
then 1 else MAX(id)+1 end from tb1;
WHILE @count <= 2000
BEGIN
insert into tb1 select @count, null,null,null;
set @count += 1;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT @count;
COMMIT TRANSACTION;
END CATCH
END;
SELECT
name,
create_date,
state_desc,
recovery_model_desc,
log_reuse_wait,
log_reuse_wait_desc
FROM
sys.databases
WHERE
database_id = DB_ID(N'mini');
/********************************************************************/
<< 実行結果 >>
/********************************************************************/
name create_date state_desc recovery_model_desc
---- ----------------------- ---------- -------------------
mini 2009-10-07 12:03:09.740 ONLINE FULL
log_reuse_wait log_reuse_wait_desc
-------------- -------------------
4 ACTIVE_TRANSACTION
/********************************************************************/
log_reuse_waitに4＝ACTIVE_TRANSACTIONが表示され、
アクティブなトランザクションによって
再利用待機状態のトランザクションログがある事が示されています。
この状態から「WHILE @count <= 2000」→「WHILE @count <= 4000」にし、
SQLを再実行します。
すると、クエリがエラーになり、下記メッセージが出力されます。
<< 実行結果 >>
/********************************************************************/
メッセージ 3930、レベル 16、状態 1、行 18
現在のトランザクションのコミットおよびログ ファイルに書き込む操作は
サポートできません。トランザクションをロールバックしてください。
/********************************************************************/
ここでトランザクションの使用可能な領域を作成するため、
データベースのバックアップと
トランザクションログのバックアップを取ります。
<< SQL文 >>
/********************************************************************/
BACKUP DATABASE [mini]
TO DISK = N'C:\SqlServerData\back.bck'
BACKUP LOG [mini]
TO DISK = N'C:\SqlServerData\log_back.log'
/********************************************************************/
<< 実行結果 >>
/********************************************************************/
データベース 'mini' の 184 ページ、ファイル 1 のファイル 'miniPri' を
処理しました。
データベース 'mini' の 16 ページ、ファイル 1 のファイル 'mini2' を
処理しました。
データベース 'mini' の 8 ページ、ファイル 1 のファイル 'mini3' を
処理しました。
データベース 'mini' の 2 ページ、ファイル 1 のファイル 'log' を
処理しました。
BACKUP DATABASE により 210 ページが 0.354 秒間で
正常に処理されました (4.626 MB/秒)。
データベース 'mini' の 3 ページ、ファイル 1 のファイル 'log' を
処理しました。
BACKUP LOG により 3 ページが 0.163 秒間で正常に処理されました
(0.131 MB/秒)。
/********************************************************************/
この状態から「WHILE @count <= 4000」→「WHILE @count <= 10000」にし、
SQLを再実行し、データの追加と、ログの状態を確認します。
<< 実行結果 >>
/********************************************************************/
name create_date state_desc recovery_model_desc
---- ----------------------- ---------- -------------------
mini 2009-10-07 12:03:09.740 ONLINE FULL
log_reuse_wait log_reuse_wait_desc
-------------- -------------------
2 LOG_BACKUP
/********************************************************************/
そのまま、同じSQLを再実行すると、以下のエラーメッセージが出力されます。
<< 実行結果 >>
/********************************************************************/
データベース 'mini' のトランザクション ログがいっぱいです。
ログの領域を再利用できない理由を確認するには、
sys.databases の log_reuse_wait_desc 列を参照してください。
/********************************************************************/
では、この状態でBACKUP DATABASEを実行してみます。
すると以下のメッセージが表示されます。
<< 実行結果 >>
/********************************************************************/
メッセージ 3013、レベル 16、状態 1、行 1
BACKUP DATABASE が異常終了しています。
データベース 'mini' の 97 ページ、ファイル 2 のファイル 'log' を
処理しました。
BACKUP LOG により 97 ページが 0.212 秒間で正常に処理されました
(3.553 MB/秒)。
メッセージ 9002、レベル 17、状態 2、行 1
データベース 'mini' のトランザクション ログがいっぱいです。
ログの領域を再利用できない理由を確認するには、sys.databases の
log_reuse_wait_desc 列を参照してください。
/********************************************************************/
このメッセージによって、BACKUP DATABASEを行うにも、
トランザクションログが出力されている事が分ります。
よって、トランザクションログが一杯になってしまった場合には、
BACKUP DATABASEの前にBACKUP LOGを実行する必要がありますので、
順番を入れ替えます。
<< SQL文 >>
/********************************************************************/
use [mini]
BACKUP LOG [mini]
TO DISK = N'C:\SqlServerData\log_back.log'
BACKUP DATABASE [mini]
TO DISK = N'C:\SqlServerData\back.bck'
/********************************************************************/
<< 実行結果 >>
/********************************************************************/
データベース 'mini' の 28 ページ、ファイル 3 のファイル 'log' を
処理しました。
BACKUP LOG により 28 ページが 0.116 秒間で正常に処理されました
(1.868 MB/秒)。
データベース 'mini' の 184 ページ、ファイル 2 のファイル 'miniPri' を
処理しました。
データベース 'mini' の 16 ページ、ファイル 2 のファイル 'mini2' を
処理しました。
データベース 'mini' の 8 ページ、ファイル 2 のファイル 'mini3' を
処理しました。
データベース 'mini' の 1 ページ、ファイル 2 のファイル 'log' を
処理しました。
BACKUP DATABASE により 209 ページが 0.290 秒間で正常に処理されました
(5.630 MB/秒)。
/********************************************************************/
BACKUP LOGによって、トランザクションログに再利用できる領域が作成され、
BACKUP DATABASEが実行できるようになりました。
それでは、先ほどのスクリプトの@countを10000→2000に戻し、
数回ほど実行してみます。
すると、先ほどと同じ『トランザクション ログがいっぱいです』という
エラーメッセージが出てきます。
この状態では、再実行できませんので、今度はBACKUP LOGだけを実行して、
トランザクションログの状態を確認してみると、log_reuse_wait=0になり、
再利用できる状態になった事が分ります。
ここで取ったトランザクションログのバックアップは、
BACKUP DATABASE以降の更新情報を含んでいるので、
復旧時に、使用するバックアップログファイルになりますので、
誤って削除しないように注意が必要です。
======================================================================
バックアップからの復旧
======================================================================
では、バックアップからの復元を試してみようと思います。
最初にログの状態と、テーブルのデータを確認します。
<< SQL文 >>
/********************************************************************/
SELECT
name,
create_date,
state_desc,
recovery_model_desc,
log_reuse_wait,
log_reuse_wait_desc
FROM
sys.databases
WHERE
database_id = DB_ID(N'mini');
use [mini]
go
SELECT
*
FROM
tb1;
/********************************************************************/
<< 実行結果 >>
/********************************************************************/
name create_date state_desc recovery_model_desc
---- ----------------------- ---------- -------------------
mini 2009-10-07 12:03:09.740 ONLINE FULL
log_reuse_wait log_reuse_wait_desc
-------------- -------------------
0 NOTHING
id col1 col2 col3
-- ---- ---- ----
/********************************************************************/
この状態から以下のスクリプトを実行し、
トランザクションログの状態を確認します。
<< SQL文 >>
/********************************************************************/
USE [mini]
GO
TRUNCATE TABLE TB1;
GO
BEGIN
DECLARE @count int;
BEGIN TRANSACTION;
BEGIN TRY
select @count = case when MAX(id) IS NULL
then 1 else MAX(id)+1 end from tb1;
while @count < 2000
BEGIN
insert into tb1 select @count, N'test',N'test',null;
set @count += 1;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT @count;
ROLLBACK TRANSACTION;
END CATCH
END;
/********************************************************************/
<< ログ、データ状態 >>
/********************************************************************/
name create_date state_desc recovery_model_desc
---- ----------------------- ---------- -------------------
mini 2009-10-07 12:03:09.740 ONLINE FULL
log_reuse_wait log_reuse_wait_desc
-------------- -------------------
4 ACTIVE_TRANSACTION
id col1 col2 col3
-- ---- ---- ----
1 test test NULL
2 test test NULL
(以下、略)
/********************************************************************/
このテーブルのデータ、トランザクションログの状態で、
DATABASE BACKUP、トランザクションログバックアップを実行します。
<< SQL文 >>
/********************************************************************/
BACKUP LOG [mini]
TO DISK = N'C:\SqlServerData\log_back.log'
BACKUP DATABASE [mini]
TO DISK = N'C:\SqlServerData\back.bck'
/********************************************************************/
次に下記のSQLを実行し、テーブルに格納されているデータを入れ替えて、
ログの状態を確認します。
<< SQL文 >>
/********************************************************************/
USE [mini]
GO
TRUNCATE TABLE TB1;
GO
BEGIN
DECLARE @count int;
BEGIN TRANSACTION;
BEGIN TRY
select @count = case when MAX(id) IS NULL
then 1 else MAX(id)+1 end from tb1;
while @count < 2000
BEGIN
insert into tb1 select @count, N'test',N'test',N'test';
set @count += 1;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT @count;
ROLLBACK TRANSACTION;
END CATCH
END;
/********************************************************************/
<< ログ、データ状態 >>
/********************************************************************/
name create_date state_desc recovery_model_desc
---- ----------------------- ---------- -------------------
mini 2009-10-07 12:03:09.740 ONLINE FULL
log_reuse_wait log_reuse_wait_desc
-------------- -------------------
2 LOG_BACKUP
id col1 col2 col3
-- ---- ---- ----
1 test test test
2 test test test
(以下、略)
/********************************************************************/
ここでトランザクションログファイルだけバックアップを取ります。
<< SQL文 >>
/********************************************************************/
BACKUP LOG [mini]
TO DISK = N'C:\SqlServerData\log_back_norecovery.log'
/********************************************************************/
さてこの状態で、tb1のデータを誤って『全消去』してしまったとしましょう。
<< SQL文 >>
/********************************************************************/
USE [mini]
GO
TRUNCATE TABLE TB1;
GO
/********************************************************************/
BACKUP DATABASEで取ったデータはtb1.col3 = nullのデータです。
消す直前のデータではtb1.col3 = N'test'となっていました。
その更新内容は、バックアップログを適用する事で復元できます。
<< SQL文 >>
/********************************************************************/
use [master]
ALTER DATABASE [mini] SET SINGLE_USER ;
BACKUP LOG [mini]
TO DISK = N'C:\SqlServerData\log_back_norecovery2.log'
WITH NORECOVERY
RESTORE DATABASE [mini]
FROM DISK = N'C:\SqlServerData\back.bck'
WITH NORECOVERY
RESTORE LOG [mini]
FROM DISK = N'C:\SqlServerData\log_back.log'
WITH NORECOVERY
RESTORE LOG [mini]
FROM DISK = N'C:\SqlServerData\log_back_norecovery.log'
WITH NORECOVERY
RESTORE DATABASE [mini]
WITH RECOVERY
/********************************************************************/
<<実行結果>>
/********************************************************************/
データベース 'mini' の 12 ページ、ファイル 1 のファイル 'log' を
処理しました。
BACKUP LOG により 12 ページが 0.224 秒間で正常に処理されました
(0.398 MB/秒)。
データベース 'mini' の 176 ページ、ファイル 1 のファイル 'miniPri'
を処理しました。
データベース 'mini' の 32 ページ、ファイル 1 のファイル 'mini2' を
処理しました。
データベース 'mini' の 8 ページ、ファイル 1 のファイル 'mini3' を
処理しました。
データベース 'mini' の 1 ページ、ファイル 1 のファイル 'log' を
処理しました。
RESTORE DATABASE により 217 ページが 0.127 秒間で正常に処理されました
(13.295 MB/秒)。
データベース 'mini' の 0 ページ、ファイル 1 のファイル 'miniPri' を
処理しました。
データベース 'mini' の 0 ページ、ファイル 1 のファイル 'mini2' を
処理しました。
データベース 'mini' の 0 ページ、ファイル 1 のファイル 'mini3' を
処理しました。
データベース 'mini' の 2 ページ、ファイル 1 のファイル 'log' を
処理しました。
RESTORE LOG により 2 ページが 0.036 秒間で正常に処理されました
(0.271 MB/秒)。
データベース 'mini' の 0 ページ、ファイル 1 のファイル 'miniPri' を
処理しました。
データベース 'mini' の 0 ページ、ファイル 1 のファイル 'mini2' を
処理しました。
データベース 'mini' の 0 ページ、ファイル 1 のファイル 'mini3' を
処理しました。
データベース 'mini' の 43 ページ、ファイル 1 のファイル 'log' を
処理しました。
RESTORE LOG により 43 ページが 0.047 秒間で正常に処理されました
(6.991 MB/秒)。
RESTORE DATABASE により 0 ページが 0.795 秒間で正常に処理されました
(0.000 MB/秒)。
/********************************************************************/
上記SQLを実行した事で、下記実行結果ののように復元できました。
log_back_norecovery2.logをRESTORE LOGの対象にしなかったのは、
誤って行われた『truncate table』の操作ログが格納されているためです。
<< 実行後のログ、データ状態 >>
/********************************************************************/
name create_date state_desc recovery_model_desc
---- ----------------------- ---------- -------------------
mini 2009-10-07 12:03:09.740 ONLINE FULL
log_reuse_wait log_reuse_wait_desc
-------------- -------------------
0 NOTHING
id col1 col2 col3
-- ---- ---- ----
1 test test test
2 test test test
(以下、略)
/********************************************************************/
もし、tb1.col3の値がNULLのときに復元する場合は、
log_back_norecovery.logをリカバリ対象から外せば行えます。
<< SQL文 >>
/********************************************************************/
use [master]
ALTER DATABASE [mini] SET SINGLE_USER ;
BACKUP LOG [mini]
TO DISK = N'C:\SqlServerData\log_back_norecovery2.log'
WITH NORECOVERY
RESTORE DATABASE [mini]
FROM DISK = N'C:\SqlServerData\back.bck'
WITH NORECOVERY
RESTORE LOG [mini]
FROM DISK = N'C:\SqlServerData\log_back.log'
WITH NORECOVERY
RESTORE DATABASE [mini]
WITH RECOVERY
/********************************************************************/
<< 実行後ログ、データ状態 >>
/********************************************************************/
name create_date state_desc recovery_model_desc
---- ----------------------- ---------- -------------------
mini 2009-10-07 12:03:09.740 ONLINE FULL
log_reuse_wait log_reuse_wait_desc
-------------- -------------------
0 NOTHING
id col1 col2 col3
-- ---- ---- ----
1 test test NULL
2 test test NULL
(以下、略)
/********************************************************************/
======================================================================
まとめ
======================================================================
復旧モードには以下の3種類ある。
・完全復旧モデル
・一括ログ復旧モデル
・単純復旧モデル
完全復旧モデルでは、トランザクションログのバックアップまたは
データベースのバックアップが取られない限り、トランザクションログの
再利用はされない。それによって、トランザクションログの
使用可能領域が無くなると、SQLが実行できなくなる。
トランザクションログには、BACKUP DATABASEの情報も追加される。
データベースの復元には、完全バックアップ(+差分バックアップ)および
トランザクションログのバックアップを使用して復元させることが出来る。
┏─────────────────────────────────┓
┃ご質問について ┃
┗─────────────────────────────────┛
＜皆様からのご質問を受付けております＞
皆様のご質問にはできるだけ、お答えしたいと思っています。
すべてのご質問にお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非お気軽に下記アドレス
までお寄せください。
ご意見、ご感想などもお待ちしておりますっ！！
mailto:letter@insight-tec.co.jp
┏─────────────────────────────────┓
┃編集者より ┃
┗─────────────────────────────────┛
友達と飲んだ後に飲み足らず、コンビニでお酒を買いに言ったところ。。。缶
コーヒーのおまけに小型「キン肉マン」フィギュアーを発見！！大人３人で真
剣に物色後、２つ購入。翌朝、冷蔵庫にいるのを見つけて何故あそこまでテン
ションが上がったのか未だ不明。そんなに好きでもないのに。。。見るたびに
若干後悔してならないここ数日。 by TI
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜えす！エス！レスキュー SQL Server -どっぷりリファレン中！！-＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com
mailto:letter@insight-tec.co.jp
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright(c) 2009, Insight Technology, Inc., All Rights Reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
