使用 fn_dblog手动恢复误操作的 update（单列数值型数据恢复）

事由

通常，我们在操作数据库的时候，很对 update 指令指定一个更新范围，比如指定更新某个ID的数据，指定某个类型的数据，符合某个条件的数据

而实际操作中，我们偶尔会出现一些误操作，比如同时编写多个指令时，其中某个步骤需要更新一下数据库的数据，这个时候，Hmmm，真的是偶尔，偶尔，偶尔会忘记加上限定条件，然后，一个 update 就执行了下去。。。

然后，马上就能发现是误操作了，但是，数据已经被更新掉了，这个时候，只能通过还原数据库来找回原来的数据了。

CSDN 文盲老顾的博客，https://blog.csdn.net/superwfei
老顾的个人社区，https://bbs.csdn.net/forums/bfba6c5031e64c13aa7c60eebe858a5f?category=10003&typeId=3364713

fn_dblog

而事实上，我们这种立刻发现误操作的情况下，未必需要用到还原数据库来恢复数据。

在 mssql 数据库中，有一个系统表值函数 fn_dblog，可以用来拉取最近的操作日志哦。

使用方式很简单，直接使用 select 查询即可

select * from fn_dblog(null,null)

当然，直接这么运行，基本上都会出现超时错误，因为所有的指令都会被查询出来，但是还有实时的指令进入到查询队列中。

所以，我们需要对这个查询进行一个简单的条件限定。

比如，我们误操作的表是 test_db_tableA，那么我们就可以将这个表名作为限制条件进行查询。

select * from fn_dblog(null,null) where allocunitname like '%test_db_tableA%'

这个时候，会列出最近的所有对这个表进行操作的数据比对，其中，对 operation 进行一下限制，可以只查询 update 造成影响的指令，也就是限定其的值为 lop_modify_row

select * from fn_dblog(null,null) where allocunitname like '%test_db_tableA%' and operation='lop_modiify_row'

然后，就会列出来很多很多数据了，尤其是这个表值函数，列出来的字段是真心多啊。

我们限制一下其列出的内容

select [current LSN],Operation,Context,[Transaction ID],AllocUnitName,[RowLog Contents 0],[RowLog Contents 1],[RowLog Contents 2],[RowLog Contents 3],[RowLog Contents 4] from fn_dblog(null,null) where allocunitname like '%test_db_tableA%' and operation='lop_modiify_row'

其中 current LSN 是唯一数据的列，Transaction ID 列应该是事务ID，但是，老顾暂时没能和sys.dm_exec_requests系统视图里的 TransactionID对应上，毕竟数据结构不太一样，不知道中间的转换规则，这个问题以后再讨论，重要的数据，是 RowLog Contents 相关的5个列，以老顾的误操作举例，查询后得出下列数据

