首页 > 代码库 > 【转】SQLServer 2008以上误操作数据库恢复方法——日志尾部备份

【转】SQLServer 2008以上误操作数据库恢复方法——日志尾部备份

4号,公司的生产数据表被全部删除,目前没有找到原因,由于刚接触SQL不久,所以短时间内不会还原,也不敢动被原服务器,于是就将原服务器停掉,拷贝出里面的PPD数据库文件,留作备份;近几天在自己的电脑上尝试修复,一直没有成功,细读了一下《SQL2005技术内幕——存储引擎》了解到删除列、删除表这些操作不会直接对每一行数据进行操作,而是直接改变他们的物理指向地址的ID,专业术语我也不是很清楚,我的理解是这样的,有时间再弄清楚,不过这足以让我明白被删除的表还是存在mdf文件中,其改变的便宜地址记录在日志log文件中。但是试了很多办法也找不回表,曾试过第三方软件比如Log Explorer(很多人说4.2版本可以用在SQL2005上,但是我的没法用于找回删除的表,那个【挽救】的按钮永远是灰白的)、ApexSQLRecover(功能很强大,但是试用版撤销事务后每个表只能恢复100条记录,正版价格在1W以上)、还有极佳数据恢复等软件,这些软件都是要花钱的。总之是没有找回来

今天我意外看到尾部日志的恢复方法,前提是要有一次完整备份,哪怕是十年前的也可以,我努力回忆,想起来在新系统测试阶段,我似乎有过一次完整备份,那次是担心切换新系统后数据列的格式不对,用来做参照的,于是赶紧打开服务器,果然有一次10月份的完整备份,虽然里面的某些字段不同,但是应该是没有问题的,逼近完整备份之后,没有重装过SQL2005,它的事务日志应该是完整的,于是拷贝出10月份的完整备份文件拿到自己的电脑上尝试恢复。按照下面的方法最终成功恢复了12.04号的数据,真的是太棒了。

转帖地址:http://blog.csdn.net/dba_huangzj/article/details/8491327

原文出处:http://blog.csdn.net/dba_huangzj/article/details/8491327

问题:

         经常看到有人误删数据,或者误操作,特别是update和delete的时候没有加where,然后就喊爹喊娘了。人非圣贤孰能无过,做错可以理解,但不能纵容,这个以后再说,现在先来解决问题。

        遇到这种情况,一般都是没有做备份,不然也不会来发问了。首先要冷静,否则会有更大的灾难。直到你放弃。

 

 

解决方法:

 

       对于这类问题,主要是找回误操作之前的数据,在2008之前,有个很出名的工具Log Exploer,听说还挺好用的,这个网上大把教程,这里就不多说了。但是唯一遗憾的是,不支持2008及更高版本,这时除了其他第三方工具,那么最常用的就是本文提到的方法——日志尾部备份。本文实验环境2008R2,对于2008及其以上版本可以使用这个方法,其实2005也可以,2000很少用,没试过,只是2008之前可以使用Log Exploer,所以就没必要用这种方法。

      下面图文并茂讲解操作方法,至于原理,不属于本文范围,而且我相信真遇到误操作的时候,估计没人会看原理了。

步骤:

(1)、检查数据库的恢复模式,如图:

 

 

 

或者使用脚本检查:

SELECT recovery_model,recovery_model_desc FROM sys.databasesWHERE name =AdventureWorks  

结果如下:

 

 

        确保数据库的恢复模式最起码不能为【简单】。至于如何修改成完整模式,我觉得这些应该没必要多说了。

 

       切记,对于任何重要环境,不仅仅是客户正式环境(俗称生产环境),都强烈建议使用【完整恢复模式】,虽然对于另外两种(大容量日志(BULK_LOGGED)、简单(SIMPLE))来说,完整恢复模式产生的日志会大,但是在出现问题的时候,就会觉得这些都不算什么了。并且我也想不到任何理由对于正式环境不使用完整恢复模式。只要管理得当,完整恢复模式的日志也不会太变态。

 

(2)、这里其实隐含另外一步,曾经做过最少一次的完整备份。因为所有类型的备份都基于完整备份,如果没有最少一次完整备份,其他类型的备份都是多余的,所以在这里强调一下,在创建完一个新数据库之后,强烈建议甚至强制做一次完整备份。

SELECT  database_name,recovery_model,name FROM msdb.dbo.backupset

使用上面的语句粗略可以看到有那些数据库做过备份,由于测试,所以做了几次备份,可以看到我这个时间点已经做了备份了。

 

 

(3)、确保别人不再连接数据库,然后做一次日志尾部备份:

首先先创建一点数据:

