I got bored (really bored) one weekend I decided to challenge myself. I had corruption within a specific table (localised within a page) with no backups handy only an old image of the data files. The challenge being salvage data without attaching the data files.
TDE (Transparent Data Encryption) encrypts the data files at rest but don’t forget that it also encrypts your backup file too. I fancied looking inside the MDF and a backup of my database before enabling TDE and comparing it to when enabled.
If you like learning about internals use the undocumented function to get the FileID:PageID of your table rows and use that in DBCC PAGE.
SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS RID, * FROM Person.EmailAddress;
Output:
RID
(1:3392:0)
(1:3392:1)
(1:3392:2)
Now set your trace flag on and read the contents of DBCC PAGE to satisfy your curiosity.
Enjoy.
