SQL Server – Undelete a Table and Restore a Single Table from Backup

Posted by Mladen Prajdic on SQL Team See other posts from SQL Team or by Mladen Prajdic
Published on Tue, 12 Oct 2010 12:12:16 GMT Indexed on 2010/12/06 16:58 UTC
Read the original article Hit count: 569

Filed under:

This post is part of the monthly community event called T-SQL Tuesday started by Adam Machanic (blog|twitter) and hosted by someone else each month. This month the host is Sankar Reddy (blog|twitter) and the topic is Misconceptions in SQL Server. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.

Let me start by saying: This code is a crazy hack that is to never be used unless you really, really have to. Really!

And I don’t think there’s a time when you would really have to use it for real. Because it’s a hack there are number of things that can go wrong so play with it knowing that. I’ve managed to totally corrupt one database. :) Oh… and for those saying: yeah yeah.. you have a single table in a file group and you’re restoring that, I say “nay nay” to you.

As we all know SQL Server can’t do single table restores from backup. This is kind of a obvious thing due to different relational integrity (RI) concerns. Since we have to maintain that we have to restore all tables represented in a RI graph. For this exercise i say BAH! to those concerns. Note that this method “works” only for simple tables that don’t have LOB and off rows data. The code can be expanded to include those but I’ve tried to leave things “simple”. Note that for this to work our table needs to be relatively static data-wise. This doesn’t work for OLTP table. Products are a perfect example of static data. They don’t change much between backups, pretty much everything depends on them and their table is one of those tables that are relatively easy to accidentally delete everything from.

This only works if the database is in Full or Bulk-Logged recovery mode for tables where the contents have been deleted or truncated but NOT when a table was dropped.

Everything we’ll talk about has to be done before the data pages are reused for other purposes. After deletion or truncation the pages are marked as reusable so you have to act fast. The best thing probably is to put the database into single user mode ASAP while you’re performing this procedure and return it to multi user after you’re done.

How do we do it?

We will be using an undocumented but known DBCC commands: DBCC PAGE, an undocumented function sys.fn_dblog and a little known DATABASE RESTORE PAGE option. All tests will be on a copy of Production.Product table in AdventureWorks database called Production.Product1 because the original table has FK constraints that prevent us from truncating it for testing.

-- create a duplicate table. This doesn't preserve indexes!
SELECT  *
INTO    AdventureWorks.Production.Product1
FROM    AdventureWorks.Production.Product

After we run this code take a full back to perform further testing.

First let’s see what the difference between DELETE and TRUNCATE is when it comes to logging. With DELETE every row deletion is logged in the transaction log. With TRUNCATE only whole data page deallocations are logged in the transaction log. Getting deleted data pages is simple. All we have to look for is row delete entry in the sys.fn_dblog output. But getting data pages that were truncated from the transaction log presents a bit of an interesting problem. I will not go into depths of IAM(Index Allocation Map) and PFS (Page Free Space) pages but suffice to say that every IAM page has intervals that tell us which data pages are allocated for a table and which aren’t. If we deep dive into the sys.fn_dblog output we can see that once you truncate a table all the pages in all the intervals are deallocated and this is shown in the PFS page transaction log entry as deallocation of pages. For every 8 pages in the same extent there is one PFS page row in the transaction log. This row holds information about all 8 pages in CSV format which means we can get to this data with some parsing. A great help for parsing this stuff is Peter Debetta’s handy function dbo.HexStrToVarBin that converts hexadecimal string into a varbinary value that can be easily converted to integer tus giving us a readable page number.

The shortened (columns removed) sys.fn_dblog output for a PFS page with CSV data for 1 extent (8 data pages) looks like this:

-- [Page ID] is displayed in hex format. 
-- To convert it to readable int we'll use dbo.HexStrToVarBin function found at 
-- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-convert-hex-string-to-varbinary.aspx 
-- This function must be installed in the master database
SELECT  Context, AllocUnitName, [Page ID], Description
FROM    sys.fn_dblog(NULL, NULL)
WHERE    [Current LSN] = '00000031:00000a46:007d'

The pages at the end marked with 0x00—> are pages that are allocated in the extent but are not part of a table.

We can inspect the raw content of each data page with a DBCC PAGE command:

-- we need this trace flag to redirect output to the query window.
DBCC TRACEON (3604); 
-- WITH TABLERESULTS gives us data in table format instead of message format
-- we use format option 3 because it's the easiest to read and manipulate further on
DBCC PAGE (AdventureWorks, 1, 613, 3) WITH TABLERESULTS

