/*--SQL Server 中的最小日志记录操作
原作: happydreamer(小黑)
--整理..邹建--2003.12--*/
/*--控制数据库恢复选项
ALTER DATABASE 数据库名 SET RECOVERY <RECOVERY_OPTIONS>
<RECOVERY_OPTIONS> 的取值: FULL | BULK_LOGGED | SIMPLE
FULL: 系统将对介质错误提供完全保护。
如果数据文件损坏，介质恢复可以还原所有已提交的事务。
BULK_LOGGED: 将在介质错误保护程度与某些大规模或大容量操作
的最优性能及日志存储空间最少占用量之间进行权衡。
这些操作包括:
SELECT INTO、
大容量装载操作（bcp 和 BULK INSERT）、CREATE INDEX
文本和图象操作（WRITETEXT 和 UPDATETEXT）。
在大容量日志记录恢复模型下，对整个类只做最少的日志记录，
并且无法逐个操作地控制日志记录行为。
SIMPLE: 系统将提供占用日志空间最小的备份策略。
服务器故障恢复不再需要的日志空间可被自动重用。
重要 简单恢复模型比其它两种模型更容易管理，
但数据文件损坏时造成数据丢失的可能性更大。
最近的数据库备份或差异数据库备份之后的更改都将丢失，必须手工重新输入。
默认恢复模型由 model 数据库的恢复模型确定。
若要更改新数据库的默认值，请用 ALTER DATABASE 设置 model 数据库的恢复选项。
--*/
/*--
三种恢复模型对这些操作记录日志情况比较
使用了一个未公开的函数: fn_dblog
可以看日志文件的记录数
用法:select count(*) from ::fn_dblog(null,null)
--*/
--1.简单恢复模型
--创建测试数据库
use master
go
create database test
go
--打开创建的测试数据库
use test
go
--设置恢复模型为:简单模型
alter database test set recovery simple
--插入数据测试
select top 10000 a.* into testtable
from master..sysobjects a , master..syscolumns b
--查看日志文件中的记录数
select count(*) from ::fn_dblog(null,null)
--事务日志空间使用情况
dbcc sqlperf(logspace)
go
--删除测试的数据库
use master
drop database test
go
--2.大容量日志记录恢复模型
/*--说明
这些大容量复制操作的数据丢失程度要比完全恢复模型严重，
不允许恢复部分的BULK操作，只能全部恢复
--*/
--创建测试数据库
use master
go
create database test
go
--打开创建的测试数据库
use test
go
--设置恢复模型为:大容量日志记录
alter database test set recovery BULK_LOGGED
--插入数据测试
select top 10000 a.* into testtable from master..sysobjects a , master..syscolumns b
--查看日志文件中的记录数
select count(*) from ::fn_dblog(null,null)
--事务日志空间使用情况
dbcc sqlperf(logspace)
go
--删除测试的数据库
use master
drop database test
go
--3.完全恢复模型
/*--说明
记录的日志数要比大容量日志记录恢复模型多，
将记录每一条BCP or BULK insert,create index 的每一条index row,
对image,text字段 WRITETEXT or UPDATETEXT的所有操作
--*/
--创建测试数据库
use master
go
create database test
go
--打开创建的测试数据库
use test
go
--设置恢复模型为:完全恢复模型
alter database test set recovery full
--插入数据测试
select top 10000 a.* into testtable from master..sysobjects a , master..syscolumns b
--查看日志文件中的记录数
select count(*) from ::fn_dblog(null,null)
--事务日志空间使用情况
dbcc sqlperf(logspace)
go
--删除测试的数据库
use master
drop database test
go
/*--我的电脑上测试结果
恢复模型 速度 日志记录数 日志的可用空间 日志信息占用的百分比(%)
--------------- ------- ----------- --------------- ------------------------
简单模型 <=1秒 385 0.484375 46.673386
大容量日志记录 <=1秒 408 0.484375 47.177418
完全恢复模型 9秒 560 1.9921875 85.416664
--*/
/*--结论
恢复模型 速度 日志大小 安全性
--------------- ------- ----------- ---------
简单模型 快 很 低
大容量日志记录 快 小 较低
完全恢复模型 慢 大 高
--*/