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 Consulting Firm 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.
USE TestX CREATE TABLE Test ( [DescriptionX] VARCHAR(20) ) /*Insert sample data*/ INSERT INTO Test ( DescriptionX ) VALUES ('Hello World!'), ('USA Today') /*Getting data page information:*/ SELECT * FROM Test CROSS APPLY Fn_PhysLocCracker (%%PHYSLOC%%) AS Pl
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:
EXEC sp_helptext 'fn_PhysLocCracker'
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. You can find more info about the Fn_PhysLocCracker in this great article at: SQL Server 2008: New (undocumented) physical row locator function
Let's run the following code:
/* Trace flag 3604 is used to redirect the output of some DBCC commands to the result window. */ DBCC TraceOn (3604); DBCC PAGE (TestX, 1, 320, 3) WITH TABLERESULTS;
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:
DROP TABLE Test
Now, we can create a table and insert into the DBCC PAGE output to recover the data:
CREATE TABLE PageData ( PageDataPK [int] IDENTITY(1,1) NOT NULL, ParentObject VARCHAR(1000) NULL, Object VARCHAR(4000) NULL, Field VARCHAR(1000) NULL, ObjectValue VARCHAR(MAX) NULL ) GO
Let's insert the DBCC PAGE Data into the PageData Table:
INSERT INTO PageData (ParentObject, [Object], Field, ObjectValue) EXEC ('DBCC TraceOn (3604); DBCC PAGE (''TestX'',1, 320, 2) WITH TABLERESULTS') GO
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:
SELECT * FROM PageData WHERE ObjectValue LIKE '%USA Today%' OR ObjectValue LIKE '%Hello World!%'
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.
Go Back to Our: Main Blog
Written by: Doron Farber - The Farber Consulting Group, Inc