Undropping a SQL Server Table - Part 4

Introduction

If you’ve been following this series of posts you know that I’m running through the steps I used to recover approximately 10GB of data from tables which had been accidentally dropped, and for which no backup existed.

So far, we’ve created a stored procedure with which to extract a hexadecimal dump of a specified page (Part 1), identified which pages contained data from which tables (Part 2), and parsed each page into individual records (Part 3).

This part will parse those records (which are still in raw hexadecimal form) into individual columns (i.e. into CHARs, DECIMALs, INTs, etc).

Record Structure

Before we actually parse the record, let’s a quickly re-cap of its structure. In Part 2 of this series, I described the basic layout of a record as follows:

Offset	Size	Mnemonic
0	1	TagA
1	1	TagB
2	2	Fsize
4	Fsize – 4	Fdata
Fsize	2	Ncol
Fsize + 2	((NCol - 1) / 8) + 1	Nullbits
Fsize + 2 + size of Nullbits	2	VarCount
Fsize + 2 + size of Nullbits + 2	VarCount * 2	VarOffset
Fsize + 2 + size of Nullbits + 2 + (VarCount * 2)	(varies)	VarData

All the data pertaining to fixed-length columns is stored in Fdata, and all the data pertaining to variable-length columns is stored in VarData.

Fdata Structure

The data for all fixed width columns is stored in one contiguous block called Fdata. The number of bytes allocated to each column is defined by its data type (and size in the case of CHAR and NCHAR columns, and precision in the case of DECIMAL columns). The number of bytes occupied does not vary from row to row. This applies even if a column contains NULL: a CHAR(10) which is NULL still occupies 10 bytes within Fdata.

The following table summarises the format in which some common fixed-length columns are stored.

Data Type	Storage
INT	Four-byte signed integer
SMALLINT	Two-byte signed integer
TINYINT	One-byte signed integer
CHAR(n)	n bytes, encoded according to the relevant code page
MONEY	Eight-byte signed integer; final four digits are to the right of an implied decimal point
DATETIME	Four-byte signed integer specifying the number of days before or after 1st January 1900 followed by a four-byte unsigned integer containing the number of 300th of a second intervals since midnight
FLOAT	Eight-byte value as specified by IEEE 754
DECIMAL(p,s)	A single sign byte (0x00 or 0x01) followed by a four-, eight-, twelve- or sixteen-byte signed integer with the final s digits to the right of an implied decimal point

Note that multi-byte numeric values are always stored with their bytes reversed.

Fixed-Width Examples

It always helps to see an example or two, so here are some examples of these data types in action:

• The INT value 1,234,567,890 is converted into a four-byte hexadecimal number, 499602D2, then those bytes reversed to D2029649.
• The SMALLINT value 12345 is converted into a two-byte hexadecimal number, 3039, then the bytes are reversed to 3930.
• The TINYINT value 123 is converted into a one-byte hexadecimal number, 7B.
• The CHAR(n) value "ABC" has each character converted into a hexadecimal number according to the relevant code page, giving 656667 for Latin1_General. No byte reversal takes place for non-numeric data types.
• The MONEY value 1234567890.1234 is scaled by 10,000 (to create an integer value) and is then converted into an eight-byte hexadecimal number, 00000B3A73CE2FF2, before being byte-reversed to give F22FCE733A0B0000.
• The DATETIME value 01:23:45.67 on 25th December 2009 has it’s time component converted to 1,507,701 (being the number of 300th of a second intervals since midnight: 1*60*60*300 + 23*60*300 + 45*300 + 201) which is then converted into the four-byte hexadecimal value 00170175 and stored with its bytes reversed as 75011700. The date component is converted to 40,170 (being the number of days since 1st January 1900) which is then converted into the four-byte hexadecimal value 00009CEA and stored with its bytes reversed as EA9C0000. The date and time components are stored adjacent to each other giving a final stored value of 75011700EA9C0000.
• The FLOAT value 1234567890.1234 is stored according to the IEEE 754 specification for double-precision floating point numbers. The number is converted into a Sign bit (positive = 0), an 11-bit Exponent (1053 = 10000011101) and a 52-bit binary Fraction (0.14978094596732 = 0010011001011000000010110100100001111110010111001001) where the formula -1^Sign * (1+Fraction) * 2^(Exponent-1023) gives the required value. This 64-bit binary number is converted into the hexadecimal number 41D26580B487E5C9 and stored with its eight bytes reversed as C9E587B48065D241.
• The DECIMAL(p,s) value 123,456.78 is stored slightly differently depending upon the precision, p. All DECIMALs use a full byte to store the sign, so it’ll store the hexadecimal byte 01 to indicate positive. The value 123,456.78 will be scaled to remove the decimal point and give the integer 12,345,678, or BC614E in hexadecimal. The number of bytes used to store that integer value will be 4 bytes if p is less than 10, else 8 bytes if p is less than 20 bytes, else 12 bytes if p is less than 29, else 16 bytes. This makes sense in that, for example, the maximum unsigned integer which can be stored in 4 bytes is 4,294,967,295 (2^31 - 1) so we can only use it to store a 9-digit number. Assuming p is less than 10, then this unsigned integer will be stored as the 4-byte hexadecimal number 00BC614E. When reversed, and with a leading 01 for the sign, this will be stored as 014E61BC00.

