SQL Server 2008如何通过日志文件，恢复数据？

Question

租用的虚拟主机所关联的数据库文件损坏了，导致无法正常使用数据库了，因为没有每天备份，现在通过前两天的备份文件，恢复了数据库文件，让数据库能正常使用了，受损的mdf和ldf，idc那边帮我备份了一份，现在想找回丢失的数据，有什么办法吗？让朋友帮忙过，说是数据库主文件（mdf）损坏，日志文件应该是正常的，应该怎样操作呢？？？急求！！！

Answer 1

apexlog 之类的软件吧，或者能找回丢失的数据

Answer 2

Nothing you can do if db is in simple recovery model.

Answer 3

It's full recovery model

Answer 4

Nothing you can do if db is in simple recovery model.

It's full recovery model.

Answer 5

Do you have log backup?

Answer 6

Do you have log backup?

No,only the database files (old and broken) and the backup file(full recovery model).

Answer 7

You may out of luck here, most of third party tools can read from active db log file and log backup files. Here's one to recovery db from corrupted .mdf file:
http://www.mssqldatabaserecovery.com/

Answer 8

Here's another one: http://www.serversdatarecovery.com/sqldatabase.html

Answer 9

Here's another one: http://www.serversdatarecovery.com/sqldatabase.html

Both of them are not useful,they could just read the data for days before the database was broken.Are there any tools could read the log file?

Answer 10

You don't have any good backup from the database was broken,including log backup.so，you have only a slim chance to get back the data you lost

you can consider this software apexlog

http://www.apexsql.com/sql_tools_log.aspx

if your data is very important,you can paid them to use the  recovery  function and try to rescue your lost data

Answer 11

You don't have any good backup from the database was broken,including log backup.so，you have only a slim chance to get back the data you lost

you can consider this software apexlog

http://www.apexsql.com/sql_tools_log.aspx

if your data is very important,you can paid them to use the  recovery  function and rescue your lost data

1、数据库是10月15号出的问题，数据库损坏的

2、数据库完整备份是10月13号凌晨的，也就是说，只有12号及之前的数据

3、15号损坏的时候，数据库日志文件是正常的（ldf），数据库文件已备份下来

4、目前使用13号凌晨的备份，恢复了数据库

5、数据库损坏时，主数据文件已经受损，此时的备份有用吗

现在想通过提取15号损坏之后的日志文件，来还原数据，是否可行呢？

Answer 12

Don't think any tool can read offline db log file, you can try attach the db on dev server and put it in emergency mode then use tool to read log file. Or open case with MS support.

Answer 13

用第三方软件是可以的批量导入导出这些天的sql执行语句然后回滚把

Answer 14

You don't have any good backup from the database was broken,including log backup.so，you have only a slim chance to get back the data you lost

you can consider this software apexlog

http://www.apexsql.com/sql_tools_log.aspx

if your data is very important,you can paid them to use the  recovery  function and rescue your lost data

1、数据库是10月15号出的问题，数据库损坏的

2、数据库完整备份是10月13号凌晨的，也就是说，只有12号及之前的数据

3、15号损坏的时候，数据库日志文件是正常的（ldf），数据库文件已备份下来

4、目前使用13号凌晨的备份，恢复了数据库

5、数据库损坏时，主数据文件已经受损，此时的备份有用吗

现在想通过提取15号损坏之后的日志文件，来还原数据，是否可行呢？

Reading the log records that have no corrupt is in principle feasible
but that can't assure that find out all the lost data  As much as possible

Answer 15

可以先把MDF文件和LDF文件备份一下，然后先试试SQL Server的DBCC CHECKDB(用REPAIR_REBUILD或者 REPAIR_ALLOW_DATA_LOSS参数），看看能不能恢复出你要的数据，具体可以参见 http://technet.microsoft.com/en-us/library/ms176064.aspx

如果不行的话，可以试试第三方工具，比如我用过DataNumen SQL Recovery，网址是 http://www.datanumen.com/sql-recovery/ ， 可以恢复受损的MDF文件数据，效果不错。

Answer 16

数据库备份还原顺序关系

把受损的日志能不能备份出来，如果能备份日志，参照以上方法

-------------其它方法

1、单独附加mdf文件让其自动生成新日志试试,能不能正常

2、实例建同名的数据库，停SQL把受损DB覆盖新DB文件，然后启动SQL用单用户修复DB(DBCC CHECKDB命令参照联机)

用

DBCC CHECKDB;检查 再用下面方法修复
DBCC CHECKDB (数据库名, REPAIR_ALLOW_DATA_LOSS);
或
DBCC CHECKTABLE('表名',REPAIR_ALLOW_DATA_LOSS)

---

Roy Wu(吳熹Blog)(微博)

Answer 17

数据库备份还原顺序关系

把受损的日志能不能备份出来，如果能备份日志，参照以上方法

-------------其它方法

1、单独附加mdf文件让其自动生成新日志试试,能不能正常

2、实例建同名的数据库，停SQL把受损DB覆盖新DB文件，然后启动SQL用单用户修复DB(DBCC CHECKDB命令参照联机)

用

DBCC CHECKDB;检查 再用下面方法修复
DBCC CHECKDB (数据库名, REPAIR_ALLOW_DATA_LOSS);
或
DBCC CHECKTABLE('表名',REPAIR_ALLOW_DATA_LOSS)

---

Roy Wu(吳熹Blog)(微博)

http://www.cnblogs.com/CareySon/p/3509030.html

http://www.cnblogs.com/linyechengwei/archive/2009/12/07/1618647.html