0

I have primary keys for a table of my database for the deleted records, I want to check who deleted those records. My application uses asp.net membership for user authentication. no one logs on SQL server except me.

I'm not a DBA so this is confusing for me, found this but it's confusing for me. also found some tools like red-gate and apex-sql but red-gate only works with SQL 2000.

ekad
  • 14,436
  • 26
  • 44
  • 46
Ali Umair
  • 1,386
  • 1
  • 21
  • 42
  • 1
    You want to recover (undelete) SQL rows... that's not too easy - not impossible, but its non-trivial. If you have control over your application then why do you simply not restrict the delete operation based on user role and if it proceeds, log that details in a audit table. Or (and this is what I do), don't actually delete records. Instead have a Status flag (or enum) that indicates whether the record is active or not. There are plenty of ways to skin this cat. If you have no control over the app, then a stored proc on the delete operation could be the way to go. – robnick Feb 10 '14 at 08:03
  • Are these Rows physically deleted from the Tables? if yes then it'll be magic to recover them unless you have a database backup, IMO add IsDeleted column to table and DeletedBy column to store who deleted the row, and to restore it just change IsDeleted field. – Emad Mokhtar Feb 10 '14 at 08:39
  • @robnick : Yes i have complete control over application, i've also restricted users and roles but when we did an audit it was discovered that some records have been deleted. i've found a way in which users can delete record but it's very time consuming to delete a master child record completely. Now im planning to put a log file of every operation performed in application in xml format. but now i need to see who deleted them. i've datecreated ,timecreated , last updated and whom last updated. but no info regarding deletion – Ali Umair Feb 10 '14 at 08:57
  • @EmadMokhtar Rows are physically deleted. I've other columns like i mentioned above but don't have any information on deletion. your suggestion is good, I'll do it but what can i do in my current scenario ? – Ali Umair Feb 10 '14 at 09:03
  • @AliUmair please read this MSDN thread http://social.msdn.microsoft.com/Forums/sqlserver/en-US/86befdbe-9806-4d96-9e9f-ead753d0fc20/recover-deleted-rows-from-sql-server-database?forum=transactsql – Emad Mokhtar Feb 10 '14 at 16:24

1 Answers1

0

You can try ApexSQL Log tool to try to recover deleted rows but you can't actually see who deleted them. All third party tools can capture only SQL Server logins

You should also check necessary prerequisites for successful recovery:

  1. SQL Server 2005, 2008, 2008 R2 or 2012 database
  2. The database is in FULL recovery model
  3. The database Auto-shrink is OFF
  4. The transaction log is not truncated
  5. The backup files have not been compressed and uncompressed using any 3rd party tools
  6. Full database backups and transaction log backups are SQL Server native or native compressed are SQL Server native or native compressed
  7. The whole chain of transaction log backups since a full database backup exists. Or a full database backup, then differential database backups and then the transaction log backup chain from there up to the point in time you want to recover
  8. If the database is restored using an original database backup, you must provide transaction log backups from the original server or restore the database using the original server mdf and ldf files
Marko Krstic
  • 629
  • 6
  • 5