So, if we had an Fdata structure containing the INT value 1,234,567,890, the CHAR(3) value "ABC" and the DECIMAL(9,2) value 123,456.78 we’d expect to see the following bytes stored:

D2029649

656667

014E61BC00

VarData

Variable-length records are stored as a long string of concatenated bytes. For example, assuming Latin1_General, the byte sequence 656565666666676767 could represent one VARCHAR column containing the text "AAABBBCCC" or three VARCHAR columns containing the text "AAA", "BBB" and "CCC" respectively. The VarOffset array within the record must be used to establish where each column ends (or, more accurately, where each column starts). We’ll talk more about the VarData structure when we actually parse it later in this post.

Parsing Raw Records

As we happen to know the structure of the TestTable I dropped back in Part 2, we could extract the raw data for fixed-width columns from the records in LazarusRecord with code like this:

SELECT SUBSTRING(Data,  8, 1) + SUBSTRING(Data,  7, 1) + SUBSTRING(Data, 6, 1) + SUBSTRING(Data, 5, 1) AS raw_MyInt,
       SUBSTRING(Data, 10, 1) + SUBSTRING(Data,  9, 1) AS raw_MySmallInt,
       SUBSTRING(Data, 11, 1) AS raw_MyTinyInt,
       SUBSTRING(Data, 12, 6) AS raw_MyChar,
       SUBSTRING(Data, 25, 1) + SUBSTRING(Data, 24, 1) + SUBSTRING(Data, 23, 1) + SUBSTRING(Data, 22, 1) + SUBSTRING(Data, 21, 1) + SUBSTRING(Data, 20, 1) + SUBSTRING(Data, 19, 1) + SUBSTRING(Data, 18, 1) AS raw_MyMoney,
       SUBSTRING(Data, 33, 1) + SUBSTRING(Data, 32, 1) + SUBSTRING(Data, 31, 1) + SUBSTRING(Data, 30, 1) + SUBSTRING(Data, 29, 1) + SUBSTRING(Data, 28, 1) + SUBSTRING(Data, 27, 1) + SUBSTRING(Data, 26, 1) AS raw_MyDateTime,
       SUBSTRING(Data, 41, 1) + SUBSTRING(Data, 40, 1) + SUBSTRING(Data, 39, 1) + SUBSTRING(Data, 38, 1) + SUBSTRING(Data, 37, 1) + SUBSTRING(Data, 36, 1) + SUBSTRING(Data, 35, 1) + SUBSTRING(Data, 34, 1) AS raw_MyFloat,
       SUBSTRING(Data, 46, 1) + SUBSTRING(Data, 45, 1) + SUBSTRING(Data, 44, 1) + SUBSTRING(Data, 43, 1) + SUBSTRING(Data, 42, 1) AS raw_MyDecimal
FROM   LazarusRecord

Note that we're ignoring the first four bytes of each record (TagA, TagB and Fsize) and aren't paying attention to whether the column actually contains NULL.

We could then convert each column to its correct data type by converting the above statement into a common table expression and selecting from it as follows:

