sql2005 從日志中恢復(fù)delete數(shù)據(jù)

1、SQL2005從日志中恢復(fù)從日志中恢復(fù)數(shù)據(jù)數(shù)據(jù)1.當(dāng)前日志沒(méi)有CheckPoint走，那么在系統(tǒng)函數(shù)fn_dblog里面就有某個(gè)表操作記錄，基于fn_dblog表，做了一個(gè)分析當(dāng)前日志中被刪除數(shù)據(jù)的存儲(chǔ)過(guò)程不支持的數(shù)據(jù)類型為：imagetextuniqueidentifiervarbinarybinaryrealmoneyfloatntextbitdecimalnumericsmallmoneytimestampxmlsysname其他

2、均支持FROMPOD_SN_LISTWHERESERIAL_NUMBERBETWEEN1A7372SZW4T1A73771CW4N有2690行，執(zhí)行：EXEC[GetdDataFromLog]POD_SN_LIST用2分17秒就可以找回：2.相關(guān)原理圖片為：ALTERPROCEDURE[dbo].[GetdDataFromLog]AddtheparametersfthestedprocedurehereA.OBJECT_ID=OBJEC

3、T_ID(@TABLENAME)DERBYA.column_idTOP1@FirstColumnName=ColumnNameFROM#TEMP_HEADERWHILEEXISTS(FROM#TEMP_HEADER)BEGINTOP1@ColumnName=[ColumnName]@name=[name]@max_length=max_lengthFROM#TEMP_HEADER@iSQL=@iSQLCASE@nameWHENvarbi

4、naryTHEN@ColumnName@name(@max_length)WHENbinaryTHEN@ColumnName@name(@max_length)WHENTHEN@ColumnName@name(@max_length)WHENnvarTHEN@ColumnName@name(@max_length)WHENvarTHEN@ColumnName@name(@max_length)WHENnTHEN@ColumnName@n

5、ame(@max_length)WHENsql_variantTHEN@ColumnName@name(@max_length)ELSE@ColumnName@nameENDTOP(1)FROM#TEMP_HEADEREND@iSQL=SUBSTRING(@iSQL1LEN(@iSQL)1)@iSQL=CREATETABLETEMP_TABLE_FReste(@iSQL)EXEC(@iSQL)#TEMP_HEADER[RowLogCon

6、tents0]ASr0into#tempfrom::fn_dblog(nullnull)whereallocunitname=@FK_nameoperationin(LOP__ROWS)WHILEEXISTS(from#temp)BEGINTOP1@Data=r0from#tempintoTEMP_TABLE_FResteEXECdbo.ParsedRowsFromTable_1@Data@TableNamePRINT@DataPRIN