In a couple of my tables in my SQL Server 2005 database all of my data has been erased. Is there anyway to get a log in SQL Server of all the statements that have ran in the past day? I am trying to find out if someone did this on accident, there is a vulnerability in my web app, or the actual DB has been compromised.
+2
A:
See the following there are a couple of programs which will allow you to read the log.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
The one from Red Gate is called SQL Rescue and looks pretty good.
Chris Lively
2009-05-22 15:01:35
You can actually tear apart the Transaction log and determine what was run on the database. However these are in a proprietary, undocumented file format - be on the lookout for tools that can do this, I unfortunately don't have a recommendation.
Redbeard 0x0A
2009-05-22 15:09:52
@Redbeard: I was wrong and updated my answer. Thanks!
Chris Lively
2009-05-22 15:11:16
+2
A:
You could try a log rescue tool like Log Rescue
I would also sort out some auditing of your own.
Log Rescue doesn't support SQL 2005 so you could also try Apex SQL Log
John Hunter
2009-05-22 15:02:46
The page for Log Rescue states that it does not support SQL 2005 (which is what OP is using)
Jason Musgrove
2009-05-22 15:10:21
+1
A:
There are applications you can buy that can convert a transaction log backup into the actual statements that were run. You may be able to find a trial version of some of these, unfortunately I cannot reccommend any specific one though.
Robin Day
2009-05-22 15:04:32
+3
A:
You're looking for the transaction log. Depending on how, and if, it is setup, you'll be able to see what was run. There some info on it at http://www.databasedesign-resource.com/sql-server-transaction-log.html. Given that, I'm sure you can also Google some better resource.
Autocracy
2009-05-22 15:04:37
My understanding of the transaction log is that it does *not* store the text of the SQL statements run against the server, but the locations and content of pages that were affected in each transaction. The log is useful for rolling transactions forward or backward for point-in-time-restores, but, as far as I know, useless for forensics.
Jason Musgrove
2009-05-22 15:09:51
Terminology snafu, I think. MySQL, for example, calls the log you described the transaction log. MySQL's "binary log" is equivalent to SQL Server's "transaction log".
Autocracy
2009-05-22 15:24:23
+1
A:
Something else to keep in mind: if a hacker gained enough access to clean out some tables, there's a good chance they gained enough access to have their way with your log files as well.
Joel Coehoorn
2009-05-22 15:10:32
+3
A:
You could also try running the command DBCC LOG(database,3). It will output the data that is in the transaction log.
omockler
2009-05-22 15:11:06
+1
A:
Make a Transaction Log Backup in SQL Server, download a Trial Version of TOAD for SQL Server there you can import your Transactionlog Backup.
And if you want you can also create INSERT Scripts of the DELETED records. But I dont know if there are any restrictions in the TOAD trial version.
john84
2009-05-22 15:11:40