WITH cteRaw AS
(
  -- the SELECT statement above
)
SELECT CONVERT(INT,      raw_MyInt)      AS MyInt,
       CONVERT(SMALLINT, raw_MySmallInt) AS MySmallInt,
       CONVERT(TINYINT,  raw_MyTinyInt)  AS MyTinyInt,
       CONVERT(CHAR(6),  raw_MyChar)     AS MyChar,
       CONVERT(MONEY,    raw_MyMoney)    AS MyMoney,
       DATEADD(ms, CONVERT(DECIMAL(19,0), CONVERT(INT, SUBSTRING(raw_MyDateTime, 5, 4))) / 0.3, DATEADD(d, CONVERT(INT, SUBSTRING(raw_MyDateTime, 1, 4)), '19000101')) AS MyDateTime,
       CASE WHEN SUBSTRING(raw_MyDecimal, 5, 1) = 0x01 THEN 1 ELSE -1 END * CONVERT(DECIMAL(9,2), CONVERT(BIGINT, SUBSTRING(raw_MyDecimal, 1, 4)) * CONVERT(DECIMAL(9,2), 0.01)) AS MyDecimal
FROM   cteRaw

If you run the above query you'll find it outputs a rowset like this one:

MyInt	MySmallInt	MyTinyInt	MyChar	MyMoney	MyDateTime	MyDecimal
0	100	1	XYZ1	1.0001	2009-01-01 01:00:00.000	1.01
2000000	100	2	XYZ2	2.0002	2009-01-01 02:00:00.000	2.02
3000000	300	2	XYZ3	3.0003	2009-01-01 03:00:00.000	3.03
4000000	400	4	XYZ4	4.0004	2009-01-01 04:00:00.000	4.04
5000000	500	5	XYZ5	5.0005	2009-01-01 05:00:00.000	5.05
etc.

So - we've recovered our data. Right?

Well, we're getting there - but the job is far from over. We've haven't paid any attention to the NULL bitmap, haven't converted the raw_MyFloat column into MyFloat, and haven't parsed-out the variable-length columns. Also, we've hard-coded lots of logic based upon our knowledge of the table structure (like the * 0.01 converting raw_MyDecimal into MyDecimal, for example).

Okay, let's attack these issues one-by-one.

Adding Support for the NULL Bitmap

As TestTable has 10 columns, the NULL bitmap will be two bytes wide (1 bit per column, rounded up to the nearest byte). And as the NULL bitmap immediately follows Fdata we can simply add

       SUBSTRING(Data, 50, 1) + SUBSTRING(Data, 49, 1) AS NullBitmap

to the end of the list of columns select by cteRaw. For the first row (which has a NULL MyInt) this would produce a value of 0xFC01 (or 1111110000000001 in binary) for NullBitmap. Bit 0 (which represents MyInt) is 1, meaning NULL; bits 1 through 9 (which represent the other nine columns) are 0 meaning those columns are not NULL; bits 10 through 15 do not map to a column within the table and so have no meaning (although they are always actually set to 1).

When recovering all my lost data I had been using the bitwise AND operator (&) to test which bits were set. This worked fine until I needed to recover a particularly wide table: as the bitwise AND operator can only be used with integer data types, and as the largest such data type in SQL Server is BIGINT (which is 64 bits wide) the widest table which can be supported via this approach is 64 columns wide. My table had more columns than that, so I resorted to converting NullBitmap into a string of '1's and '0's and using a simple SUBSTRING on it. I created a user-defined function to do this:

CREATE FUNCTION dbo.fnVarBinaryToBinaryString
(
  @Input VARBINARY(64)
)
RETURNS VARCHAR(512)
    -- WARNING: input bytes and output (string) bits are reversed
