SQL Server: Cómo saber cuándo se han borrado datos y recuperarlos usando el registro de transacciones

En el post anterior explicaba como examinar el registro de transacciones de una base de datos para ver poder ver el detalle de los cambios realizados en la misma, ya sean consultas DML como DDL. En este post se explica como usar la misma herramienta en otro caso práctico para poder detectar en que momento se han borrado unos datos y como recuperarlos haciendo una restauración point-in_time de la base de datos para poder copiarlos de nuevo.

Recuperando lo visto en este post, en MS SQL Server podemos consultar el contenido del log de transacciones usando la función no documentada fn_dblog. Esta opción, podemos ejecutarla con los valores por defecto (NULL, NULL) para que nos devuelva TODO el contenido del log o podemos filtrar por LSN (LSN inicial, LSN final). Eso sí, como requisito, es imprescindible que la base de datos esté usando la opción de seguimiento de cambios "completa" (full recovery model):

SELECT [Current LSN],
  [Operation],
  [Transaction Name],
  [Transaction ID],
  [Transaction SID],
  [SPID],
  [Begin Time]
FROM fn_dblog(null,null)

Antes de empezar, para el ejemplo crearemos primero una base de datos de prueba:

-- Script creación de la base de datos

USE [master];
GO

CREATE DATABASE [Testdb];
GO

-- Creación de una tabla

USE [Testdb];
GO

CREATE TABLE [Articulos] (
[IdArticulo] INT IDENTITY,
[FechaCreacion] DATETIME DEFAULT GETDATE (),
[Codigo] CHAR (5) NOT NULL,
[Descripcion] CHAR(20) NULL);

-- Carga de datos

INSERT INTO [Articulos] (Codigo, Descripcion) VALUES ('00S00','Scott Spark MTB')
GO
INSERT INTO [Articulos] (Codigo, Descripcion) VALUES ('00G10','Scott Genius 10 MTB')
GO
INSERT INTO [Articulos] (Codigo, Descripcion) VALUES ('00G20','Scott Genius 20 MTB')
GO
INSERT INTO [Articulos] (Codigo, Descripcion) VALUES ('00G30','Scott Genius 30 MTB')
GO

-- A continuació haremos un DELETE y un nuevo insert.

USE [Testdb];
GO

DELETE FROM [Articulos]
GO

INSERT INTO [Articulos] (Codigo, Descripcion) VALUES ('00G40','Scott Genius 40MTB')
GO

El caso práctico es que alguien ha borrado el contenido de una tabla y no sabemos cuando lo ha hecho. Por suerte, sabemos que tenemos activado el seguimiento completo para la base de datos y una copia de seguridad completa de la madrugada anterior. Para poder recuperar los datos que hemos perdido, investigaremos cuando se han borrado los datos en el registro de transacciones, recuperaremos el último LSN, haremos un backup del registro de transacciones en su estado actual y haremos una restauración "point-in-time" en otra base de datos para conseguir los datos como estaban justo antes de ser eliminados. Pasos a seguir:

• Buscar quién y cuando han borrado los datos: Primero ejecutamos fn_dblog para consultar en todo el registro para buscar operaciones del tipo LOP_DELETE_ROWS. También podríamos filtrar por AllocUnitName y buscar el nombre completo de la tabla (dbo.Articulos).

USE [Testdb];
GO

SELECT
  [Current LSN],
  [Transaction ID],
  [Operation],
  [Context],
  [AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'

Mirando la imagen vemos el Id de transacción que ha ejecutado el DELETE. Cuidado porque aunque un bloque SQL con más de una SQL esté en una misma transacción, el LSN es totalmente independiente en cada una de ellas. Por ello, ahora buscaremos el inicio de la transacción (LOP_BEGIN_XACT) para poder coger su LSN y ver de paso cuando se ha eliminado:

SELECT
  [Current LSN],
  [Transaction ID],
  [Operation],
  [Context],
  [AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:000002ee'
  AND [Operation] = 'LOP_BEGIN_XACT'

• Calcular el parámetro para indicar el momento al que restaurar. Ahora que ya tenemos el momento del delete y su LSN, ya podemos hacer la restauración de la copia de la base de datos. Para ello usaremos la opción STOPBEFOREMARK del comando RESTORE. La única pega es que debemos convertir el LSN a formato decimal. Eso lo hacemos partiendo en tres el LSN, convirtiendo cada miembro a decimal y formateándolo:

1. El primer miembro (00000021) se pasa a decimal y se formatea sin ceros a la izquierda, quedando como 33.
2. El segundo miembro (0000006c) se pasa a decimal y se formatea con 0 a la izquierda hasta que tenga longitud 10, quedando como 0000000108.
3. El tercer miembro (0003) se pasa a decimal y se formatea con 0 a la izquierda hasta que tenga longitud 5, quedando como 00003.
4. Unimos las tres cadenas, nos queda 33000000010800003.

• Hacer el backup del log de transacciones actual. Solo necesitamos hacer el backup del log transacciones si no se ha hecho ninguno anterior al momento presente y posterior al DELETE.

• Restaurar al momento clave en otra base de datos. Restauramos primero el Backup total con NORECOVERY y luego el del log hasta el momento anterior al DELETE (especificando el LSN en formato decimal):

--Restore del backup completo.

RESTORE DATABASE TestDb_Copia
FROM DISK = 'C:\TestDb_Copia_full.bak'
WITH MOVE 'TestDb' TO 'C:\TestDb.mdf',
  MOVE 'TestDb_log' TO 'C:\TestDb_log.ldf',
REPLACE, NORECOVERY;
GO

--Restore del log de transacciones.

RESTORE LOG TestDb_Copia
FROM DISK = N'C:\TestDb.trn'
WITH STOPBEFOREMARK = 'lsn:33000000010800003'

• Finalmente comprobar y copiar lo que nos haga falta. Comprobamos que datos tiene la restauración de la base de datos temporal y copiamos los datos que nos haga falta de la temporal a la original.

Para poder aplicar todo este mismo proceso para cuando alguien ha BORRADO UNA TABLA lo que tenemos que hacer es buscar el LSN filtrando [Transaction Name]='DROPOBJ' en lugar de Operation = 'LOP_DELETE_ROWS'. El resto es lo mismo (backup log, restore backup, restore log stopbeforemark).

En conclusión...

... esta bien saber que el log de transacciones está ahí por algo más. Como en el otro post, saber que podemos explorarlo y buscar qué pasó en momentos clave de nuestra base de datos o cuando paso algo concreto. Esta bien poder recuperar la tabla pero también tenemos que pensar en cada caso si algo así nos vale porque puede ser que necesitemos hacer "matching" de los datos. En el caso de tablas del ERP pueden intervenir secuencias, tablas relacionadas etc etc. Puede que recuperar la tabla antes de ser borrada no valga para volcarla directamente pero al menos sea útil para consulta y volver a generar los datos a mano o desde el ERP. Igualmente está bien saber que tenemos esta posibilidad y probarlo en un entorno de test para saber como funciona el día que nos haga falta.

Libros de SQL Server

¿Quieres profundizar más en Transact-SQL o en administración de bases de datos SQL? Puedes hacerlo consultando alguno de estos libros de SQL Server.

Mejor revisa la lista completa de los últimos libros de SQL Server publicados en Amazon según lo que te interese aprender, pero estos son los que a mi me parecen más interesantes, teniendo en cuenta precio y temática:

• eBooks de SQL Server gratuítos para la versión Kindle, o muy baratos (menos de 4€):

• Libros recomendados de SQL Server