SQL数据库恢复模型(日志)

----------------------------------------------------------------------
1、SQL数据库恢复模型
----------------------------------------------------------------------
1）完全恢复模型
-----------------
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）能还原全部数据，并可以将数据库恢复到任意指定的时刻。
（4）为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。
------------------
（2）大容量日志记录恢复模型
------------------
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）日志中不记录*作细节（如select into、create index等），而只记录*作的最终结果，因此占用日志空间小。
（4）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。
-------------------
（3）简单恢复模型
-------------------
（1）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。
（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。
（3）不适于生产系统和大规模*作环境下选用。
-----------------------------------------
alter database d1 set recovery simple--设置数据库恢复模型
alter database d1 set recovery bulk_logged
alter database d1 set recovery full
----------------------------------------------------------------------

 

在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

    在实际情况中，你应该选择使用哪种恢复模型呢？答案在于你能承受丢失多少数据。

在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

    在实际情况中，你应该选择使用哪种恢复模型呢？答案在于你能承受丢失多少数据。

 

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

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