AS
BEGIN

  DECLARE @Result VARCHAR(512)
  SET @Result = ''

  DECLARE @Offset INT
  SELECT @Offset = DATALENGTH(@Input)

  WHILE @Offset > 0
  BEGIN
    SELECT @Result = @Result +
           CASE SUBSTRING(@Input, @Offset, 1) & 15 -- 0x0F
             WHEN 0x00 THEN '0000'
             WHEN 0x01 THEN '1000'
             WHEN 0x02 THEN '0100'
             WHEN 0x03 THEN '1100'
             WHEN 0x04 THEN '0010'
             WHEN 0x05 THEN '1010'
             WHEN 0x06 THEN '0110'
             WHEN 0x07 THEN '1110'
             WHEN 0x08 THEN '0001'
             WHEN 0x09 THEN '1001'
             WHEN 0x0A THEN '0101'
             WHEN 0x0B THEN '1101'
             WHEN 0x0C THEN '0011'
             WHEN 0x0D THEN '1011'
             WHEN 0x0E THEN '0111'
             WHEN 0x0F THEN '1111'
           END +
           CASE SUBSTRING(@Input, @Offset, 1) & 240 -- 0xF0
             WHEN 0x00 THEN '0000'
             WHEN 0x10 THEN '1000'
             WHEN 0x20 THEN '0100'
             WHEN 0x30 THEN '1100'
             WHEN 0x40 THEN '0010'
             WHEN 0x50 THEN '1010'
             WHEN 0x60 THEN '0110'
             WHEN 0x70 THEN '1110'
             WHEN 0x80 THEN '0001'
             WHEN 0x90 THEN '1001'
             WHEN 0xA0 THEN '0101'
             WHEN 0xB0 THEN '1101'
             WHEN 0xC0 THEN '0011'
             WHEN 0xD0 THEN '1011'
             WHEN 0xE0 THEN '0111'
             WHEN 0xF0 THEN '1111'
           END
    SET @Offset = @Offset - 1
  END

  RETURN @Result
END
GO

To use this function when parsing our TestTable, cteRaw has the following column appended:

       dbo.fnVarBinaryToBinaryString(SUBSTRING(Data, 50, 1) + SUBSTRING(Data, 49, 1)) AS NullBitmapString

and the SELECT statement which calls cteRaw has CASE statements wrapping each conversion. For example:

SELECT CASE WHEN SUBSTRING(NullBitmapString, 1, 1) = '1' THEN NULL ELSE CONVERT(INT,      raw_MyInt)      END AS MyInt,
       CASE WHEN SUBSTRING(NullBitmapString, 2, 1) = '1' THEN NULL ELSE CONVERT(SMALLINT, raw_MySmallInt) END AS MySmallInt,
       CASE WHEN SUBSTRING(NullBitmapString, 3, 1) = '1' THEN NULL ELSE CONVERT(TINYINT,  raw_MyTinyInt)  END AS MyTinyInt
       -- etc.
FROM   cteRaw

If we use this revised version of our parsing query we get a rowset which looks like this:

MyInt	MySmallInt	MyTinyInt	MyChar	MyMoney	MyDateTime	MyDecimal
NULL	100	1	XYZ1	1.0001	2009-01-01 01:00:00.000	1.01
2000000	NULL	2	XYZ2	2.0002	2009-01-01 02:00:00.000	2.02
3000000	300	NULL	XYZ3	3.0003	2009-01-01 03:00:00.000	3.03
4000000	400	4	XYZ4	4.0004	2009-01-01 04:00:00.000	4.04
5000000	500	5	XYZ5	5.0005	2009-01-01 05:00:00.000	5.05
etc.

Note that the NULLs match where they were placed when TestTable was created back in Part 2 of this series.

Adding Support for FLOATs

FLOATs are stored in an 8-byte structure according to IEEE 754 and although you can convert from a FLOAT to a BINARY(8) using the CONVERT function, the reverse is not true. (You get an “Explicit conversion from data type binary to float is not allowed” error if you try.) This means you have to parse the IEEE 754 format yourself. I didn’t fancy that so lifted something from the web (credit to Jeff Moden and Peter Larsson - see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81849) which did the trick:

USE DataRecovery
GO

