《恢復SQL Server被誤刪除的數據（再擴展）》

地址：http://www.cnblogs.com/lyhabc/p/4620764.html

曾經想實現Log Explorer for SQL Server的功能，利用ldf里面的日志來還原誤刪除的數據

這里有一篇文章做到了，不過似乎不是所有的數據類型都支持

以下為譯文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

在我使用SQLSERVER的這些年里面，大部分人都會問我一個問題：“能不能恢復被刪除的數據？？”

現在，從SQLSERVER2005 或以上版本能很容易能夠恢復被刪除的數據

（注意：這個腳本能恢復下面的數據類型的數據 而且兼容CS 排序規則）

• image
• text
• uniqueidentifier
• tinyint
• smallint
• int
• smalldatetime
• real
• money
• datetime
• float
• sql_variant
• ntext
• bit
• decimal
• numeric
• smallmoney
• bigint
• varbinary
• varchar
• binary
• char
• timestamp
• nvarchar
• nchar
• xml
• sysname

讓我來用demo來解釋一下我是怎么做到的

USE masterGO--創建數據庫CREATE DATABASE testGOUSE [test]GO--創建表CREATE TABLE [dbo].[aa](    [id] [int] IDENTITY(1,1) NOT NULL,    [NAME] [nvarchar](200) NULL) ON [PRIMARY]GO--插入測試數據INSERT [dbo].[aa]        ( [NAME] )SELECT '你好'GO--刪除數據Delete from aaGo--驗證數據是否已經刪除Select * from aaGo

現在你需要創建一個存儲過程來恢復你的數據

-- Script Name: Recover_Deleted_Data_Proc-- Script Type : Recovery Procedure -- Develop By: Muhammad Imran-- Date Created: 15 Oct 2011-- Modify Date: 22 Aug 2012-- Version    : 3.1-- Notes : Included BLOB data types for recovery.& Compatibile with Default , CS collation , Arabic_CI_AS. CREATE PROCEDURE Recover_Deleted_Data_Proc    @Database_Name NVARCHAR(MAX) ,    @SchemaName_n_TableName NVARCHAR(MAX) ,    @Date_From DATETIME = '1900/01/01' ,    @Date_To DATETIME = '9999/12/31'AS    DECLARE @RowLogContents VARBINARY(8000)    DECLARE @TransactionID NVARCHAR(MAX)    DECLARE @AllocUnitID BIGINT    DECLARE @AllocUnitName NVARCHAR(MAX)    DECLARE @SQL NVARCHAR(MAX)    DECLARE @Compatibility_Level INT      SELECT  @Compatibility_Level = dtb.compatibility_level    FROM    master.sys.databases AS dtb    WHERE   dtb.name = @Database_Name     IF ISNULL(@Compatibility_Level, 0) <= 80        BEGIN            RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)            RETURN        END     IF ( SELECT COUNT(*)         FROM   INFORMATION_SCHEMA.TABLES         WHERE  [TABLE_SCHEMA] + '.' + [TABLE_NAME] = @SchemaName_n_TableName       ) = 0        BEGIN            RAISERROR('Could not found the table in the defined database',16,1)            RETURN        END     DECLARE @bitTable TABLE        (          [ID] INT ,          [Bitvalue] INT        )--Create table to set the bit position of one byte.     INSERT  INTO @bitTable            SELECT  0 ,                    2            UNION ALL            SELECT  1 ,                    2            UNION ALL            SELECT  2 ,                    4            UNION ALL            SELECT  3 ,                    8            UNION ALL            SELECT  4 ,                    16            UNION ALL            SELECT  5 ,                    32            UNION ALL            SELECT  6 ,                    64            UNION ALL            SELECT  7 ,                    128 --Create table to collect the row data.    DECLARE @DeletedRecords TABLE        (          [Row ID] INT IDENTITY(1, 1) ,          [RowLogContents] VARBINARY(8000) ,          [AllocUnitID] BIGINT ,          [Transaction ID] NVARCHAR(MAX) ,          [FixedLengthData] SMALLINT ,          [TotalNoOfCols] SMALLINT ,          [NullBitMapLength] SMALLINT ,          [NullBytes] VARBINARY(8000) ,          [TotalNoofVarCols] SMALLINT ,          [ColumnOffsetArray] VARBINARY(8000) ,          [VarColumnStart] SMALLINT ,          [Slot ID] INT ,          [NullBitMap] VARCHAR(MAX)        )--Create a common table expression to get all the row data plus how many bytes we have for each row.;    WITH    RowData              AS ( SELECT   [RowLog Contents 0] AS [RowLogContents] ,                            [AllocUnitID] AS [AllocUnitID] ,                            [Transaction ID] AS [Transaction ID]   --[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)                            ,                            CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) AS [FixedLengthData]  --@FixedLengthData -- [TotalnoOfCols] =  Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)                            ,                            CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) AS [TotalNoOfCols] --[NullBitMapLength]=ceiling([Total No of Columns] /8.0)                            ,                            CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0)) AS [NullBitMapLength]  --[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )                            ,                            SUBSTRING([RowLog Contents 0],                                      CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 3,                                      CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0))) AS [NullBytes] --[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )                            ,                            ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN (                                        0x10, 0x30, 0x70 )                                   THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 3                                                              + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0)), 2))))                                   ELSE NULL                              END ) AS [TotalNoofVarCols]  --[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )                            ,                            ( CASE WHEN SUBSTRING([RowLog Cont