Since the DBACC PAGE output can be quite extensive I won’t put it here. You can see an example of it in the link at the beginning of this section.

Getting deleted data back

When we run a delete statement every row to be deleted is marked as a ghost record. A background process periodically cleans up those rows. A huge misconception is that the data is actually removed. It’s not. Only the pointers to the rows are removed while the data itself is still on the data page. We just can’t access it with normal means. To get those pointers back we need to restore every deleted page using the RESTORE PAGE option mentioned above. This restore must be done from a full backup, followed by any differential and log backups that you may have. This is necessary to bring the pages up to the same point in time as the rest of the data. However the restore doesn’t magically connect the restored page back to the original table. It simply replaces the current page with the one from the backup. After the restore we use the DBCC PAGE to read data directly from all data pages and insert that data into a temporary table. To finish the RESTORE PAGE procedure we finally have to take a tail log backup (simple backup of the transaction log) and restore it back. We can now insert data from the temporary table to our original table by hand.

Getting truncated data back

When we run a truncate the truncated data pages aren’t touched at all. Even the pointers to rows stay unchanged. Because of this getting data back from truncated table is simple. we just have to find out which pages belonged to our table and use DBCC PAGE to read data off of them. No restore is necessary. Turns out that the problems we had with finding the data pages is alleviated by not having to do a RESTORE PAGE procedure.

Stop stalling… show me The Code!

This is the code for getting back deleted and truncated data back. It’s commented in all the right places so don’t be afraid to take a closer look. Make sure you have a full backup before trying this out. Also I suggest that the last step of backing and restoring the tail log is performed by hand.