/*由于tempdb永远为简单恢复模式,所以不适合做案例。这里使用微软的示例数据库AdventureWorks*/USE AdventureWorksGOIF OBJECT_ID(testRestore) IS NOT NULL     DROP TABLE testRestoreGOCREATE TABLE testRestore    (      id INT IDENTITY(1, 1) ,      NAME VARCHAR(50)    );--插入测试数据:   INSERT INTO testRestore(Name)SELECT test1UNION ALL SELECT test2UNION ALL SELECT test3UNION ALL SELECT test4UNION ALL SELECT test5UNION ALL SELECT test6UNION ALL SELECT test7UNION ALL SELECT test8SELECT * FROM testRestore

检查一下结果:

 

然后来做个删除操作,为了定位是啥时候发生的,我加了一个waitfor命令,让它在某个时间发生,这样恢复的时候就有准确性:

USE AdventureWorksGOWAITFOR TIME 21:45DELETE FROM dbo.testRestore

现在来看看数据:

USE AdventureWorksGOSELECT * FROM dbo.testRestore



 

 

 

 

到这一步,灾难出现了。但是切记要冷静。

 

下面就是本文的重点开始,做一次日志备份,最重要是选择【备份日志尾部】

 


 

 

然后在【选项】页选择:除【事务日志】除,其他红框包裹的地方为强烈建议勾选的地方。并且保证数据库不要有别人在连接,因为备份日志尾部会使数据库处于还原状态,拒绝其他会话的连接,如果不断开其他连接,是备份不了的。



 

 

然后按确定,当然,可以使用上方的【脚本】来生成语句:

USE MasterGOBACKUP LOG [AdventureWorks] TO  DISK = NE:\AdventureWorks.bak WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = NAdventureWorks-事务日志 备份, SKIP, NOREWIND, NOUNLOAD,  NORECOVERY , COMPRESSION,  STATS = 10, CHECKSUMGOdeclare @backupSetId as intselect @backupSetId = position from msdb..backupset where database_name=NAdventureWorks and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=NAdventureWorks )if @backupSetId is null begin raiserror(N验证失败。找不到数据库“AdventureWorks”的备份信息。, 16, 1) endRESTORE VERIFYONLY FROM  DISK = NE:\AdventureWorks.bak WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWINDGO

此时,数据库会处于【正在还原】的状态

 

 

 

如果发现备份不了可以用下面语句查看,并把spid杀掉:

SELECT  * FROM sys.sysprocesses WHERE dbid=DB_ID(AdventureWorks)

执行结果:

 

 

然后kill掉。

接着继续备份。

 

然后进行还原,如图:

先要还原完整备份,选择最近的那次,由于日志备份的特性(以后其他文章再说),只认最后一次备份,所以要选择最新的那次,否则还原不了。

 

 

 

这里又有一个注意事项,记得选择:

 

 

 

 

接着还原日志文件,这是最最重要的一步:

 

 

 

然后:


 

 

由于实验的时候出了点问题,后面重做了,所以时间选择到22:19分,我是在22:20分删除数据的。这里不用太在意,只要把时间点指定到你误删除的时间之前即可。而由于日志尾部备份都是最后一个备份文件,所以这里选则红框部分即可:

 

 

 

现在再检查一下:

 

 

 

可以看到,数据已经还原成功。

 

总结:

平时不做备份,出问题来喊急,这是苟有自取,还有一些脑袋发热的人喜欢看到ldf很大就直接删除,那以后出问题就别怪微软了。

本文中的方法看上去有点繁琐,但是实操几次就觉得好了,但是步骤建议严格按照上面说的,因为一旦操作错误,就很麻烦,此时再次强调——冷静冷静再冷静!!!!!!

这种方法有几个缺点:

1、             如果你发现误操作以后还有很多人做了操作,那么你还原成功后,别人的操作就会冲掉,所以发生误操作后,要马上停止别人对数据库的操作。

2、             这个方法要对数据库独占,所以你想偷偷恢复是不行的了。勇敢承认错误吧。

对于核心数据表,还是要先做好预防操作,可以看:SQLServer恢复表级数据。

 

关于备份,可以看我的另外一篇文章:第一篇——第一文 SQL Server 备份基础

我的总结:

通过读取日志,可以发现表是SA用户名使用DROP TABLE的命令删除的,具体是谁干的,无法找到

引起这项事故的根源是安全性意识薄弱:

1、车间的外网没有封锁

2、车间的USB接口没有封锁

3、SQL默认的sa为空密码,其权限为最高权限,而我在切换系统后没有给sa设置密码,这样的话,只要发送以下命令,就可以获取所有用户表的列表,然后DORP表就可以把表都删除掉

selecte *from sysobjectswhere xtype="U"
GO

4、SQL的权限分配不合理,没有细分,同意用的sa用户连接

5、没有建立备份计划

预防措施:

1、切断车间外网

2、封锁USB接口

3、sa用户名设置密码,在DDL触发器中添加禁止表删除命令

4、重新分配用户权限,客户端使用不同的用户名

5、建立合理的自动备份计划

 

最后再次感谢文章原作者

 

【转】SQLServer 2008以上误操作数据库恢复方法——日志尾部备份