Introduction
This series of posts covers the steps required to recover 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), parsed each page into individual records (Part 3), and parsed those records (which were still in raw hexadecimal form) into individual columns to create a standard SQL Server rowset (Part 4).
This part will present the SQL which can be used to auto-generate the statement we came up with in the last post for a table with an arbitrary structure.
Record Structure
The statement we're trying to generate is that which appears at the end of (Part 4). I should warn you in advance that the code presented below is ugly. Very ugly. I shouldn't have attempted to generate the SQL via SQL. But I did. Or, at least I should have refactored some of the repetitive code into user-defined function. But I didn't. So the code below is what I actually used to generate the SQL to perform the parsing. Sorry.
I won't walk through the SQL, as I have done with previous samples, as I walked through the SQL we're trying to generate in the last post and because I think the comments herein should fully explain what's going on.
USE DataRecovery GO -- note that only the following data types are supported by this script: -- CHAR, DATETIME, DECIMAL, FLOAT, INT, MONEY, NVARCHAR, SMALLINT, TINYINT, VARCHAR -- specify the table we're attempting to recover; we use this to key into LazarusColumn -- to establish the structure of the table whose records are in LazarusRecord DECLARE @TableName SYSNAME SET @TableName = 'TestTable' -- <<< change this -- @SQL will contain the dynamic SQL which we'll ultimately execute DECLARE @SQL VARCHAR(MAX) -- @Position contains the 1-based position within LazarusRecord.Data which we're currently -- focussed on DECLARE @Position INT SET @Position = 1 -- begin the common table expression (CTE) by selecting the raw Data, as we'll need this -- to extract any variable-length strings SELECT @SQL = 'WITH cteRaw AS ( SELECT Data, ' -- move past StatusBitsA (1) and StatusBitsB (1) SET @Position = @Position + 2 -- extract the ColumnCountOffset SELECT @SQL = @SQL + 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS ColumnCountOffset, ' -- move past ColumnCountOffset SELECT @Position = @Position + 2 -- add the SQL to perform raw extracts of fixed-length columns SELECT @SQL = @SQL + CASE xtype WHEN 56 /* INT */ THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+3) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+2) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS raw_' + [name] + ', ' WHEN 52 /* SMALLINT */ THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS raw_' + [name] + ', ' WHEN 48 /* TINYINT */ THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS raw_' +[name] + ', ' WHEN 175 /* CHAR */ THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', ' + CONVERT(VARCHAR, [length]) + ') AS raw_' + [name] + ', ' WHEN 60 /* MONEY */ THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+7) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+6) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+5) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+4) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+3) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+2) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS raw_' + [name] + ', ' WHEN 61 /* DATETIME */ THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+7) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+6) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+5) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+4) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+3) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+2) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS raw_' + [name] + ', ' WHEN 62 /* FLOAT */ THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+7) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+6) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+5) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+4) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+3) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+2) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS raw_' + [name] + ', ' WHEN 106 /* DECIMAL */ THEN CASE [length] WHEN 5 THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 4) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 3) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 2) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position ) + ', 1) AS raw_' + [name] + ', ' WHEN 9 THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 8) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 7) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 6) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 5) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 4) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 3) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 2) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position ) + ', 1) AS raw_' + [name] + ', ' WHEN 13 THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+12) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+11) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+10) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 9) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 8) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 7) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+ 6) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+5) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+4) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+3) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+2) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position ) + ', 1) AS raw_' + [name] + ', ' WHEN 17 THEN 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+16) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+15) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+14) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+13) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+12) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+11) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+10) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+4) + ', 9) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+8) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+7) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+6) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+5) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+4) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+3) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+2) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1)+ SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS raw_' + [name] + ', ' END ELSE '' END, -- update @Position as we go (this will updated each time we read a LazarusColumn) @Position = @Position + CASE WHEN xtype IN (56,52,48,175,60,61,62,106,108) THEN [length] ELSE 0 END FROM LazarusColumn WHERE TableName = @TableName ORDER BY colid -- add the SQL to extract the ColumnCount (should be the same for all rows, and ideally we would -- compare this with the number of rows in LazarusColumn for this table - but we don't) SELECT @SQL = @SQL + 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1) AS ColumnCount, ' -- move past ColumnCount SELECT @Position = @Position + 2 -- @NullBitmapSQL will contain the SQL clause we need to extract the NULL bitmap DECLARE @NullBitmapSQL VARCHAR(MAX) SELECT @NullBitmapSQL = '' -- add the SQL to extract the NULL bitmap; its length varies according to the number of -- columns in the table (1 bit per column, rounded up to a byte interval) SELECT @NullBitmapSQL = @NullBitmapSQL + 'SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position + colid / 8) + ', 1) + ' FROM LazarusColumn WHERE TableName = @TableName AND (colid % 8) = 1 ORDER BY colid DESC -- remove the trailing '+ ' from the @NullBitmap SQL clause, then append this clause to the -- full SQL together which code to parse the NULL bitmap into a string of 1s and 0s. SELECT @NullBitmapSQL = SUBSTRING(@NullBitmapSQL, 1, LEN(@NullBitmapSQL) - 2) SELECT @SQL = @SQL + @NullBitmapSQL + ' AS NullBitmap, dbo.fnVarBinaryToBinaryString(' + @NullBitmapSQL + ') AS NullBitmapString, ' -- move past the NULL bitmap SELECT @Position = @Position + ((COUNT(*) - 1) / 8) + 1 FROM LazarusColumn WHERE TableName = @TableName -- @FixedLength stores the size of the fixed-length portion of the record; so that's TagA, -- TagB, Fsize, Fdata, Ncol and NullBits (using the mnemonics we used in Parts 2 and 4) DECLARE @FixedLength INT SELECT @FixedLength = @Position - 1 -- add the SQL to calculate endpos_, being the position 1 byte before the start of the first -- variable-length column; note that trailing variable-length columns which are NULL don't -- have an end position stored and that forwarded records have an additional 2-byte offset -- to the back-pointer; this is all explained in Part 4 SELECT @SQL = @SQL + CONVERT(VARCHAR, @FixedLength) + ' + 2 + (CONVERT(SMALLINT,SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1)) * 2) + (CONVERT(TINYINT, SUBSTRING(Data, 1, 1)) & 0x0E) AS endpos_, ' -- move past VariableLengthColumnCount SELECT @Position = @Position + 2 -- add the SQL to establish the ending position of each variable-length column SELECT @SQL = @SQL + CASE xtype WHEN 167 /* VARCHAR */ THEN 'CONVERT(INT, SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1)) AS endpos_' +[name] + ', ' WHEN 231 /* NVARCHAR */ THEN 'CONVERT(INT, SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position+1) + ', 1) + SUBSTRING(Data, ' + CONVERT(VARCHAR, @Position) + ', 1)) AS endpos_' +[name] + ', ' ELSE '' END, -- update @Position as we go (this will updated each time we read a LazarusColumn) @Position = @Position + CASE WHEN xtype IN (167,231) THEN 2 ELSE 0 END -- 2 bytes for each end position FROM LazarusColumn WHERE TableName = @TableName ORDER BY colid -- remove the trailing ', ' (each column added above has a trailing comma; we don't want the last) SELECT @SQL = SUBSTRING(@SQL, 1, DATALENGTH(@SQL)-2) -- end the creation of the common table expression and begin the outer SELECT statement; note that -- we check that the rows in LazarusRecord are for the table we're trying to recover - this should -- be unnecessary but does no harm; also note that we're only including rows where TagB (the 2nd -- byte of the data record) is 0 - experience has taught me that if TagB contains any other value -- then the data is 'noise' - perhaps it's a deleted record - I'm not sure SELECT @SQL = @SQL + ' FROM LazarusRecord WHERE TableName = ''' + @TableName + ''' AND SUBSTRING(Data, 2, 1) = 0x00) SELECT ' -- in order to use SUBSTRING to snip out the variable-length columns we need to know where -- each starts and ends; a variable-length column starts one byte beyond where the last one -- ended to we need to keep track of the name of the previous column so we can look-up its -- ending position on a row-by-row basis; the first variable-length column will have no -- previous column, so we'll end up looking for a it's position as 'endpos_' (where 'endpos_' -- is normally followed by a column name; /this/ is why we used this strange column name -- when working out where the end of VarCount is DECLARE @PreviousVariableLengthColumnName SYSNAME SELECT @PreviousVariableLengthColumnName = '' -- add the SQL to convert the raw binary data into the correct value (or NULL), based upon the -- data type of the column SELECT @SQL = @SQL + 'CASE WHEN SUBSTRING(NullBitmapString, ' + CONVERT(VARCHAR, colid) + ', 1) = ''1'' THEN NULL ELSE ' + CASE xtype WHEN 56 /* INT */ THEN 'CONVERT(INT, raw_' + [name] + ')' WHEN 52 /* SMALLINT */ THEN 'CONVERT(SMALLINT, raw_' + [name] + ')' WHEN 48 /* TINYINT */ THEN 'CONVERT(TINYINT, raw_' + [name] + ')' WHEN 175 /* CHAR */ THEN 'CONVERT(CHAR(' + CONVERT(VARCHAR, [length]) + '), raw_' + [name] + ')' WHEN 60 /* MONEY */ THEN 'CONVERT(MONEY, raw_' + [name] + ')' WHEN 61 /* DATETIME */ THEN 'DATEADD(ms, CONVERT(DECIMAL(19,0), CONVERT(INT, SUBSTRING(raw_' + [name] + ', 5, 4))) / 0.3, DATEADD(d, CONVERT(INT, SUBSTRING(raw_' + [name] + ', 1, 4)), ''19000101''))' WHEN 62 /* FLOAT */ THEN 'dbo.fnLazarusBinaryFloat2Float(CONVERT(BINARY(8), raw_' + [name] + '))' WHEN 106 /* DECIMAL */ THEN 'CASE WHEN SUBSTRING(raw_' + [name] + ', ' + CONVERT(VARCHAR, [length]) + ', 1) = 0x01 THEN 1 ELSE -1 END * CONVERT(DECIMAL(' + CONVERT(VARCHAR, [xprec]) + ',' + CONVERT(VARCHAR, [xscale]) + '), CONVERT(BIGINT, SUBSTRING(raw_' + [name] + ', 1, ' + CONVERT(VARCHAR, [length]-1) + ')) ' + CASE WHEN [xscale] = 0 THEN '' ELSE '* CONVERT(DECIMAL('+ CONVERT(VARCHAR, [xprec]) + ',' + CONVERT(VARCHAR, [xscale]) + '), 0.' + REPLICATE('0', [xscale]-1) + '1)' END + ')' WHEN 167 /* VARCHAR */ THEN 'CONVERT(VARCHAR(255), SUBSTRING(Data, endpos_' + @PreviousVariableLengthColumnName + ' + 1, endpos_' + [name] + ' - endpos_' + @PreviousVariableLengthColumnName + '))' WHEN 231 /* NVARCHAR */ THEN 'CONVERT(NVARCHAR(255), SUBSTRING(Data, endpos_' + @PreviousVariableLengthColumnName + ' + 1, endpos_' + [name] + ' - endpos_' + @PreviousVariableLengthColumnName + '))' ELSE ' raw_' + [name] + ' ' END + ' END AS ' + [name] + ', ', -- update @PreviousVariableLengthColumnName as we go (this will updated each time we reada LazarusColumn) @PreviousVariableLengthColumnName = CASE WHEN xtype IN (167,231) THEN [name] ELSE @PreviousVariableLengthColumnName END FROM LazarusColumn WHERE TableName = @TableName ORDER BY colid -- remove the trailing ', ' from the SQL so far and add the final FROM clause SELECT @SQL = SUBSTRING(@SQL, 1, DATALENGTH(@SQL)-2) SELECT @SQL = @SQL + ' FROM cteRaw' -- display or EXECUTE the statement(depending upon how brave we're feeling --SELECT @SQL EXECUTE (@SQL) GO
I told you it was bad! If you run the above statement with the final SELECT @SQL uncommented you'll see that the generated SQL looks like this:
WITH cteRaw AS ( SELECT Data, SUBSTRING(Data, 4, 1) + SUBSTRING(Data, 3, 1) AS ColumnCountOffset, 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, SUBSTRING(Data, 48, 1) + SUBSTRING(Data, 47, 1) AS ColumnCount, SUBSTRING(Data, 50, 1) + SUBSTRING(Data, 49, 1) AS NullBitmap, 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 WHERE TableName = 'TestTable' AND SUBSTRING(Data, 2, 1) = 0x00) 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
Again, not nice. But what is nice is the rowset which results from running it. It's our undropped TestTable. And, unlike in Part 4, we didn't have to hard-code its structure anywhere.
| 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. |
The final post of the series will cover those scenarios where you don't have any meta-data to work with - i.e. you need to undrop a table but you don't know what it's original structure was.
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 4 - Parsing Rows into Columns
- Undropping a SQL Server Table - Part 6 - Coping with Missing Meta-Data
Posts
Hi, Ian.
ReplyDeleteIf one day you'll come to visit Brazil let me know and I'll be glad to buy a dinner.
I'm sure I owe you one. :)
Best,
Eduardo Costa
You're very welcome Eduardo. I'm glad you find the articles useful.
ReplyDelete