MasterofProject

Sogou SQLServer more than 2008 of the misuse of the database recovery method - log back

label Server SQLError operationrecoveryLogLog backup
41766 people read comment(67) Collection report
Classification:
Original source:Http://prog3.com/sbdm/blog/dba_huangzj/article/details/8491327

Question:

Often see someone accidentally deleted data, or misuse, especially the update and delete no where and shout dad shouted Niang. To err is human, wrong can understand, but can not indulge, this later, now the first to solve the problem.

Encountered such a situation, the general is not to do backup, or will not come to ask questions. First tochill, otherwise there will be a greater disaster. Until you give up.


Solution method:

For this type of problem, mainly to retrieve the data before the error, before 2008, there is a very famous tool Exploer Log, I heard quite good use, this online a lot of tutorials, here is not to say. But the only regret is that the 2008 and higher version is not supported by this time, in addition to other third party tools, then the most commonly used method is the method of reference to the tail. The experimental environment 2008R2, for 2008 and above version can use this method, in 2005 can, 2000 rarely used, not tried, just before 2008 can use log exploer, so there is no need to use this method.

Following with the aid of diagrams to explain the operation method, as for the principle, does not belong to the scope of this article, and I believe I meet the misoperation when, it is estimated that no one will see the principle the.

Step:

(1), check the database recovery model, as shown in fig.:





Or use a script to check:

Recovery_model recovery_model_desc, SELECT
Sys.databases FROM
Name ='AdventureWorks'WHERE

The results are as follows:



To ensure that the database recovery model at least can not be simple. As to how to modify a complete model, I think these should not be necessary to say.

 

       Remember, for any important environment, not only is a formal customer environment (commonly known as the production environment), strongly recommend the use of [the full recovery model, while for the other two (large capacity log (bulk logged), simple (simple), the full recovery model production of log will, but when problems arise, will feel these are not what. And I can not think of any reason for the formal environment does not use the full recovery model. As long as the management is properly, the integrity of the recovery model log will not be too abnormal.

 

(2) here is another step in fact, once doneA full backup at least once. Because the backup of all types are based on a full backup, if not at least a full backup and other types of backup is redundant, so here emphasize, after creating a new database, strongly suggest that even forced to do a full backup.

Database_name recovery_model, SELECT, name
Msdb.dbo.backupset FROM

Using the above statement can be seen as a rough to see those databases have been back, as a result of testing, so do a few backups, you can see that I have made this point in time.



(3) to ensure that others are no longer connected to the database, and then do a log back:

First create a little data:

*
Because tempdb is always a simple recovery model, so it is not suitable for case.
Using Microsoft's sample database AdventureWorks
* /
使用AdventureWorks
去
如果object_id('testrestore”)是无效的
testrestore表
去
创建表testrestore
(
ID int身份(1、1),
名称varchar(50)
);
——插入测试数据:
插入testrestore(名称)
选择“Test1”
联合所有
选择“试验”
联合所有
选择“试验”
联合所有
选择“法规”
联合所有
选择“测试”
联合所有
选择“考试”
联合所有
选择“考试”
联合所有
选择“试验”
SELECT * FROM testrestore
检查一下结果:



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

使用AdventureWorks
去
等待时间21:45”
删除从dbo.testrestore

现在来看看数据:

使用AdventureWorks
去
SELECT * FROM dbo.testrestore



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

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



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




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


使用主
去
[中]磁盘备份日志= n'e:\ AdventureWorks .bak”no_truncate,noformat,在名称= n'adventureworks -事务日志备份,跳过,norewind,nounload,恢复,压缩,数据= 10,校验和
去
backupsetid声明为int
选择“backupsetid =位置从msdb备份集的地方.. database_name = n'adventureworks和backup_set_id =(选择最大(backup_set_id)从msdb ..备份集在database_name = n'adventureworks”)
如果“backupsetid是零开始RAISERROR(N’验证失败。找不到数据库“AdventureWorks”的备份信息。,16,1)结束
verifyonly从磁盘= n'e还原:\ AdventureWorks .bak”文件= @ backupsetid,nounload,norewind
去

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



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


SELECT * FROM sys.sysprocesses哪里DBID = db_id('adventureworks”)

执行结果:



然后杀掉。

接着继续备份。

 

然后进行还原,如图:

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




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




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




然后:





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




现在再检查一下:




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

 

总结:

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

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

这种方法有几个缺点:

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

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

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


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

六十四
Guess you're looking for
View comments
* the above user comments only represent their personal views, does not represent the views or position of the CSDN website
    personal data
    • visit921642 times
    • Integral:Eleven thousand five hundred and twenty
    • Grade
    • Rank:661st name
    • original100
    • Reproduced:37
    • Translation:95
    • Comments:429
    My micro blog
    Blog column
    Latest comments
    Quantum statistics