源自: http://www.sqlservercentral.com/articles/Stairway+Series/73779/
作者: Tony Davis, 2011/09/07
翻译：刘琼滨 谢雪妮 许雅莉 赖慧芳
译文:
该系列
本文是楼梯系列的一部分:SQL Server中的事务日志管理的阶梯
当事情进展顺利时，不需要特别意识到事务日志是做什么的，或者它是如何工作的。您只需确信每个数据库都有正确的备份机制。当事情出错时，对事务日志的理解对于采取纠正措施是非常重要的，特别是当需要一个实时的数据库恢复时。Tony Davis给出了每个DBA都应该知道的正确的细节级别。
不能经常说，除非数据库是在简单的恢复模式下操作的，所以在事务日志上执行定期备份是非常重要的。这将控制事务日志的大小，并确保在发生灾难时，您能够在灾难发生前不久恢复数据库。这些事务日志备份将与常规的完整数据库(数据文件)备份一起执行
如果您在一个测试系统中，您不需要恢复到以前的时间点，或者很高兴能够恢复到最后一个完整的数据库备份，那么您应该以简单模式操作数据库。
让我们更详细地讨论这些问题。
备份的重要性
例如，考虑到SQL Server数据库“崩溃”的情况，可能是由于硬件故障，以及“实时”数据文件(mdf和ndf文件)，以及事务日志文件(ldf文件)，这些都无法访问。
在最坏的情况下，如果在其他地方没有这些文件的备份(副本)，那么您将遭受100%的数据损失。为了确保您能够恢复数据库，并在服务器崩溃之前的某个时间点恢复数据，或者在数据丢失或损坏之前，DBA需要定期对数据和日志文件进行备份。
DBA可以执行的备份主要有三种类型(尽管在简单的恢复模式中只有前两个应用程序):
完整的数据库备份——备份数据库中的所有数据。这实际上是在为给定数据库制作MDF文件的副本
差异数据库备份——复制自上一次完全备份以来发生的任何数据的副本。
事务日志备份—将所有日志记录的副本都插入到事务日志中，因为上一次事务日志备份(或数据库检查点，如果在简单的恢复模式下工作)。当进行日志备份时，日志通常会被截断，这样文件中的空间就可以被重用，尽管有些因素可能会延迟这一点(请参阅第8级-帮助，我的日志是满的)
一些初级dba和许多开发人员可能被“full”这个术语误导了，他们认为一个完整的数据库备份可以支持“一切”;事务日志的数据和内容。这是不正确的。本质上，完全和差异备份只备份数据，尽管它们也备份了足够的事务日志，以支持备份数据的恢复，并复制在备份过程中所做的任何更改。但是，在实际操作中，完整的数据库备份不会备份事务日志，因此不会导致事务日志的截断。只有事务日志备份导致日志的截断，所以执行日志备份是控制生产系统中日志文件大小的唯一正确方法。一些常见但不正确的方法将在8级帮助中讨论，我的日志已经满了。
文件和Filegroup备份
大型数据库有时会被组织成多个文件组，并且可以在单独的文件组或文件组内的文件中执行完整和不同的备份，而不是在整个数据库中执行。这个话题在这个楼梯上不会被进一步讨论。
恢复模型
SQL Server数据库备份和恢复操作发生在该数据库的恢复模型的上下文中。恢复模型是一个数据库属性，它决定您是否需要(甚至可以)备份事务日志和如何记录操作。对于可用的恢复操作，关于颗粒页面和文件还原也有一些不同，但是我们不会在本系列中介绍这些操作。
在一般操作中，数据库将以简单或完全恢复模式运行，两者之间最重要的区别如下:
简单—事务日志仅用于数据库恢复和回滚操作。它在定期检查点期间自动被截断。它不能被备份，因此不能用于将数据库恢复到以前存在的某个状态。
FULL -事务日志不会自动在周期性检查点被截断，因此可以备份并用于将数据恢复到以前的时间点，以及数据库恢复和回滚。日志文件只在日志备份发生时被截断。
还有第三个模式，bulk_log，在这个模式中，通常会生成大量写入事务日志的操作会执行更少的日志记录，以便不超过事务日志。
可以最低记录的操作
可以最小记录的操作示例包括批量导入操作(例如，使用bcp或批量插入)、选择/进入操作和某些索引操作，例如索引重构。一个完整的列表可以在这里找到:http://msdn.microsoft.com/en-us/library/ms191244.aspx。
一般来说，在完全恢复模式下运行的数据库可以临时切换到bulk_logging模式，以便在最少的日志记录下运行这些操作，然后切换回FULL模式。永久地在bulk_log模式下运行不是最小化事务日志大小的可行方法。我们将更详细地讨论这一点，以管理日志的大容量日志恢复模式。
选择正确的恢复模型
在完全恢复模式和简单模式之间选择操作数据库的主要标准如下:您愿意冒损失的风险的数据有多少?
在简单的恢复模式中，只有完全和差异备份是可能的。假设您完全依赖完全备份，每天早上2点执行一个备份，服务器在凌晨1点发生致命的崩溃。在这种情况下，您将能够恢复在前一个上午2点所采取的完整的数据库备份，并且将丢失23小时的数据。
在完全备份之间执行差异备份是可能的，以减少丢失风险的数据量。所有备份都是I / O密集型流程，但对于full和较小程度的差异备份来说尤其如此。它们可能会影响数据库的性能，因此在用户访问数据库时不应该运行它们。实际上，如果你在简单的恢复模式下工作，你暴露在数据丢失的风险下将会是几个小时的顺序。
如果一个数据库保存了业务关键数据，并且您希望您的数据损失以分钟而不是小时来度量，那么您将需要在完全恢复模式下操作数据库。在这种模式下，您需要进行完整的数据库备份，然后是一系列频繁的事务日志备份，然后是另一个完整的备份，等等。
在这种情况下，您可以在理论上恢复最新的、有效的完全备份(如果采用最新的差异备份，如果采用)，然后是可用日志文件备份的链，因为最后一次完全备份或差异备份。然后，在恢复过程中，在备份的日志文件中记录的所有操作都将向前滚动，以使数据库恢复到与灾难发生时非常接近的时间点。
再次，备份日志文件的频率将取决于您准备损失多少数据，以及服务器上的工作负载。在关键的财务或会计应用程序中，对数据丢失的容忍度或多或少是零，那么您可能每隔15分钟就会进行日志备份，或者可能更频繁地进行日志备份。在我们之前的例子中,这意味着您可以恢复2点完全备份,然后应用的每个日志文件,假设你有一个完整的日志链从你使用的完整备份数据库恢复的基础,一个在12.45点,15分钟前崩溃。实际上，如果在崩溃之后仍然可以访问当前日志，允许您执行尾部日志备份，那么您可能可以将数据损失最小化到接近于零。
日志链和尾日志备份…
…将级别5 -管理日志中详细讨论在模型全面复苏
当然,全面复苏会带来更高的维护开销,在创建和监测工作的额外的努力才能运行非常频繁的事务日志备份,这些备份的I / O资源需要(虽然时间短),和所需的磁盘空间来存储大量的备份文件。在为给定数据库选择适当的恢复模式之前，需要在业务级别上考虑这个问题。
设置和切换恢复模型
可以使用清单3.1中所示的下列简单命令之一来设置恢复模型。
USE master;
-- set recovery model to FULL
ALTER DATABASE TestDB
SET RECOVERY FULL;
-- set recovery model to SIMPLE
ALTER DATABASE TestDB
SET RECOVERY SIMPLE;
-- set recovery model to BULK_LOGGED
ALTER DATABASE TestDB
SET RECOVERY BULK_LOGGED;
清单3.1:设置数据库恢复模型
数据库将采用模型数据库指定的默认恢复模型。在许多情况下，这意味着数据库的“默认”恢复模型是完整的，但是不同版本的SQL Server可能对模型数据库有不同的缺省值。
发现经济复苏模型
理论上，我们可以通过执行清单3.2中所示的查询来找到给定数据库使用的模型。
SELECT name ,
recovery_model_desc
FROM sys.databases
WHERE name = 'TestDB' ;
GO
清单3.2:查询系统。恢复模型的数据库
但是，要小心这个查询，因为它可能并不总是说真话。例如，如果我们创建一个全新的数据库，然后立即运行清单3.2中的命令，它将报告数据库处于完全恢复模型中。然而，实际上，直到数据库备份全部完成后，数据库才会在自动截断模式下运行(即SIMPLE)。
通过在SQL Server 2008实例上创建一个新的数据库，我们可以看到这一点。我们使用一些测试数据创建一个表，然后检查恢复模型，如清单3.3所示。
/* STEP 1: CREATE THE DATABASE*/
USE master ;
IF EXISTS ( SELECT name
FROM sys.databases
WHERE name = 'TestDB' )
DROP DATABASE TestDB ;
CREATE DATABASE TestDB ON
(
NAME = TestDB_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'
) LOG ON
(
NAME = TestDB_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.ldf'
) ;
/*STEP 2: INSERT A MILLION ROWS INTO A TABLE*/
USE TestDB
GO
IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL
DROP TABLE dbo.LogTest ;
SELECT TOP 1000000
SomeID = IDENTITY( INT,1,1 ),
SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
+ CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,
SomeMoney = CAST(ABS(CHECKSUM(NEWID())) %
10000 / 100.0 AS MONEY) ,
SomeDate = CAST(RAND(CHECKSUM(NEWID()))
* 3653.0 + 36524.0 AS DATETIME) ,
SomeHex12 = RIGHT(NEWID(), 12)
INTO dbo.LogTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2 ;
SELECT name ,
recovery_model_desc
FROM sys.databases
WHERE name = 'TestDB' ;
GO
name recovery_model_desc
-------------------------------------------
TestDB FULL
清单3.3:一个新创建的TestDB数据库，分配了完整的恢复模型
这表明我们处于完全恢复模式，但是现在让我们检查日志空间的使用情况，强制一个检查点，然后重新检查日志的使用情况，如清单3.4所示。
DBCC SQLPERF(LOGSPACE) ;
-- DBCC SQLPERF reports a 110 MB log file about 90% full
CHECKPOINT
GO
DBCC SQLPERF(LOGSPACE) ;
-- DBCC SQLPERF reports a 100 MB log file about 6% full
清单3.4:日志文件在检查点被截断!
注意，日志文件的大小大致相同，但现在只有6%;日志已被截断，并提供了可重用的空间。尽管数据库被分配到完全恢复模式，但在第一个完整的数据库备份之前，它实际上并没有在该模式中运行。有趣的是，这意味着我们可以实现同样的效果，而不是显式强制一个检查点，而是运行TestDB数据库的完整备份。完整的备份操作触发一个检查点，日志被截断。
要确定恢复模型的运行情况，请执行清单3.5中所示的查询。
SELECT db_name(database_id) AS 'DatabaseName' ,
last_log_backup_lsn
FROM master.sys.database_recovery_status
WHERE database_id = db_id('TestDB') ;
GO
DatabaseName last_log_backup_lsn
-----------------------------------------------
TestDB NULL
清单3.5:数据库是否真的处于完全恢复模式?
如果在last_log_backup_lsn列中出现一个NULL值，那么该数据库实际上处于自动截断模式，当出现数据库检查点时将被截断。执行了完整的数据库备份之后，您将发现该列中填充了记录备份操作的日志记录的LSN，此时数据库真正处于完全恢复模式。从这一点开始，完整的数据库备份将不会对事务日志产生影响;截断日志的唯一方法是备份日志。
切换模式
如果您曾经将一个数据库从完整的或大容量的日志模式切换到简单模式，这将破坏日志链，您只能在切换之前将数据库恢复到最后一个日志备份的位置。因此，建议在切换之前立即进行日志备份。如果您随后将数据库从简单切换到完整或大容量日志模式，请记住，数据库实际上将继续在自动truncate模式下运行(清单3.5将显示NULL)，直到执行另一个完整备份。
如果您从FULL - bulk_log模式切换，那么这将不会破坏日志链。然而，在存储日志模式中发生的任何大量操作都不会完全登录到事务日志中，因此不能按操作逐个操作，就像完全记录的操作一样。这意味着不可能在包含大量操作的事务日志中及时恢复数据库。您只能恢复到日志文件的末尾。为了“重新启用”时间点恢复，在批量操作完成后切换回全模式，并立即进行日志备份。
自动化和验证备份
可以在SQL Server Management Studio中通过简单的t - SQL脚本执行特定的数据库和事务日志备份。但是，对于生产系统，DBA需要一种方法来自动化这些备份，并验证备份是否有效，并且可以用来恢复数据。
本主题的全部内容超出了本文的范围，但是下面列出了一些可用选项。由于SSMS维护计划的一些缺点，大多数有经验的dba会选择编写自己的脚本，然后将它们自动化。
SSMS维护计划向导和设计器-两个工具，内置到SSMS，允许您配置和调度一系列核心数据库维护任务，包括完整的数据库备份和事务日志备份。DBA还可以运行DBCC完整性检查、调度作业以删除旧的备份文件等等。在Brad McGhee的书中，可以找到关于这些工具及其局限性的优秀描述，Brad对SQL Server维护计划的可靠指南
t - sql脚本—您可以编写定制的t - sql脚本来自动化备份任务。Ola Hallengren提供了一套行之有效的维护脚本。他的脚本创建了各种存储过程，每个都执行特定的数据库维护任务，包括备份，以及使用SQL代理作业进行自动化。Richard Waymire的SQL Server代理是一个很好的信息来源。
Powershell / SMO脚本——比t - sql脚本更强大和通用，但是对于许多dba来说，它具有更陡峭的学习曲线，Powershell可以用于脚本，几乎可以自动执行任何维护任务。看到的,例如:http://www.simple-talk.com/author/allen-white/。
第三方备份工具——有一些第三方工具可以自动备份，以及验证和监控它们。大多数提供备份压缩和加密以及其他特性以简化备份管理、验证备份等等。例如Red Gate的SQL备份、Quest的LiteSpeed等等。
资源:
TLogStairway_Level3.sql
本文是SQL Server楼梯中的事务日志管理的一部分
在我们的RSS订阅中注册，当我们在楼梯上发布一个新级别的时候就会得到通知!