通过日志恢复SQL Server的历史数据

Question

通过日志恢复SQL Server的历史数据

作者：未知　时间： 2008/11/15 13:11:19　文档类型：未知　来自：未知 浏览统计：687
        SQL Server中全角和半角字符的比较问题 MSSQL2005 如何启用xp_cmdshell

园子里前段时间发过一篇通过日志恢复MSSQL数据例子 ，我总结一下

通过日志还原，最重要的是：

1.必须有一个完整的备份，且这个备份必须是在修改、删除数据之前做的。

2.在更新、删除数据之后，做日志备份，该log备份将用于还原之前的数据

下面步骤的目的：还原被删除的表

在SQL Server Management可视化操作步骤：

1.创建数据库并完整备份：
--创建测试数据库
CREATE DATABASE Db
GO

--对数据库进行备份
BACKUP DATABASE Db TO DISK='c:db.bak' WITH FORMAT
GO

2.创建一个空表
--创建测试表
CREATE TABLE Db.dbo.TB_test(ID int)

3.删除刚刚建的这个空表，假设这个表被误删除了
--假设我们现在误操作删除了 Db.dbo.TB_test 这个表
DROP TABLE Db.dbo.TB_test

到了这一步，我们想还原被删除的TB_Test表，这个时候，记住删除表之前的时间，后面会用到

这个时候，需要备份日志，可以用SQL Management界面操作备份，也可以用T-SQL备份
BACKUP LOG Db TO DISK='c:db_log.bak' WITH FORMAT
GO

4.还原数据库，可以替换原来数据库，或者还原成一个新的数据库DB1,这里新的数据库DB1

如果是界面操作：

“任务”- “还原” - “数据库”：

在“常规”选择页中的设备后面选择我们之前的完整备份：db.bak,

目标数据库：DB1

在“恢复状态”下选择第2项“不对数据库执行任何操作，不回滚提交的事务。。” ，点确定后，可以看到DB1数据库的状态变成“正在还原。。”

接下来：在DB1数据库上点右键--还原--事务日志，选择刚刚我们删除表后备份的事务日志：db_log.bak，

在下面选择时间点，这个时间是上面我们记住的删除表之前的时间，炶

　SQL Server 的每一个数据库，无论是系统数据库（master,model, sybsystemprocs, tempdb），还是用户数据库，都有自己的transaction log，每个库都有syslogs表。Log记录用户对数据库修改的操作，所以如果不用命令清除， log会一直增长直至占满空间。清除log可用dump transaction 命令；或者开放数据库选项trunc log on chkpt，数据库会每隔一段间隔自动清除log。管理好数据库log是用户操作数据库必须考虑的一面。 　　下面就几个方面谈谈log及其管理：

　　一、SQL Server 如何记录及读取日志信息
　　我们知道，SQL Server是先记log的机制。Server Cache Memory中日志页总是先写于数据页：

　　Log pages 在commit ,checkpoint,space needed 时写入硬盘。

　　Data pages 在checkpoint,space needed 时写入硬盘。

　　系统在recovery 时读每个database 的syslogs 表的信息，回退未完成的事务(transaction)（数据改变到事务前状态）；完成已提交的事务(transaction)（数据改变为事务提交后的状态）。在Log中记下checkpoint点。这样保证整个数据库系统的一致性和完整性。

　　二、Transaction logs 和checkpoint 进程
　　checkpoint 命令的功能是强制所有“脏”页（自上次写入数据库设备后被更新过的页）写入数据库设备。自动的checkpoint 间隔是由SQL Server 根据系?统活动和系统表sysconfigures中的恢复间隔（recovery interval）值计算出的。通过指定系统恢复所需的时间总量，恢复间隔决定了checkpoint 的频率。

　　如果数据库开放 trunc log on chkpt选项，则SQL Server在数据库系统执行checkpoint时自动清除log。但用户自己写入执行的checkpoint命令并不清除log，即使trunc log on chkpt选项开放。只有在trunc log on chkpt选项开放时，SQL Server自动执行checkpoint动作，才能自动清除log 。这个自动的checkpoint动作在SQL Server中的进程叫做checkpoint进程。当trunc log on chkpt选项开放时，checkpoint进程每隔0秒左右清除log，而不考虑recovery interval设置时间的间隔。