current LSN             Operation                       Context                         Transaction ID AllocUnitName                                                                                                                                                                                                                                                    RowLog Contents 0                                                                                                                                                                                                                                                  RowLog Contents 1                                                                                                                                                                                                                                                  RowLog Contents 2                                                                                                                                                                                                                                                  RowLog Contents 3                                                                                                                                                                                                                                                  RowLog Contents 4
----------------------- ------------------------------- ------------------------------- -------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
00000509:00000265:0182  LOP_MODIFY_ROW                  LCX_PFS                         0000:0022595c  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x04                                                                                                                                                                                                                                                               0x60                                                                                                                                                                                                                                                               0x                                                                                                                                                                                                                                                                 0x                                                                                                                                                                                                                                                                 0x
00000509:00000265:0186  LOP_MODIFY_ROW                  LCX_IAM                         0000:0022595c  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x000000000000                                                                                                                                                                                                                                                     0xEE1000000100                                                                                                                                                                                                                                                     0x                                                                                                                                                                                                                                                                 0x                                                                                                                                                                                                                                                                 0x
00000509:000002dd:0029  LOP_MODIFY_ROW                  LCX_PFS                         0000:0022595e  dbo.pub_users_eduinfo.name                                                                                                                                                                                                                                       0x04                                                                                                                                                                                                                                                               0x60                                                                                                                                                                                                                                                               0x                                                                                                                                                                                                                                                                 0x                                                                                                                                                                                                                                                                 0x
00000509:000002dd:002e  LOP_MODIFY_ROW                  LCX_IAM                         0000:0022595e  dbo.pub_users_eduinfo.name                                                                                                                                                                                                                                       0x000000000000                                                                                                                                                                                                                                                     0xF01000000100                                                                                                                                                                                                                                                     0x                                                                                                                                                                                                                                                                 0x                                                                                                                                                                                                                                                                 0x
00000509:00000370:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225983  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x04                                                                                                                                                                                                                                                               0x05                                                                                                                                                                                                                                                               0x1640000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A5A42F31E4991                                                                                                                                                                                                             0x
00000509:00000372:0004  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225984  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x04                                                                                                                                                                                                                                                               0x05                                                                                                                                                                                                                                                               0x163F000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A6835335383DB                                                                                                                                                                                                             0x
00000509:00000374:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225985  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x04                                                                                                                                                                                                                                                               0x05                                                                                                                                                                                                                                                               0x163E000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A917958C466D7                                                                                                                                                                                                             0x
00000509:00000376:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225986  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x00                                                                                                                                                                                                                                                               0x01                                                                                                                                                                                                                                                               0x163D000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A714D764BA26B                                                                                                                                                                                                             0x
00000509:00000378:0006  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xED                                                                                                                                                                                                                                                               0xEF                                                                                                                                                                                                                                                               0x162F000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AE093C47DC954                                                                                                                                                                                                             0x
00000509:00000378:000a  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xF0                                                                                                                                                                                                                                                               0xEF                                                                                                                                                                                                                                                               0x1630000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AE13336D6BF3D                                                                                                                                                                                                             0x
00000509:00000378:000d  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x4B04                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x1632000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AF84B73CE9E8D                                                                                                                                                                                                             0x
00000509:00000378:0010  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x6A04                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x1633000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A010718597B81                                                                                                                                                                                                             0x
00000509:00000378:0013  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x7D04                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x1634000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A39222FD017F4                                                                                                                                                                                                             0x
00000509:00000378:0016  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x6808                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x1635000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AC06E4447F2F8                                                                                                                                                                                                             0x
00000509:00000378:0019  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xC308                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x1636000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A205A6AC83644                                                                                                                                                                                                             0x
00000509:00000378:001c  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xDF08                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x1637000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AD916015FD348                                                                                                                                                                                                             0x
00000509:00000378:001f  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xDF08                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x1638000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A501004DAEFAE                                                                                                                                                                                                             0x
0000050a:00000010:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xA608                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x1639000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AA95C6F4D0AA2                                                                                                                                                                                                             0x
0000050a:00000010:0005  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xE908                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x163A000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A496841C2CE1E                                                                                                                                                                                                             0x
0000050a:00000010:0008  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xED08                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x163B000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AB0242A552B12                                                                                                                                                                                                             0x
0000050a:00000010:000b  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225987  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0x4404                                                                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x163C000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A88011DDC4767                                                                                                                                                                                                             0x
0000050a:0000001a:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:0022598a  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF                                                                                                                                                                                                                                                               0xF0                                                                                                                                                                                                                                                               0x1630000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AE13336D6BF3D                                                                                                                                                                                                             0x
0000050a:0000001c:0005  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:0022598b  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF                                                                                                                                                                                                                                                               0xED                                                                                                                                                                                                                                                               0x162F000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AE093C47DC954                                                                                                                                                                                                             0x
0000050a:0000002c:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225997  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x6A04                                                                                                                                                                                                                                                             0x1633000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A010718597B81                                                                                                                                                                                                             0x
0000050a:0000002e:0005  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225998  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x7D04                                                                                                                                                                                                                                                             0x1634000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A39222FD017F4                                                                                                                                                                                                             0x
0000050a:00000030:0005  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:00225999  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x6808                                                                                                                                                                                                                                                             0x1635000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AC06E4447F2F8                                                                                                                                                                                                             0x
0000050a:00000032:0005  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:0022599a  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0xC308                                                                                                                                                                                                                                                             0x1636000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A205A6AC83644                                                                                                                                                                                                             0x
0000050a:00000035:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:0022599b  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0xE908                                                                                                                                                                                                                                                             0x163A000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A496841C2CE1E                                                                                                                                                                                                             0x
0000050a:00000056:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:002259ab  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x4B04                                                                                                                                                                                                                                                             0x1632000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AF84B73CE9E8D                                                                                                                                                                                                             0x
0000050a:00000058:0004  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:002259ac  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0xDF08                                                                                                                                                                                                                                                             0x1637000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AD916015FD348                                                                                                                                                                                                             0x
0000050a:0000005a:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:002259ad  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0xDF08                                                                                                                                                                                                                                                             0x1638000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A501004DAEFAE                                                                                                                                                                                                             0x
0000050a:0000005f:0002  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:002259b0  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0xA608                                                                                                                                                                                                                                                             0x1639000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AA95C6F4D0AA2                                                                                                                                                                                                             0x
0000050a:00000061:0005  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:002259b1  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0xED08                                                                                                                                                                                                                                                             0x163B000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000AB0242A552B12                                                                                                                                                                                                             0x
0000050a:00000063:0005  LOP_MODIFY_ROW                  LCX_CLUSTERED                   0000:002259b2  dbo.pub_users_eduinfo.PK__pub_user__3213E83F2E8A0CFA                                                                                                                                                                                                             0xEF03                                                                                                                                                                                                                                                             0x4404                                                                                                                                                                                                                                                             0x163C000000010000                                                                                                                                                                                                                                                 0x0101000C000019FC0C070000010200040204000A88011DDC4767                                                                                                                                                                                                             0x(34 行受影响)

fn_dblog 列数据意义

现在，可以稍微解释一下这几个列的数据的意义了。

老顾在这里的误操作，是最简单的误操作，即误将某列数值型的数据，全部更新成一个值了。

update tableA set u_id=1007

也就是这个数据恢复是最简单的，RowLog Contents 0 列，是更新前的数据，RowLog Contents 1 是更新后的数据。

我们可以看到，上边的查询结果里，有很多 RowLog Contents 1 的值是 0xEF03 的，这个就是刚才 update 误操作差生的操作记录，而 RowLog Contents 0，就是更新前的数据。

需要注意，SqlServer 里，二进制数据，是从右到左书写的，也就是 0xEF03，写成16进制的内容，应该是 0x03EF，换算成10进制就是1007了。

那么，有了更新前的数据，只需要再找到主键的内容，我们就可以直接把数据进行恢复了。

好在，fn_dblog 里有相关内容，可以看到 RowLog Content 2 这一列。0x1633000000010000 这个值，前边的 0x16 代表的意义暂时不详，但是 33 就是指向的主键 ID，也就是主键值为 51 的行。推测这里是可以用 int 数值类型，如果是其他类型，应该不是 0x16 开头了。而 0x16 之后，还是按照SqlServer 的二进制从右到左运算其十进制数据即可。哦，最后的 010000 部分意义不明，暂时不用管他了。

需要注意的是，如果你更新整个表，部分行的数据不产生变化，是不会出现在 fn_dblog 这个查询结果里。

所以，放心大胆的直接用这个查询结果逆推原始数据，修复误操作产生的结果吧。