你真的会使用SQL Server的备份还原功能吗？

之一：恢复模型：

在SQL Server中，除了系统数据库外，你创建的每一个数据库都有三种可供选择的恢复模型: Simple(简单), full(完整), bulk-logged(批量日志)。 下面这条语句可以显示出所有在线数据库的恢复模型:
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master..sysdatabases ORDER BY name

SQL Server 2005及以上版本也可以使用下面这条语句来查看:
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

如果想改变数据库的恢复模型，可以使用下面SQL语句:

简单恢复模型:ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
完整恢复模型:ALTER DATABASE AdventureWorks SET RECOVERY FULL
批量日志恢复模型:ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

在实际情况中，你应该选择使用哪种恢复模型呢？答案在于你能承受丢失多少数据。让我们用下面这些图表来说明这三种恢复模型之间的不同。下面这张图是一个数据库分别在9点和11点进行了一次完整备份。

 

1.简单恢复模型

假设硬件在10:45分时坏了。 如果数据库使用的是简单模型的话，那你将要丢失105分钟的数据。因为你可以恢复的最近的时间点是9点，9点之后的数据将全部丢失。当然你可以使用差异备份来分段运行，如下图:

像这样使用差异性备份的话，你将丢失45分钟的数据。现在，假设用户在9:50删除了一张很重要的表，你能恢复删除点之前的数据吗？答案当然是No。因为差异性备份仅仅包含数据页的修改，它不能用于恢复一个指定的时间点。你不得不把数据库恢复到9点的状态，然后重做后面49分钟的事情。

2.完整恢复模型

假如在9点和11点之间没有进行事务日志的备份，那么你将面临和使用简单恢复模型一样的情况。另外，事务日志文件会很大，因为SQL Server不会删除已经提交和已经CheckPoint的事务，直到它们被备份。

假设每30分钟备份一次事务日志：

假如硬件在10:45分时坏了，那你只会丢失15分钟的数据。你可以使用9点的完整备份及直到10:30的事务日志来恢复。假如9:50分删除了重要数据怎么办呢？没关系，你可以使用在10点备份的事务日志，把数据库恢复到9:49分的状态。 因为你恢复时无法直接跳过9:50那次误删除的操作日志而恢复9:50之后的数据, 所以你还必须重做误删除之后的操作。不过，这已经是不错的选择了。

注意：市场上有一些工具，可以使用事务日志来恢复用户误操作而丢失的数据，就是利用了上述原理

3.批量日志恢复模型

批量日志恢复模型被定义成一种最小化事务日志的完整恢复模型。例如select into就是一种最小化事务日志，假设这种事务发生在9:40分

这个事务将被最小化的记录下来，这就意味着SQL Server仅仅记录由于这个事务而产生的数据页的变化，它不记录每一条插入到数据表中的数据。假如9:50时一个重要的表数据被删除了，那意味着什么呢？意味着你不能把数据库再恢复到9:49分的状态了，因为事务日志在10点时被备份并且不能恢复到一个指定的时间点上。你只能把数据库恢复到9:30分的状态。你要记住，无论在什么时候，只要事务日志备份包含一个或多个最小化日志事务，那你就不能再把备份还原到一个指定的时间点了。

既然如此，那人们为什么还要使用批量日志恢复模型呢？一个最主要的原因就是性能。让我们以select into以例，从一个结果集来创建一张大表。假如你使用完整备份模型，那这张表中的每一条插入的数据都被记录下来，事务日志会消耗很多磁盘空间。假如你使用批量日志恢复模型，那么仅仅会记录数据页的修改细节以达到最好的性能。就像我们刚才描述的那样，使用事务日志的好处就是可以恢复到某一个指定的恢复点，但是会大大影响性能。

下面的几种操作都会最小化日志操作:
·批量导入操作(例如:INSERT ... SELECT * FROM OPENROWSET(BULK...), and BULK INSERT)
·select into 操作
·使用update来更新部分的大数据值数据类型。写入语句是插入或是追加数据，注意当被更新的数据存在时最小化日志不会被记录
·假如数据库恢复模型被设置为批量恢复或是简单恢复，那么一些索引的DDL操作会产生最小化日志，无论这个操作是在线还是离线被执行
·删除索引新建堆时

注意：当一个数据库的数据文件不可用时(也许是硬件坏了)，假如媒介依旧在线可用，那么你依然可以备份事务日志文件。但是你需要确定backup log命令一定要加上no_truncate选项。这样你就可以备份硬件毁坏前的事务日志了，这种方法常用来备份事务日志结尾。

然则，假如你的数据库使用批量日志恢复模型且事务日志包括最小化日志事务，那么包括被修改过的页的数据文件一定要可用，假如数据文件不可用了，也就意味着你将不能备份事务日志结尾。这也是使用批量日志恢复模型另一个需要考虑的地方。

总之，简单恢复模型提供了数量最少的恢复选项和最简单的管理模型。完整恢复模型在恢复数据库时允许更复杂的情况存在。批量恢复模型简化了一些复杂性，从而得到了更好的性能。大家可以从Books Online中得到这三种恢复更细致的对比。

之二：主要备份类型：

假设在下面几个时间段中，一个数据库积累插入了如下数据：

1.完整数据库备份

故名思意，完整数据库备份包括完整的数据库信息。它包括数据库的数据文件和备份结尾的部份活动事务日志。