　　三、Transaction log 的大小
　　没有一个十分严格的和确切的方法来确定一个数据库的log应该给多大空间。对一个新建的数据库来说，log大小为整个数据库大小的20%左右。因为 log记录对数据库的修改，如果修改的动作频繁，则log的增长十分迅速。所以说log空间大小依赖于用户是如何使用数据库的。
　　例如：
　　1.update,insert和delete 的频率
　　2.每个transaction 中数据的修改量
　　3.SQL Server系统参数recovery interval 值
　　4.log是否存到介质上用于数据库恢复
　　还有其它因素影响log大小，我们应该根据操作估计log大小，并间隔一个周期就对log进行备份和清除。

　　四、检测log 的大小
　　若log 在自己的设备上，dbcc checktable (syslogs) 有如下信息：

　　例:***NOTICE:space used on the log segment is 12.87Mbytes,64.35%
　　***NOTICE:space free on the log segment is 7.13Mbytes,35.65%

　　根据log剩余空间比例来决定是否使用dump transaction 命令来备份和清除log。

　　用快速方法来判断transaction log 满的程度。

　　1>use database_name
　　2>go
　　1>select data_pgs (8,doa mpg)
　　2>from sysindexes where id=8
　　3>go
　　Note:this query may be off by as many as 16 pages.

　　在syslogs 表用sp_spaceused 命令。

　　五、log 设备
　　一般来说，应该将一个数据库的data和log存放在不同的数据库设备上。这样做的好处：
　　1.可以单独地备份(back up)transaction log
　　2.防止数据库溢满
　　3.可以看到log空间的使用情况。[dbcc checktable (syslogs)]
　　4.可以镜像log设备

　　六、log 的清除
　　数据库的log是不断增长的，必须在它占满空间之前清除。前面已经讨论过，清除log可以开放数据库选项trunc log on chkpt，使数据库系统每隔一段时间间隔自动清除log，还可以执行命令dump transaction 来清除log.trunc log on chkpt 选项同dump transaction with truncate_only 命令一样，只是清除log而不保留log到备份设备上。所以如果只想清除log而不做备份，可以使用trunc log on chkpt 选项及dump transaction with truncate_only,dump transaction with no_log 命令。若想备份，应做dump transaction database_name to dumpdevice。

　　七、管理大的transactions
　　有些操作是大批量地修改数据，log增长速度十分快，如：
　　1.大量数据修改
　　2.删除一个表的所有记录
　　3.基于子查询的数据插入
　　4.批量数据拷贝

　　下面讲述怎样使用这些transaction 使log? 不至溢满：
　　大量数据修改
　　例 ：
　　1>update large_tab set col_1=0
　　2>go

　　若这个表很大，则此update动作在未完成之前就可能使log满，引起1105错误（log full）而且执行这种大的transaction所产生的exclusive table loc，阻止其他用户在update期间修改这个表，这可能引起死锁。为避免这些情况，我们可以把这个大的transaction分成几个小的 transactions，并执行dump transaction 动作。

　　上述例子可以分成两个或多个小transactions.

　　例如：

　　1>update large_tab set col1=0
　　2>where col2 3>go
　　1>dump transaction database_name with truncate_only
　　2>go
　　1>update large_tab set col1=0
　　2>where col2>=x
　　3>go
　　1>dump transaction database_name with truncate_only
　　2>go

　　若这个transaction 需要备份到介质上，则不用with truncate_only 选项。若执 行dump transaction with truncate_only，应该先做dump database 命令。

　　删除一个表的所有记录：
　　例：
　　1>delete table large_tab
　　2>go

　　同样，把整个table的记录都删除，要记很多log，我们可以用truncate table命令代替上述语句完成相同功能。

　　1>truncate table large_tab
　　2>go

　　这样，表中记录都删除了，而使用truncate table 命令，log只记录空间回收情况，而不是记录删除表中每一行的操作。

　　基于子查询的数据插入
　　例：
　　1>insert new_tab select col1,col2 from large_tab
　　2>go

　　同样的方法，对这个大的transaction，我们应该处理为几个小的transactions。