USE master
GO
IF OBJECT_ID('dbo.HexStrToVarBin') IS NULL
    RAISERROR ('No dbo.HexStrToVarBin installed. 
Go to http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-convert-hex-string-to-varbinary.aspx 
and install it in master database' , 18, 1)    
SET NOCOUNT ON
BEGIN TRY    
    DECLARE @dbName VARCHAR(1000), @schemaName VARCHAR(1000), @tableName VARCHAR(1000),
            @fullBackupName VARCHAR(1000), @undeletedTableName VARCHAR(1000),
            @sql VARCHAR(MAX), @tableWasTruncated bit;
    /*
    THE FIRST LINE ARE OUR INPUT PARAMETERS 
    In this case we're trying to recover Production.Product1 table in AdventureWorks database.
    My full backup of AdventureWorks database is at e:\AW.bak
    */
    SELECT  @dbName = 'AdventureWorks', @schemaName = 'Production', @tableName = 'Product1', @fullBackupName = 'e:\AW.bak', 
            @undeletedTableName = '##' + @tableName + '_Undeleted', @tableWasTruncated = 0,
            -- copy the structure from original table to a temp table that we'll fill with restored data
            @sql = 'IF OBJECT_ID(''tempdb..' + @undeletedTableName + 
                                 ''') IS NOT NULL DROP TABLE ' + @undeletedTableName +
                   ' SELECT *' +
                   ' INTO ' + @undeletedTableName +
                   ' FROM [' + @dbName + '].[' + @schemaName + '].[' + @tableName + ']' +
                   ' WHERE 1 = 0'
    EXEC (@sql)
    IF OBJECT_ID('tempdb..#PagesToRestore') IS NOT NULL 
        DROP TABLE #PagesToRestore        
    /* FIND DATA PAGES WE NEED TO RESTORE*/
    CREATE TABLE #PagesToRestore ([ID] INT IDENTITY(1,1), [FileID] INT, [PageID] INT, 
                                    [SQLtoExec] VARCHAR(1000)) -- DBCC PACE statement to run later
    RAISERROR ('Looking for deleted pages...', 10, 1)    
    -- use T-LOG direct read to get deleted data pages
    INSERT INTO #PagesToRestore([FileID], [PageID], [SQLtoExec])
    EXEC('USE [' + @dbName + '];SELECT  FileID, PageID, ''DBCC TRACEON (3604); DBCC PAGE ([' + @dbName + 
            '], '' + FileID + '', '' + PageID + '', 3) WITH TABLERESULTS'' as SQLToExec
FROM (SELECT DISTINCT LEFT([Page ID], 4) AS FileID, CONVERT(VARCHAR(100), ' + 
             'CONVERT(INT, master.dbo.HexStrToVarBin(SUBSTRING([Page ID], 6, 20)))) AS PageID
FROM    sys.fn_dblog(NULL, NULL)
WHERE    AllocUnitName LIKE ''%' + @schemaName  + '.' + @tableName + '%'' ' +
       'AND Context IN (''LCX_MARK_AS_GHOST'', ''LCX_HEAP'') AND Operation in (''LOP_DELETE_ROWS''))t');
SELECT  *
FROM    #PagesToRestore
    -- if upper EXEC returns 0 rows it means the table was truncated so find truncated pages
    IF (SELECT COUNT(*) FROM #PagesToRestore) = 0
    BEGIN
        RAISERROR ('No deleted pages found. Looking for truncated pages...', 10, 1)    
        -- use T-LOG read to get truncated data pages
        INSERT INTO #PagesToRestore([FileID], [PageID], [SQLtoExec])
        -- dark magic happens here
        -- because truncation simply deallocates pages we have to find out which pages were deallocated.
        -- we can find this out by looking at the PFS page row's Description column.
        -- for every deallocated extent the Description has a CSV of 8 pages in that extent.
        -- then it's just a matter of parsing it.
        -- we also remove the pages in the extent that weren't allocated to the table itself 
        -- marked with '0x00-->00'
        EXEC ('USE [' + @dbName + '];DECLARE @truncatedPages TABLE(DeallocatedPages VARCHAR(8000), IsMultipleDeallocs BIT);
INSERT INTO @truncatedPages
SELECT  REPLACE(REPLACE(Description, ''Deallocated '', ''Y''), ''0x00-->00 '', ''N'') + '';'' AS DeallocatedPages,
        CHARINDEX('';'', Description) AS IsMultipleDeallocs
FROM    (
SELECT  DISTINCT LEFT([Page ID], 4) AS FileID, CONVERT(VARCHAR(100), 
        CONVERT(INT, master.dbo.HexStrToVarBin(SUBSTRING([Page ID], 6, 20)))) AS PageID, 
        Description
FROM    sys.fn_dblog(NULL, NULL)
WHERE    Context IN (''LCX_PFS'') AND Description LIKE ''Deallocated%''
        AND AllocUnitName LIKE ''%' + @schemaName  + '.' + @tableName + '%'') t;
SELECT  FileID, PageID 
        , ''DBCC TRACEON (3604); DBCC PAGE ([' + @dbName + '], '' + FileID + '', '' + PageID + '', 3) WITH TABLERESULTS'' as SQLToExec
FROM    (
SELECT  LEFT(PageAndFile, 1) as WasPageAllocatedToTable
        , SUBSTRING(PageAndFile, 2, CHARINDEX('':'', PageAndFile) - 2 ) as FileID
        , CONVERT(VARCHAR(100), CONVERT(INT, 
                    master.dbo.HexStrToVarBin(SUBSTRING(PageAndFile, CHARINDEX('':'', PageAndFile) + 1, LEN(PageAndFile))))) as PageID
FROM    (
        SELECT  SUBSTRING(DeallocatedPages, delimPosStart, delimPosEnd - delimPosStart) as PageAndFile, IsMultipleDeallocs
        FROM    (
                SELECT  *,        
                        CHARINDEX('';'', DeallocatedPages)*(N-1) + 1 AS delimPosStart,
                        CHARINDEX('';'', DeallocatedPages)*N         
                        AS delimPosEnd
                FROM    @truncatedPages t1
                        CROSS APPLY 
                        (SELECT TOP (case when t1.IsMultipleDeallocs = 1 then 8 else 1 end) 
                                ROW_NUMBER() OVER(ORDER BY number) as N 
                        FROM master..spt_values) t2
                )t)t)t
WHERE WasPageAllocatedToTable = ''Y''')
        SELECT @tableWasTruncated = 1
    END    
    DECLARE @lastID INT, @pagesCount INT
    SELECT @lastID = 1, @pagesCount = COUNT(*) FROM #PagesToRestore
    SELECT @sql = 'Number of pages to restore: ' + CONVERT(VARCHAR(10), @pagesCount)
    IF @pagesCount = 0
        RAISERROR ('No data pages to restore.', 18, 1)    
    ELSE
        RAISERROR (@sql, 10, 1)    
    -- If the table was truncated we'll read the data directly from data pages without restoring from backup
    IF @tableWasTruncated = 0
    BEGIN 
        -- RESTORE DATA PAGES FROM FULL BACKUP IN BATCHES OF 200
        WHILE @lastID <= @pagesCount 
        BEGIN
            -- create CSV string of pages to restore
            SELECT @sql = STUFF((SELECT ',' + CONVERT(VARCHAR(100), FileID) + ':' + CONVERT(VARCHAR(100), PageID)
                                 FROM #PagesToRestore WHERE ID BETWEEN @lastID AND @lastID + 200
                                 ORDER BY ID FOR XML PATH('')), 1, 1, '')    
            SELECT @sql = 'RESTORE DATABASE [' + @dbName + '] PAGE = ''' + @sql + ''' FROM DISK = ''' + @fullBackupName + ''''
            RAISERROR ('Starting RESTORE command:' , 10, 1) WITH NOWAIT;
            RAISERROR (@sql , 10, 1) WITH NOWAIT;
            EXEC(@sql);
            RAISERROR ('Restore DONE' , 10, 1) WITH NOWAIT;
            SELECT @lastID = @lastID + 200
        END    
        /*
            If you have any differential or transaction log backups you 
            should restore them here to bring the previously restored data pages up to date        
        */
    END    
    DECLARE @dbccSinglePage TABLE 
    (
        [ParentObject] NVARCHAR(500), 
        [Object] NVARCHAR(500), 
        [Field] NVARCHAR(500), 
        [VALUE] NVARCHAR(MAX)
    )
    DECLARE @cols NVARCHAR(MAX), @paramDefinition NVARCHAR(500), @SQLtoExec VARCHAR(1000),
            @FileID VARCHAR(100), @PageID VARCHAR(100), @i INT = 1
    -- Get deleted table columns from information_schema view
    -- Need sp_executeSQL because database name can't be passed in as variable
    SELECT @cols = 'select @cols = STUFF((SELECT '', ['' + COLUMN_NAME + '']''
FROM   ' + @dbName + '.INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = ''' + @tableName + ''' AND
       TABLE_SCHEMA = ''' + @schemaName + '''
ORDER BY ORDINAL_POSITION
FOR XML PATH('''')), 1, 2, '''')', @paramDefinition = N'@cols nvarchar(max) OUTPUT'
    EXECUTE sp_executesql @cols, @paramDefinition, @cols = @cols OUTPUT    
    -- Loop through all the restored data pages,
    -- read data from them and insert them into temp table
    -- which you can then insert into the orignial deleted table
    DECLARE dbccPageCursor CURSOR GLOBAL FORWARD_ONLY FOR     
    SELECT [FileID], [PageID], [SQLtoExec] FROM #PagesToRestore ORDER BY [FileID], [PageID]            
    OPEN dbccPageCursor;
    FETCH NEXT FROM dbccPageCursor INTO @FileID, @PageID, @SQLtoExec;
    WHILE @@FETCH_STATUS = 0    
    BEGIN
        RAISERROR ('---------------------------------------------', 10, 1) WITH NOWAIT;
        SELECT @sql = 'Loop iteration: ' + CONVERT(VARCHAR(10), @i);
        RAISERROR (@sql, 10, 1) WITH NOWAIT;
        
        SELECT @sql = 'Running: ' + @SQLtoExec
        RAISERROR (@sql, 10, 1) WITH NOWAIT;         
        
        -- if something goes wrong with DBCC execution or data gathering, skip it but print error
        BEGIN TRY
            INSERT INTO @dbccSinglePage EXEC (@SQLtoExec)
            
            -- make the data insert magic happen here
            IF (SELECT CONVERT(BIGINT, [VALUE]) FROM @dbccSinglePage WHERE [Field] LIKE '%Metadata: ObjectId%') 
                = OBJECT_ID('['+@dbName+'].['+@schemaName +'].['+@tableName+']')
            BEGIN            
                DELETE    @dbccSinglePage
                WHERE    NOT ([ParentObject] LIKE 'Slot % Offset %' AND [Object] LIKE 'Slot % Column %')
                
                SELECT @sql = 'USE tempdb; ' +
                              'IF (OBJECTPROPERTY(object_id(''' + @undeletedTableName + '''), ''TableHasIdentity'') = 1) ' +
                              'SET IDENTITY_INSERT ' + @undeletedTableName + ' ON; ' +
                              'INSERT INTO ' + @undeletedTableName + 
                              '(' + @cols + ') ' + 
                              STUFF((SELECT    ' UNION ALL SELECT ' + 
                                      STUFF((SELECT ', ' + CASE WHEN VALUE = '[NULL]' THEN 'NULL' ELSE '''' + [VALUE] + '''' END
                                             FROM (
                                              -- the unicorn help here to correctly set ordinal numbers of columns in a data page
                                              -- it's turning STRING order into INT order (1,10,11,2,21 into 1,2,..10,11...21)
                                                  SELECT [ParentObject], [Object], Field, VALUE,
                                                         RIGHT('00000' + O1, 6) AS ParentObjectOrder,
                                                         RIGHT('00000' + REVERSE(LEFT(O2, CHARINDEX(' ', O2)-1)), 6) AS ObjectOrder
                                                  FROM   (
                                                         SELECT  [ParentObject], [Object], Field, VALUE,
                                                                 REPLACE(LEFT([ParentObject], 
                                                                              CHARINDEX('Offset', [ParentObject])-1), 
                                                                         'Slot ', '') AS O1,
                                                                 REVERSE(LEFT([Object], 
                                                                              CHARINDEX('Offset ', [Object])-2)) AS O2
                                                          FROM   @dbccSinglePage
                                                          WHERE  t.ParentObject = ParentObject )t)t
                                                          ORDER BY ParentObjectOrder, ObjectOrder
                                                          FOR XML PATH('')), 1, 2, '')
                                                  FROM    @dbccSinglePage t
                                                  GROUP BY ParentObject
                                                  FOR XML PATH('')
                                    ), 1, 11, '') + ';'
                RAISERROR (@sql, 10, 1) WITH NOWAIT;
                EXEC (@sql)
            END            
        END TRY
        BEGIN CATCH
            SELECT    @sql = 'ERROR!!!' + CHAR(10) + CHAR(13) + 
                           'ErrorNumber: ' + ERROR_NUMBER() + '; ErrorMessage' + ERROR_MESSAGE() + 
                           CHAR(10) + CHAR(13) + 'FileID: ' + @FileID + '; PageID: ' + @PageID
            RAISERROR (@sql, 10, 1) WITH NOWAIT;
        END CATCH        
        DELETE @dbccSinglePage
        SELECT    @sql = 'Pages left to process: ' + CONVERT(VARCHAR(10), @pagesCount - @i) + 
                        CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13), @i = @i+1    
        RAISERROR (@sql, 10, 1) WITH NOWAIT;
        FETCH NEXT FROM dbccPageCursor INTO @FileID, @PageID, @SQLtoExec;
    END
    CLOSE dbccPageCursor; DEALLOCATE dbccPageCursor;
    EXEC ('SELECT ''' + @undeletedTableName + ''' as TableName; SELECT * FROM ' + @undeletedTableName)
END TRY
BEGIN CATCH        
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
    IF CURSOR_STATUS ('global', 'dbccPageCursor') >= 0
    BEGIN 
        CLOSE dbccPageCursor;
        DEALLOCATE dbccPageCursor;
    END
END CATCH

-- if the table was deleted we need to finish the restore page sequence
IF @tableWasTruncated = 0
BEGIN
    -- take a log tail backup and then restore it to complete page restore process 
    DECLARE @currentDate VARCHAR(30)
    SELECT @currentDate = CONVERT(VARCHAR(30), GETDATE(), 112)

    RAISERROR ('Starting Log Tail backup to c:\Temp ...', 10, 1) WITH NOWAIT;
    PRINT ('BACKUP LOG [' + @dbName + '] TO DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''')
    EXEC ('BACKUP LOG [' + @dbName + '] TO DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''')    
    RAISERROR ('Log Tail backup done.', 10, 1) WITH NOWAIT;

    RAISERROR ('Starting Log Tail restore from c:\Temp ...', 10, 1) WITH NOWAIT;
    PRINT ('RESTORE LOG [' + @dbName + '] FROM DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''')
    EXEC ('RESTORE LOG [' + @dbName + '] FROM DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''')    
    RAISERROR ('Log Tail restore done.', 10, 1) WITH NOWAIT;
END

-- The last step is manual. Insert data from our temporary table to the original deleted table

The misconception here is that you can do a single table restore properly in SQL Server. You can't. But with little experimentation you can get pretty close to it.

One way to possible remove a dependency on a backup to retrieve deleted pages is to quickly run a similar script to the upper one that gets data directly from data pages while the rows are still marked as ghost records. It could be done if we could beat the ghost record cleanup task.