通过⽇志恢复SQLServer的历史数据
园⼦⾥前段时间发过⼀篇，我总结⼀下
通过⽇志还原，最重要的是：
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，
在下⾯选择时间点，这个时间是上⾯我们记住的删除表之前的时间，点击确定后，还原成功。可以看到被删除的表⼜回来了。
完整的脚本：
--创建测试数据库
CREATE DATABASE  Db
BACKUP DATABASE  Db  TO DISK='c:\db.bak'WITH  FORMAT
GO
--创建测试表
CREATE TABLE  Db.dbo.TB_test(ID  int )
--延时1秒钟，再进⾏后⾯的操作(这是由于SQL Server的时间精度最⼤为百分之三秒，不延时的话，可能会导致还原到时间点的操作失败)
WAITFOR  DELAY  '00:00:01'
GO
--假设我们现在误操作删除了 Db.dbo.TB_test 这个表
DROP TABLE  Db.dbo.TB_test
--保存删除表的时间
SELECT  dt =GETDATE ()  INTO  #
GO
--在删除操作后，发现不应该删除表 Db.dbo.TB_test
--下⾯演⽰了如何恢复这个误删除的表 Db.dbo.TB_test
--⾸先，备份事务⽇志(使⽤事务⽇志才能还原到指定的时间点)
BACKUP LOG  Db  TO DISK='c:\db_log.bak'WITH  FORMAT
GO
--接下来，我们要先还原完全备份(还原⽇志必须在还原完全备份的基础上进⾏)
RESTORE DATABASE  Db  FROM DISK='c:\db.bak'WITH REPLACE ,NORECOVERY
GO
sqlserver备份表语句--将事务⽇志还原到删除操作前（这⾥的时间对应上⾯的删除时间，并⽐删除时间略早
DECLARE@dt datetime
SELECT@dt=DATEADD (ms, -20 ,dt)  FROM  #  --获取⽐表被删除的时间略早的时间
RESTORE LOG  Db  FROM DISK='c:\db_log.bak'WITH  RECOVERY,STOPAT =@dt
GO
--查询⼀下，看表是否恢复
SELECT*FROM  Db.dbo.TB_test
/*--结果:
ID
-----------
（所影响的⾏数为 0 ⾏）
--*/
--测试成功
GO
--最后删除我们做的测试环境
DROP DATABASE  Db
DROP TABLE  #
这段时间看了关于在SQL server 中通过⽇志和时间点来恢复数据。也看了⼀些⽹上的例⼦，看如何通过⽇志来恢复数据。前提条件：
数据库的故障恢复改为⾮简单模式，去掉⾃动关闭和⾃动收缩两个选项
如果是简单模式：类似下⾯的语句操作数据就不会记录到⽇志中：  select * into t from [表名]
这时为保证数据的完整要将数据库的恢复模式改成“完整”
测试环境：
1，建⽴数据库和测试表
create database  zp
create table[zping] (
id  int ,
name  varchar ( 20 )
插⼊测试数据：
insert into[zping] (id,name)
values ( 1 , 'zping1' )
insert into[zping] (id,name)
values ( 2 , 'zping2' )
insert into[zping] (id,name)
values ( 3 , 'zping3' )
insert into[zping] (id,name)
values ( 4 , 'zping4' )
备份数据库，可以通过sql server的可视化操作，下⾯是⽣成的代码：
BACKUP DATABASE[zp]TO DISK=  N 'C:\k.bak'WITH  NOFORMAT, NOINIT,
NAME  =  N 'zp-完整数据库备份' , SKIP, NOREWIND, NOUNLOAD,  STATS  =10
GO
查看数据：
注意：这时我们做⼀个完整备份
这时可能⼀不⼩⼼删除了⼀些数据。我们模拟⼀下：（假定删除了1，2的数据）
delete from[zping]where  (id =1or  id  =2 )
注意：要记住⼤体的删除时间
这时发现删除错误了，怎么把数据回来啊。⽅法有两个：
1，通过Log Explorer 查进⾏恢复数据（我⽹站有此⼯具）
2，另⼀种⽅法通过恢复⽇志（指定时间点恢复）来恢复数据。
说明：第⼀个⽅法可以在线操作。
第⼆个⽅法必须停⽌数据库或者再另⼀个数据库恢复（前提是必须有⼀个完全备份和⽇志备份）
这⾥我们来讨论第⼆中⽅法指定时间点恢复：
1，这时对数据库事务⽇志做备份（注意，如果没做个数据库完整备份，是不能做事务⽇志备份的）这时新建⼀个数据库zp（将以前的数据库改名），恢复数据库
这时我们看到，有两个还原的数据库备份，因为我对zp数据库备份了两次，两次的备份的数据⽂件都⼀样。这⾥我们选择最近时间的备
份
默认在数据库的设置如下：是追加到备份集⾥，所以会有两个备份，如下图：
同时，在”选项“⾥设置”不回滚“事务，
注意：通过事务⽇志还原数据库，必须选择"不回滚"事务
确定后：出现下⾯情况：
这时发现，数据库⼀直是”正在还原“，这时还原数据库事务⽇志，
1，“常规”⾥选择时间，（刚删除的时间）
2，“选项”⾥将恢复状态设置为”回滚未提交“事务
确定后，查询数据库，发现数据回来了。
总结
1，这是⼀般⼤型⽹站数据安全的⼀个办法，因为数据库⽐较⼤（可能有⼏百G）数据，做⼀次完整备份时间很长，⽽且影响数据库服务器的性能，为保证数据安全，⼤多采⽤完整备份+事务⽇志备份来保证数据安全。例如：⼀天做⼀次或者2天做⼀次完整备份，⼏个⼩时内做⼀次⽇志备份。（当然可以⽤写⼀个job来实现）
2，如SQL server 2005⾥的镜像就是采⽤的这种事务⽇志同步的⽅法保证数据的同步。
3，如果恢复的⽇志数据出现”LSN“太早和太晚说明了事务⽇志间的不连续。这时要注意备份的时间和顺序。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。