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?
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 only takes a minute to sign up.Sign up to join this community
Is your database in full recovery mode?
If yes, are you doing transaction log backups?
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.
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:
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.