6

I lost all data entered and edited in my system yesterday, and my ISP has announced to me that last night's backup is corrupt and cannot be recovered.

Is there any way to access the database's log file to try and recover the database to the point immediately before the day's data was deleted? I'm using SQL Server 2012 Standard Edition.

I've tried this (which I found in another question on this site):

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

but I'm only getting queries since this morning.

I'd be immensely grateful for any help.

Improve this question
8
  • 1
    Log files only contain the physical changes to the database, not the queries that affected those changes. Do you have readable log backups available?
    – Jon Seigel
    Commented Jun 22, 2012 at 18:11
  • My most recent non-corrupt backup is missing yesterday's data - that's the problem I'm trying to find a solution for, if there's one to be found.
    – Remi Despres-Smyth
    Commented Jun 22, 2012 at 18:12
  • In particular, I have a single table which is append only that, if I could recover, I would be heads and shoulders above where I am currently.
    – Remi Despres-Smyth
    Commented Jun 22, 2012 at 18:13
  • 1
    What recovery model is your database in?
    – Jon Seigel
    Commented Jun 22, 2012 at 18:13
  • Recovery model is "Simple".
    – Remi Despres-Smyth
    Commented Jun 22, 2012 at 18:19

3 Answers 3

Reset to default
7

Your only real chance is using a 3rd-party log analyzer tool, and even that may not work. SIMPLE recovery truncates the log when a CHECKPOINT process occurs, which is highly likely to have happened at this point.

Also, if losing the entire set of changes between backups is unacceptable, either back up more frequently in SIMPLE (if your database is small), or switch to FULL and take log backups as well to get point-in-time recovery ability. Corrupt backups notwithstanding.

Improve this answer
2
  • 3
    @Remi: You're welcome. Sorry for your data loss. The best bet at this point is to set up a backup strategy that better meets your needs. And also, make sure to test the backups you do take.
    – Jon Seigel
    Commented Jun 22, 2012 at 19:32
  • 2
    Right, you should have a restore strategy, not a backup strategy. If you create backups and stuff them on some disk somewhere, but don't validate that you can actually restore from them, don't bother taking backups in the first place. They're not guaranteed unless you've tested them.
    – Aaron Bertrand
    Commented Jun 24, 2012 at 17:00
1

There's a free tool that reads transaction logs, but it's only for SQL Server 2000: http://www.red-gate.com/products/dba/sql-log-rescue/ I think Apex has something, but it's not free

Improve this answer
0

Odds are your database is in simple recovery mode as most hosting companies keep the databases in simple recovery mode to make the backup and restore process easier to automate. This means that there is no transaction log that you can use to recover your data from. Pretty much your only option here will be to rebuild the missing data, and hopefully find the bug that caused the data to be deleted.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.