　　1>Insert new_tab
　　2>select col1,col2 from large_tab where col1<=y
　　3>go
　　1>dump transaction database_name with truncate_only
　　2>go
　　1>insert new_tab
　　2>select col1,col2 from large_tab where col1>y
　　3>go
　　1>dump database database_name with truncate_only
　　2>go

　　同样，若想保存log到介质上，则dump transaction 后不加with truncate_only 选项。若执行dump transaction with truncate_only，应该先做dump database 动作。

　　批量数据拷贝

　　在使用bcp把数据拷入数据库时，我们可以把这个大的transaction变成几个小的transactions处理，避免log剧增。

　　开放trunc log on chkpt 选项
　　1>use master
　　2>go
　　1>sp_dboption database_name,trunc,true
　　2>go
　　1>use database_name
　　2>go
　　1>checkpoint
　　2>go

　　bcp... -b 100 (on unix)
　　bcp... /batch_size=100(on vms)

　　关闭trunc log on chkpt选项，并dump database。

　　在这个例子中，一个批执行100行?拷贝。也可以将bcp输入文件分成两或多个分开的文件，在每个文件执行后做dump transaction 来避免log 满。

　　若bcp使用快速方式（无索引，无triggers），这样操作不记log，换句话说，log 只记载空间分配情况。在这种情况下，要先做dump database（为恢复数据库用）。若log太小，可置trunc log on chkpt 选项，这样在每次checkpoint后清除log。

　　八、Threshold 和transaction log 管理
　　SQL Server提供阈值管理功能，它能帮助用户自动监视数据库log设备段的自由空间。这方面的详细讨论见NO.5技术支持杂志。log的管理是灵活而复杂的，我们应该在实践中摸索经验，针对每个数据库的不同情况，不同操作，做不同处理。

Answer 1

学习了。谢谢分享啊。

Answer 2

1.通过证书方式来实施数据库镜像时成功,相关的T-SQL语句网上到处都是...这里就不说了

2.通过Microsoft SQL Server Management Studio中数据库->任务->镜像->配置安全性也可以实施

这里有个地方需要注意,在用MSSMS来进行SQLServer数据库镜像之前,我通过了证书的方式来做过了数据库镜像了

那么我实际上已经创建了Endpoint_Mirroring,可是在使用MSSMS进行数据库镜像配置时,创建端点并没有报错...这个其实应该是会报错的,就是因为没有报错,导致我配置完成后(没有报错)进行数据库镜像时,一直报错

"无法将   ALTER   DATABASE   命令发送到远程服务器实例   'TCP://PHOENIX-ZJ.phoenixit.com:5022 '。数据库镜像配置未更改。请确保该服务器已连接，然后重试。   (Microsoft   SQL   Server，错误:   1456) "

再排查这个问题的时候,

telnet PHOENIX-ZJ.phoenixit.com:5022 是没有问题的

ping PHOENIX-ZJ.phoenixit.com也是通的..

死活不知道为啥啊...

思来想去..通过如下T-SQL语句发现了一点异常的地方:

select * from sys.database_mirroring_endpoints

这个语句查询出来有个字段"connection_auth_desc" 显示是证书模式

理论上通过MSSMS创建的Endpoint_Mirroring的话,该字段应该是"NEGOTIATE"

接下来删除这个端点:

DROP ENDPOINT Endpoint_Mirroring

然后再通过MSSMS来进行镜像数据库的配置...

Answer 3

果需要查看SQL Server 数据库中的事务日志，有什么方法呢？下面将为您介绍使用用sql语句dbcc log查看的方法，供您参考，希望对您有所启迪。

1)用系统函数

SELECT allocunitname,operation,[RowLog Contents 0] as r0,[RowLog Contents 1] as r1FROM::Fn_dblog(null,null)where allocunitname like'dbo.TArea%'and operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS')

2)用DBCC

DBCC log('hrdb', TYPE=2)

dbcc log(dbname,4)       --(n=0,1,2,3,4)

1 - 更多信息plus flags, tags, row length

2 - 非常详细的信息plus object name, index name,page id, slot id

3 - 每种操作的全部信息

4 - 每种操作的全部信息加上该事务的16进制信息

默认 type = 0