CREATE FUNCTION dbo.fnLazarusBinaryFloat2Float
(
  @BinaryFloat BINARY(8)
)
-- from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81849
RETURNS FLOAT
AS
BEGIN

  DECLARE
  @Part      TINYINT,
  @PartValue TINYINT,
  @Mask      TINYINT,
  @Mantissa  FLOAT,
  @Exponent  SMALLINT,
  @Bit       TINYINT,
  @Ln2       FLOAT,
  @BigValue  BIGINT

  SELECT
  @Part     = 1,
  @Mantissa = 1,
  @Bit      = 1,
  @Ln2      = LOG(2),
  @BigValue = CAST(@BinaryFloat AS BIGINT),
  @Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

  WHILE @Part <= 8
  BEGIN
    SELECT @Part      = @Part + 1,
           @PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
           @Mask      = CASE WHEN @Part = 2 THEN 8 ELSE 128 END

    WHILE @Mask > 0
    BEGIN
      IF @PartValue & @Mask > 0
        SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

      SET @Bit      = @Bit + 1
      SET @Mask     = @Mask / 2
    END

  END

  RETURN SIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
GO

So now we can add the following column to the statement which selects from cteRaw:

       CASE WHEN SUBSTRING(NullBitmapString, 7, 1) = '1' THEN NULL ELSE dbo.fnLazarusBinaryFloat2Float(CONVERT(BINARY(8), raw_MyFloat)) END AS MyFloat

This adds MyFloat to the recovered rowset:

MyInt	MySmallInt	MyTinyInt	MyChar	MyMoney	MyDateTime	MyFloat	MyDecimal
NULL	100	1	XYZ1	1.0001	2009-01-01 01:00:00.000	1.000001	1.01
2000000	NULL	2	XYZ2	2.0002	2009-01-01 02:00:00.000	2.000002	2.02
3000000	300	NULL	XYZ3	3.0003	2009-01-01 03:00:00.000	3.000003	3.03
4000000	400	4	XYZ4	4.0004	2009-01-01 04:00:00.000	4.000004	4.04
5000000	500	5	XYZ5	5.0005	2009-01-01 05:00:00.000	5.000005	5.05
etc.

Adding Support for Variable-Length Columns

Immediately after the NULL bitmap is a two-byte field called VarCount which provides a count of the number of variable-length columns in the record. This is followed by the VarOffset array of column end offsets. Note that trailing variable-length columns have no entry in the VarOffset array so VarCount may be less than the number of variable-length columns in the table definition. Also, if the current record is a forwarded record, VarOffset will include the end offset of the back-pointer too, but VarCount will have been not have been incremented to reflect this pseudo-column.

As the first byte of the first variable-length column starts immediately after the VarOffset array, and as the number of entries in the VarOffset array can vary from record to record, we need to calculate where that’ll be. In fact, because the VarOffset array stores the offset of the last byte of each variable-length column it actually makes more sense for us to calculate the offset of the byte immediately prior to the first variable-length column. For TestTable this would be performed as follows:

50 + 2 + (CONVERT(SMALLINT, SUBSTRING(Data, 52, 1) + SUBSTRING(Data, 51, 1)) * 2) + (CONVERT(TINYINT, SUBSTRING(Data, 1, 1)) & 0x0E) AS endpos_

That looks a little odd, so let me walk you through it. For TestTable, we read NullBitmap from the 49th and 50th byte of the record. That’s where the initial 50 comes from in the formula above – it’s just skipping over everything up to and including the NullBitmap. Then we have to skip two bytes for VarCount. Then we have to actually read VarCount (the 51st and 52nd bytes) and skip over the VarOffset array, each entry of which is two bytes long. Then we have to account for the back-pointer, if present. The RecordType is in bits 1 to 3 of TagA, the 1st byte in the record. We’ve already filtered out all RecordTypes except 0 (primary records) and 1 (forwarded records). If we use a bitwise AND operator (&) against TagA specifying a bit pattern which extracts bits 1 to 3 only (i.e. 0x0E) we’ll get the binary value 00000000 (0x00) for a primary record or 00000010 (0x02) for a forwarded record. By adding this value to our current position we move on 0 bytes for a primary record or 2 bytes for a forwarded record, thus skipping over the 'extra' entry in VarOffset which was not included in VarCount. The column alias I’m using here, endpos_, looks odd I admit but will hopefully make a little more sense after the next post.

Obviously we also need to read the ending offsets for our two variable-length columns from the VarOffsets array:

