Tell me more ×
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.
up vote 16 down vote favorite
12

I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side.

Is there any way to get back these records?

share|improve this question
9  
When's the last time you tested your backups? – Joe Jan 31 '11 at 13:49
10  
When's the last time you tested a RESTORE of your backups? – datagod Nov 23 '11 at 16:22

3 Answers

up vote 11 down vote

Unfortunately, we are not going to be able to help you without a lot more information. But from your question, I see that you have deleted what appears to be 2 million records from your database. Most likely you can't recover this information, unless you have full logging on your database and you purchase some very specific tools.

If you can describe in more detail just what it is you think you've done, and why you feel you can't get the records back, and can describe the organization of your database, then we might can help you a little more.

Some general advice: if you think you've deleted 2 million records, you're probably a little freaked out right now. So you should take a five minute break, calm down, and revisit the problem. Also, you should tell your boss right away if it's reasonable (don't wake someone up at 2am to tell them) and that you are working on a solution. Better to admit what's happened than to frantically try to recover and potentially make matters worse and hide the details. Knowing that your boss can help in some way, helps you fix the problem. Like I said, just some general advice.

share|improve this answer
3  
good idea with the 5 minute break. Always works. Helps to focus. :-) Been there done that. – hot2use Nov 19 '12 at 15:29
up vote 53 down vote

Is your database in full recovery mode?

  • If yes, are you doing transaction log backups?

    • If yes, do you have a backup utility like Quest LiteSpeed, Red Gate SQL Backup, or Idera SQLSafe?
      • If yes, those utilities can undelete objects from backup files (including the fulls and transaction logs) - but explaining how to use those is beyond the scope of what I can do here. Contact the vendor for instructions.
      • If no, restore the full backup and transaction logs as a database with a different name. (Don't overwrite the existing database.) You'll be able to get an up-to-the-second copy of the object before the delete happened, but you'll need to use the stopat part of the restore command to specify when to stop restoring the commands. You need to stop before the delete happened.
    • If no, go grab a copy of a log reader utility like Quest LiteSpeed or Apex SQL Log. These utilities can connect to the database server, examine the log file, and help you undo transactions. I'm not sure if the demo versions will work, but the paid ones definitely will.

  • If no (not in full recovery mode), restore the last full backup as a database with a different name. (Don't overwrite the database you've already got.) From there, you'll be able to restore whatever records were online at the time of the backup, but you'll lose all of the changes since.

share|improve this answer
Wow, that was way more detailed than the information I could've given. I knew those things but not "like that". Thanks for a great answer. Now let's see if @gofor.net understands how stackexchange works and comes back pretty quick? – jcolebrand♦ Jan 31 '11 at 14:42
1  
He's one of the masters (MCM) we all drool after, and dream to be some time, but not really succeed :). – Marian Jan 31 '11 at 20:37
HAHAHA, drool after, nice... – Brent Ozar Feb 1 '11 at 17:54
Great answer, Brent. I just went through the same exercise this morning. – datagod Nov 23 '11 at 16:21
4  
Great answer, I will add that if you have auditing or change tracking it is usually easiest to get the data back from those those tables when only one table has been affected. And of course, if your answer was no to some or all of the above, after you restore the records as much as they can be, then fix your backup process and possibly who has access to production data. – HLGEM Mar 20 '12 at 18:37
up vote 12 down vote

SQL Server keeps logs for each deleted record. You can query these logs via the fn_dblog SQL Server function.

SELECT [RowLog Contents 0] 
FROM   sys.fn_dblog(NULL, NULL) 
WHERE  
       AllocUnitName = 'dbo.TableName'        
   AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )        
   AND Operation in ( 'LOP_DELETE_ROWS' )   
;

But this log is in Hex format and you need to convert this Hex format to your actual data.

The article below will help you recover the deleted records in the way defined above:

http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

share|improve this answer
3  
This is pretty cool. Under what conditions does this approach work (or not work)? – Nick Chammas Nov 23 '11 at 17:16
2  
@NickChammas - It can only recover what is in the active log so for simple recovery model a checkpoint must not have occurred. Also it currently looks like it does not work correctly in databases with snapshot isolation but that should be an easy fix. @user1059637 - What is your attitude to people adding functionality to your code? – Martin Smith Nov 24 '11 at 13:27
@MartinSmith , Now it is working perfect with snapshot isolation as well. – user1059637 Nov 29 '11 at 7:36

protected by Mark Storey-Smith Nov 20 '12 at 1:03

This question is protected to prevent "thanks!", "me too!", or spam answers by new users. To answer it, you must have earned at least 10 reputation on this site.