Are you familiar with the phrase "it's not me, but someone who work for me"?!? Well... unfortunately I hear this type of phrases from time to time, AND I HATE IT. Before I move to the technical content I must once more quote Spider-Man comic!
With great power comes great responsibility
If you don't want to get responsibility, then you should not ask to be in-charge! If your team's member did something wrong and you are the team's leader, , then you are responsible for the issue and IT IS YOU even if someone else click the button.
So,why do I start with the above reminder?!?
Several days ago I got a support call from a senior DBA, saying that "someone" in the company dropped a column in a table by mistake, before they copied several of the values,which they needed. Moreover, he explained that they do have any backup of the database and therefore they lost important data.
Before he contacted to me, he tried to get help in the forums, to contact several experts, and he found Microsoft's documentation which claim that "This action cannot be undone". The question was simply a request to find away to get the lost data...
Can it be done?!?
The short answer is, that probably we can get all the lost data from the dropped column, if this was the only action. Yes, the official documentation was simply wrong. A common user which work only with the LOGICAL layer of SQL Server cannot undone this action using the documented tools, but for SQL Server internals level of users, this is a different story.
Off-topic! by the way, I hate the use of the word Expert and I think that we should prefer the word Expertise!
As first step, I contacted Microsoft internally and I updated the official document through the GitHub project, which is why you can see my name in the Contributors list
Let's examine the solution using a simple demo
Our Case Study
For the simplicity of the demo, let's create new database, and next I'll use a simple table with two columns and a single row of data.
Create table and insert one row:
IF EXISTS T;
Now, we can DROP the FName column
Using the "logical layer" that SQL Server provides for the users we can examine the columns in the table in different ways andcoinfirm that the column does not exists anymore. For example, we can use simple "SELECT *" query in order to get all the columns, we can query sys.all_columns, we can use SQL Server Management Studio or Azure Database Studio and explore the columns in the explorer windows, and so on...
object_id = OBJECT_ID(
You can notice that no mater which option you use, assuming you are using only the documented logical layer, the dropped column does not exists as expected.
But now it is time to move to level up and examine the internals of what SQL Server actually stores behind the scene on the disk.
For the sake of this task, we will need to use 2 undocumented elements as show bellow.
Solution - reading the deleted data
We can use DBCC IND command to find the pages in the disk which include the data of the table.
-- Format: DBCC IND('', '.
The result should looks like bellow image::
You can notice that the table uses two pages at this time. The page that we need is the DATA page which is page type 1. In my case it is pagePID number 328.
Now, we can examine the binary data that is actually stored on the disk for this page, using the undocumented command DBCC PAGE, but before we use this command we will need to set Trace number 3604 to ON in order to get the content of the result and not only the final message, which inform us that the commend finished.
* Note that we use the PAGEPID which we found in previous step.
DBCC TRACEON(3604, -1);
, 1, 328, 1);
Scroll down in the result until you get to the line of "Slot 0". The information after the slot number might be different for you.
For example, in my case I get:
"Slot 0, Offset 0x60, Length 32, DumpStyle BYTE"
Slot 0 is the first row in the table, Offset 0x60 is the location where the data of the row start inside the page, Length 32is the length of the data of the row.
Under this line you should get the binary data which is actually stored on the disk
Slot 0, Offset 0x60, Length 32, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 32
Memory Dump @0x0000000F56DF8060
0000000000000000: 30000c00 02000000 00000000 03000002 001a0020 0..................
0000000000000014: 00526f6e 656e4172 69656c79 .RonenAriely
You can notice that on the disk we see both columns. The values "Ronen" is in the column which we dropped.
Dropping a column does not (directly) delete the data from the disk. This action only changes the metadata of the table. Therefore, we can read the data and restore it if needed.
Resources and More information