CONVERT(INT, SUBSTRING(Data, 54, 1) + SUBSTRING(Data, 53, 1)) AS endpos_MyVarChar,
CONVERT(INT, SUBSTRING(Data, 56, 1) + SUBSTRING(Data, 55, 1)) AS endpos_MyNVarChar

Now, most of the records in TestTable will have two entries in the VarOffsets array and so will have the data for MyVarChar starting in the 57th byte. But we know that row 3 has a NULL MyNVarChar, so the VarOffsets array will be shorter than usual (trailing NULL columns have no entry in the VarOffsetArray). For row 3, therefore, the first byte of MyVarChar will be the 55th byte of the record. This in turn means that for row 3, the value extracted for endpos_MyNVarChar above will be meaningless – it’ll have been read from the first two bytes of MyVarChar. If we have our wits about us, we’ll never actually use endpos_MyNVarChar is we know that MyNVarChar is NULL.

In our final version of the parsing code below, we've added the code to establish values for endpos_MyVarChar and endpos_MyNVarChar to our common table expression and have included Data as a column available to the outer SELECT statement. This means that the outer SELECT statement can then use a simple SUBSTRING to extract values for MyVarChar and MyNVarChar. As SUBSTRING uses 1-based positions, and as both endpos_MyVarChar and endpos_MyNVarChar (and, in fact, endpos_) specify 0-based offsets, we’ve had to allow for that.

WITH cteRaw AS
(
  SELECT Data,
         SUBSTRING(Data,  8, 1) + SUBSTRING(Data,  7, 1) + SUBSTRING(Data, 6, 1) + SUBSTRING(Data, 5, 1) AS raw_MyInt,
         SUBSTRING(Data, 10, 1) + SUBSTRING(Data,  9, 1) AS raw_MySmallInt,
         SUBSTRING(Data, 11, 1) AS raw_MyTinyInt,
         SUBSTRING(Data, 12, 6) AS raw_MyChar,
         SUBSTRING(Data, 25, 1) + SUBSTRING(Data, 24, 1) + SUBSTRING(Data, 23, 1) + SUBSTRING(Data, 22, 1) + SUBSTRING(Data, 21, 1) + SUBSTRING(Data, 20, 1) + SUBSTRING(Data, 19, 1) + SUBSTRING(Data, 18, 1) AS raw_MyMoney,
         SUBSTRING(Data, 33, 1) + SUBSTRING(Data, 32, 1) + SUBSTRING(Data, 31, 1) + SUBSTRING(Data, 30, 1) + SUBSTRING(Data, 29, 1) + SUBSTRING(Data, 28, 1) + SUBSTRING(Data, 27, 1) + SUBSTRING(Data, 26, 1) AS raw_MyDateTime,
         SUBSTRING(Data, 41, 1) + SUBSTRING(Data, 40, 1) + SUBSTRING(Data, 39, 1) + SUBSTRING(Data, 38, 1) + SUBSTRING(Data, 37, 1) + SUBSTRING(Data, 36, 1) + SUBSTRING(Data, 35, 1) + SUBSTRING(Data, 34, 1) AS raw_MyFloat,
         SUBSTRING(Data, 46, 1) + SUBSTRING(Data, 45, 1) + SUBSTRING(Data, 44, 1) + SUBSTRING(Data, 43, 1) + SUBSTRING(Data, 42, 1) AS raw_MyDecimal,
         dbo.fnVarBinaryToBinaryString(SUBSTRING(Data, 50, 1) + SUBSTRING(Data, 49, 1)) AS NullBitmapString,
         50 + 2 + (CONVERT(SMALLINT, SUBSTRING(Data, 52, 1) + SUBSTRING(Data, 51, 1)) * 2) + (CONVERT(TINYINT, SUBSTRING(Data, 1, 1)) & 0x0E) AS endpos_,
         CONVERT(INT, SUBSTRING(Data, 54, 1) + SUBSTRING(Data, 53, 1)) AS endpos_MyVarChar,
         CONVERT(INT, SUBSTRING(Data, 56, 1) + SUBSTRING(Data, 55, 1)) AS endpos_MyNVarChar
  FROM   LazarusRecord
)
SELECT CASE WHEN SUBSTRING(NullBitmapString,  1, 1) = '1' THEN NULL ELSE CONVERT(INT, raw_MyInt) END AS MyInt,
       CASE WHEN SUBSTRING(NullBitmapString,  2, 1) = '1' THEN NULL ELSE CONVERT(SMALLINT, raw_MySmallInt) END AS MySmallInt,
       CASE WHEN SUBSTRING(NullBitmapString,  3, 1) = '1' THEN NULL ELSE CONVERT(TINYINT, raw_MyTinyInt) END AS MyTinyInt,
       CASE WHEN SUBSTRING(NullBitmapString,  4, 1) = '1' THEN NULL ELSE CONVERT(CHAR(6), raw_MyChar) END AS MyChar,
       CASE WHEN SUBSTRING(NullBitmapString,  5, 1) = '1' THEN NULL ELSE CONVERT(MONEY, raw_MyMoney) END AS MyMoney,
       CASE WHEN SUBSTRING(NullBitmapString,  6, 1) = '1' THEN NULL ELSE DATEADD(ms, CONVERT(DECIMAL(19,0), CONVERT(INT, SUBSTRING(raw_MyDateTime, 5, 4))) / 0.3, DATEADD(d, CONVERT(INT, SUBSTRING(raw_MyDateTime, 1, 4)), '19000101')) END AS MyDateTime,
       CASE WHEN SUBSTRING(NullBitmapString,  7, 1) = '1' THEN NULL ELSE dbo.fnLazarusBinaryFloat2Float(CONVERT(BINARY(8), raw_MyFloat)) END AS MyFloat,
       CASE WHEN SUBSTRING(NullBitmapString,  8, 1) = '1' THEN NULL ELSE CASE WHEN SUBSTRING(raw_MyDecimal, 5, 1) = 0x01 THEN 1 ELSE -1 END * CONVERT(DECIMAL(9,2), CONVERT(BIGINT, SUBSTRING(raw_MyDecimal, 1, 4)) * CONVERT(DECIMAL(9,2), 0.01)) END AS MyDecimal,
       CASE WHEN SUBSTRING(NullBitmapString,  9, 1) = '1' THEN NULL ELSE CONVERT(VARCHAR(255), SUBSTRING(Data, endpos_ + 1, endpos_MyVarChar - endpos_)) END AS MyVarChar,
       CASE WHEN SUBSTRING(NullBitmapString, 10, 1) = '1' THEN NULL ELSE CONVERT(NVARCHAR(255), SUBSTRING(Data, endpos_MyVarChar + 1, endpos_MyNVarChar - endpos_MyVarChar)) END AS MyNVarChar
