Recover your data when SQL table is Deleted by Accident without any backup

How to Recover your data when SQL Server table is deleted by accident from your database without any backup. We have the solution for you. 

Every one of us at least once had to deal with such predicament when someone accidently deletes a table without any backup to recover it.

Even though you have deleted the table, you can still access the pages that are used until they get overwritten by other SQL transactions.

When the drop command is completed SQL Server does not delete the page or pages physically from the hard disk. It checks it as a ghost page while allowing the page(s) to be overwritten only when a new transaction is entered.

Call us for Free Consultation for Remote DBA services at our SQL ConsultingFirm at: 732-536-4765

Let’s observe the SQL script below to see how can we recover the lost data after we deleted the table in SQL Server database by accident.

Here is the result we got after running the above SQL statement:

Let's run the help stored procedure and see some of the info that we get:

It says that the sys.fn_PhysLocCracker is basically cracks the output of %%physloc%% virtual column. The Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of the slot.

Let's run the following code:

We will be using an undocumented Fn_PhysLocCracker command as explained above but known DBCC PAGE commands. In addition, we used the Page ID of 320 in the DBCC PAGE command, and we got it from the above SQL statement and shown in the above image.

Please see the options of the 4th parameter for the DBCC PAGE:
0 - Gets the page header
1 - Page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)
2 - Page header plus whole page hex dump
3 - Page header plus detailed per-row interpretation

See the output result of the above MS SQL DBCC PAGE commands:

Now lets DROP the table:

Now, we can create a table and insert into the DBCC PAGE output to recover the data:

Let's insert the DBCC PAGE Data into the PageData Table:

The TextX is the database and the Page ID was 320 and based on that ID we could recover the actual data. Let's run the below SQL statement to get the data from a table which was dropped:

See the actual recovered data after the table was deleted from the database:

Obviously some data cleansing is required, but that should not be a problem to an experienced DBA.

In Conclusion: As you see it is possible to bring back the lost data after the table was deleted. It may require an investment of time however; it will save you a significant amount of time by avoiding rebuilding the table. Hourly or every 30 minute backups is highly recommended to avoid such accidents.