完整备份基本语法如下：

BACKUP DATABASE AdventureWorks TO DISK = 'g:/backups/AdventureWorks.bak'

2.差异性数据库备份

差异性数据库备份包括自从上一次完整性备份以来所有改变的数据页，以及备份结尾的部份活动事务日志。

差异备份和完整备份的语法很相似，唯一不同的是加上了DIFFERENTIAL选项，如下：

BACKUP DATABASE AdventureWorks TO DISK = 'g:/backups/AdventureWorks.bak' WITH DIFFERENTIAL

3.事务日志备份

事务日志备份的内容是从还未被备份的事务日志开始，直到备份结尾的最后一个事务日志为止。需要注意的是执行事务日志的前提是你的数据库恢复模型必须是完整恢复模型或是批量日志恢复模型(恢复模型)

通常情况下大家会以为完整性备份会截断事务日志，以便下一次备份日志时不再重复备份，但这是错误的。完整备份不会截断事务日志。

事务日志备份的语法和完整备份相似，唯一不同的是第二个关键字，把database换成log，如下：

BACKUP LOG AdventureWorks TO DISK = 'g:/backups/AdventureWorks.bak'

4.你应该选择哪种备份类型呢？

答案取决于你本身，取决你的备灾恢复需求，取决于你可以接受的丢失数据量的多少，取决于你日常数据库的备份操作，取决于数据库从灾难中恢复过来的时间。

假如你的环境允许丢失5分钟的数据，那么你必须每5分钟执行一次某种备份。对于上述的几种备份来说，每5分钟执行一次完整备份的规则显得过于频繁且影响其他用户的操作。对于差异备份来说，如果一次完整备份后，数据库又进行了大量的修改，那么差异备份同样不适合。所以，上述情况用事务日志备份应该是最合适的，因为它仅备份从最后一次事务日志备份后所产生的新事务。

然而，当你从事务日志恢复数据库时，你需要还原所有相关的备份，包括从起始的数据库备份点到最后一个事务日志。假如还原点是很久以前的时间点，那么要还原的事务日志也许会很多很多。

你可以使用差异备份来提前事务日志备份的起始还原点。但是，对于一个行动的数据来说，差异备份会比事务日志备份占用更长的时间，影响更多的数据库操作和消耗更多的磁盘空间。

当你考虑使用哪种备份方案时，你可以参考下面这几点：

• 你能接受丢失多少数据？
• 备份会影响数据库日常操作吗？
• 是否有一个高可用的维护计划来执行备份操作？
• 你能接受的恢复时间是多少？
• 是否需要具有恢复某一时间点的功能？这种情况下，事务日志备份是必须的。
• 是否有足够的存储空间来保存备份文件？
• 是否有一套高可用性的解决来完成备灾恢复计划？比如：群集, loggshipping, 数据库镜像等等

之三：使用Copy的方式来备：

SQL Server允许使用简单的Copy方式来备份数据库的数据和日志文件吗？答案是肯定的。但前提是数据文件不再被SQL Server使用。

当数据库处于活动状态，你不能简单的copy它的文件，推荐方式是使用完整数据库备份(备份类型)。这是因为数据库的数据和日志文件不能处于非一致状态。这会导致仅仅在事务日志中被修改的数据页可能无法写入数据文件。这也会影响进程中的事务无法提交或是回滚已经写入文件中的数据。

下面是一个简单的例子:

10:00	Copying of data files start, takes 10 minutes.
10:05	A checkpoint occurs, modifies some pages at the beginning of the data file (which have already been copied), and some pages at the end of the data file (yet to be copied).
10:10	Copying of data files end, copy of transaction log starts.
10:15	Copying of transaction log ends.

你最后一步是copy事务日志，这就意味着数据文件已经包括了最后一次checkpoint以后的所有更改，但是实际上数据文件仅仅包括部分更改。

Copy一个已经被其他应用程序打开的文件，这种方式是非常有用的，但如上所述，这种方式好像并不适用于备份SQL Server数据库。假如你必须使用Copy方式来替换普通的数据库备份方式的话，推荐的方式是使用sp_detach_db存储过程来分离数据库:

sp_detach_db 'AdventureWorks'

SQL Server在把数据库从SQL Server实例上分离下来之前，会把所有的脏页都写回磁盘。然后，你就可以Copy数据库文件了，这时数据库文件也是处于一致性状态的。然后，你可以使用sp_attach_db来重新附加数据库:

sp_attach_db 'AdventureWorks', 'e:/mssql_data/AdventureWorks/AdventureWorks.mdf', 'e:/mssql_data/AdventureWorks_log.ldf'

假如你的数据库仅仅由数据文件组成，而且再也不需要事务日志文件了，那么使用sp_attach_single_file_db存储过程:

sp_attach_single_file_db 'AdventureWorks', 'e:/mssql_data/AdventureWorks/AdventureWorks.mdf'

当数据库实例处于离线状态时，你也可以安全的Copy数据库文件。

Copy数据库文件这种方式的主要略势是，你不得不把数据库离线一段时间，以便你来Copy数据文件。而且，你也不能使用附加的数据库作为差异备份和事务日志备份的起始点。

本文转自：http://blog.csdn.net/tjvictor/article/details/5210157

本文翻译自sqlbackuprestore，更多精彩内容请浏览http://www.sqlbackuprestore.com