《恢復(fù)SQL Server被誤刪除的數(shù)據(jù)（再擴(kuò)展）》

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

曾經(jīng)想實(shí)現(xiàn)Log Explorer for SQL Server的功能，利用ldf里面的日志來(lái)還原誤刪除的數(shù)據(jù)

這里有一篇文章做到了，不過(guò)似乎不是所有的數(shù)據(jù)類型都支持

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

在我使用SQLSERVER的這些年里面，大部分人都會(huì)問(wèn)我一個(gè)問(wèn)題：“能不能恢復(fù)被刪除的數(shù)據(jù)？？”

現(xiàn)在，從SQLSERVER2005 或以上版本能很容易能夠恢復(fù)被刪除的數(shù)據(jù)

（注意：這個(gè)腳本能恢復(fù)下面的數(shù)據(jù)類型的數(shù)據(jù) 而且兼容CS 排序規(guī)則）

• 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

讓我來(lái)用demo來(lái)解釋一下我是怎么做到的

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

現(xiàn)在你需要?jiǎng)?chuàng)建一個(gè)存儲(chǔ)過(guò)程來(lái)恢復(fù)你的數(shù)據(jù)

-- 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