我可以从Sql Server表中恢复已删除的数据 [英] can i recover deleted data from Sql Server tables

问题描述

我是否可以从sql server 2008恢复已删除的数据。

表在服务器上不在本地。

Can I recover deleted data from sql server 2008.
table is on server not in local.

推荐答案

服务器保留日志每个删除的记录。您可以通过'fn_dblog'sql server函数使用表名查询这些日志，它将从日志中为您提供所有已删除的记录。

Server keeps log for each deleted records.You can query these logs via 'fn_dblog' sql server function with the table name and it will give you all the deleted records from the log.

Select [RowLog Contents 0] FROM   sys.fn_dblog(NULL, NULL) WHERE  AllocUnitName = 'dbo.TableName'        AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )        AND Operation in ( 'LOP_DELETE_ROWS' )

但是这个日志是十六进制格式。并且您需要将此十六进制格式转换为您的实际数据。

下面给出的文章将帮助您以上面定义的相同方式恢复已删除的记录。



如何从中恢复已删除的数据SQL Server [ ^ ]

内容来自链接...

http://thehobt.blogspot.com/2008/12/recovering-deleted-data-in-sql-server.html [ ^ ]



使用STOPAT条款备份和恢复事务日志



如果您的数据库设置为使用完全恢复Mo del，您可能有备份和还原选项，即使您在数据修改之前没有进行备份。为此：



1.使用正常的BACKUP LOG语法备份事务日志的尾部，并指定WITH NORECOVERY。有关详细信息，请参阅MSDN上的Tail-Log Backups。

2.恢复最近的完整备份，确保指定NORECOVERY。

3.恢复最新的差异备份，同时指定NORECOVERY。

4.恢复自上次完全备份或差异备份以来的每个事务日志备份。对于最终日志备份，请使用STOPAT子句在数据修改之前立即停止还原。在访问数据库之前，您需要执行恢复，因此您可以将其作为此还原的一部分执行，也可以作为单独的操作执行。有关更多信息，请查看在MSDN上恢复到特定时间点。



当然，这将消除在STOPAT条款中指定的时间/ LSN /标记之后发生的任何活动。





使用日志资源管理器或类似的恢复工具生成撤消脚本



许多供应商提供的工具允许您浏览事务日志并生成撤消脚本以回滚数据修改。我有机会自己使用这些实用程序，但最常推荐的实用程序是RedGate的SQL Log Rescue和ApexSQL的ApexSQL Log。这些不是插头 - 就像我说的那样，我没有使用它们中的任何一个，所以请不要抱怨，如果你这样做并且它们不适合你。

the content is from the link...
http://thehobt.blogspot.com/2008/12/recovering-deleted-data-in-sql-server.html[^]

Backup and restore the transaction log using the STOPAT clause

If your database is set to use the Full Recovery Model, you may have a backup and restore option, even if you didn’t take a backup prior to the data modification. In order to do this:

1.Backup the tail of the transaction log using normal BACKUP LOG syntax, and specifying WITH NORECOVERY. For further detail see Tail-Log Backups on MSDN.
2.Restore your most recent full backup, making sure to specify NORECOVERY.
3.Restore the most recent differential backup, also specifying NORECOVERY.
4.Restore each transaction log backup taken since the last full or differential backup. For the final log backup, use the STOPAT clause to stop the restore immediately prior to the data modification. Before you access the database, you need to perform recovery, so you can either do it as part of this restore, or as a separate operation. For more information, have a look at Recovering to a Specific Point in Time on MSDN.

This will, of course, wipe out any activity which took place after the time/LSN/mark specified in the STOPAT clause.


Use a log explorer, or similar recovery tool to generate undo scripts

A number of vendors offer tools which allow you to explore the transaction log and generate undo scripts to roll back data modifications. I have had occasion to use any of these utilities myself, however the most commonly recommended utilities are RedGate’s SQL Log Rescue and ApexSQL’s ApexSQL Log. These aren’t plugs – as I said, I haven’t used either of them, so please don’t complain if you do and they don’t work out for you.

如果您没有任何数据库备份，AFAIK无法恢复已删除的数据。

If you don't have any backup of your database, AFAIK you cannot recover the deleted data.

这篇关于我可以从Sql Server表中恢复已删除的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！