FROM   cteRaw

This statement yields the following rowset:

MyInt	MySmallInt	MyTinyInt	MyChar	MyMoney	MyDateTime	MyFloat	MyDecimal	MyVarChar	MyNVarChar
NULL	100	1	XYZ1	1.0001	2009-01-01 01:00:00.000	1.000001	1.01	A	X1
2000000	NULL	2	XYZ2	2.0002	2009-01-01 02:00:00.000	2.000002	2.02	AA	X2
3000000	300	NULL	XYZ3	3.0003	2009-01-01 03:00:00.000	3.000003	3.03	AAA	NULL
4000000	400	4	XYZ4	4.0004	2009-01-01 04:00:00.000	4.000004	4.04	AAAA	X4
5000000	500	5	XYZ5	5.0005	2009-01-01 05:00:00.000	5.000005	5.05	AAAAA	X5
etc.

I don’t know about you, but that looks we’ve fully recovery our dropped TestTable. I hope it was worth the effort.

The one downside is that we’ve had to hard-code the above statement for the specific structure we know TestTable had. In the next post I’ll walk through the dynamic SQL I used to generate code like the above for an arbitrary table, using the meta-data in the LazarusColumn table.

See Also

• Undropping a SQL Server Table - Part 1 - Background and Approach
• Undropping a SQL Server Table - Part 2 - Who's Page is it Anyway?
• Undropping a SQL Server Table - Part 3 - Splitting a Page into Rows
• Undropping a SQL Server Table - Part 5 - Auto-Generating the Parsing SQL
• Undropping a SQL Server Table - Part 6 - Coping with Missing Meta-Data