Sql-server – Is it possible to recover day’s worth of queries from sql server log file

recoverysql serversql-server-2012transaction-log

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.

Best Answer

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.

Related Question