Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 Jun 22 '12 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 Jun 22 '12 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 Jun 22 '12 at 18:13
1  
What recovery model is your database in? –  Jon Seigel Jun 22 '12 at 18:13
    
Recovery model is "Simple". –  Remi Despres-Smyth Jun 22 '12 at 18:19

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
    
Thanks - I appreciate your taking the time. –  Remi Despres-Smyth Jun 22 '12 at 19:20
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 Jun 22 '12 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 Jun 24 '12 at 17:00

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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