Leer un registro de transacciones de SQL Server

Los registros de transacciones de SQL Server contienen entradas que describen los cambios hechos a la base de datos. Ellos guardan suficiente información para recuperar la base de datos a un punto de tiempo específico, para reproducir o deshacer un cambio. Pero, ¿cómo ver qué hay en ellos, encontrar una transacción específica, ver qué ha pasado y revertir los cambios como recuperar accidentalmente entradas eliminadas?

Ver qué está guardado en un registro de transacciones en línea, o en una copia de seguridad de un registro de transacciones no es tan simple.

Abrir archivos LDF y TRN en un editor binario muestra entradas ininteligibles, así que estas entradas no pueden ser leídas directamente. Por ejemplo, este es un extracto de un archivo LDF:

Usando fn_dblog

fn_dblog es una función de SQL Server no documentada que lee la porción activa de un registro de transacciones en línea. Veamos los pasos que debe dar y la manera en que los resultados son presentados.

1. Ejecute la función fn_dblog

   Select * FROM sys.fn_dblog(NULL,NULL)

   

   Como la función en sí retorna 129 columnas, retornar sólo las específicas es recomendado así como reducir los resultados a un tipo de transacción específico, si aplica.

2. Del conjunto de resultados retornado por fn_dblog, encontrar las transacciones que desea ver.

   Para ver transacciones para filas insertadas, ejecute:

   SELECT [Current LSN], 
          Operation, 
          Context, 
          [Transaction ID], 
          [Begin time]
          FROM sys.fn_dblog
      (NULL, NULL)
     WHERE operation IN
      ('LOP_INSERT_ROWS');

   

   Para ver las transacciones para entradas borradas, ejecute:

   SELECT [begin time], 
          [rowlog contents 1], 
          [Transaction Name], 
          Operation
     FROM sys.fn_dblog
      (NULL, NULL)
     WHERE operation IN
      ('LOP_DELETE_ROWS');

   

3. Encuentre la columna que guarda el valor insertado o borrado – revise RowLog Contents 0 , RowLog Contents 1 , RowLog Contents 2 , RowLog Contents 3 , RowLog Contents 4, Description y Log Record.

   Los datos de las filas están guardados en diferentes columnas para diferentes tipos de operaciones. Para poder ver exactamente lo que necesita usando la función fn_dblog, usted debe conocer el contenido de la columna para cada tipo de transacción. Como no hay documentación oficial para esta función, eso no es tan fácil.

   Las filas insertadas y borradas son mostradas en valores hexadecimales. Para poder dividirlas en campos usted necesita conocer el formato que se usa, entender los bits de estado, conocer el número total de columnas y más cosas.

4. Convertir los datos binarios en datos de tabla tomando en cuenta el tipo de datos de la columna de la tabla. Note que los mecanismos de conversión son diferentes para los distintos tipos de datos.

fn_dblog es una función grandiosa, poderosa y gratis, pero tiene unas pocas limitaciones – leer entradas de registro para cambios de estructura de objetos es complejo, dado que usualmente involucra reconstruir el estado de muchas tablas de sistema, sólo la porción activa de un registro de transacciones en línea es leído, y no hay reconstrucción UPDATE/BLOB.

Como la operación UPDATE es mínimamente registrada en los registros de transacciones, sin valores antiguos o nuevos, sólo lo que cambió para la entrada (por ejemplo, SQL Server registró que “G” fue cambiado a “F”, cuando realmente el valor “GLOAT” fue cambiado a “FLOAT”), usted tiene que reconstruir manualmente el estado precio a la actualización que involucra reconstruir todos los estados intermedios entre la inserción original de la fila en la página y la actualización que está tratando de reconstruir.

Cuando se borran BLOBs, el BLOB borrado no es insertado en el registro de transacciones, así que sólo leer la entrada del registro para DELETE BLOB no puede recuperar el BLOB. Sólo si hay una entrada INSERT en el registro para el BLOB borrado, y usted logra emparejar ambos, usted podrá recuperar un BLOB borrado desde un registro de transacciones usando fn_dblog.

Usando fn_dump_dblog

Para leer un registro de transacciones nativo o copias de seguridad comprimidas nativamente, incluso sin la base de datos en línea, use la función fn_dump_dblog. De nuevo, esta función no está documentada.

1. Ejecute la función fn_dump_dblog en una copia de seguridad de registro de transacciones específica. Note que debe especificar todos los 63 parámetros.

   SELECT *
   FROM fn_dump_dblog
   (NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
   DEFAULT);

   

   Al igual que con fn_dblog, 129 columnas son retornadas, así que retornar solamente las específicas es recomendado:

   SELECT [Current LSN], 
          Operation, 
          Context, 
          [Transaction ID], 
        [transaction name],
          Description
   FROM fn_dump_dblog
   (NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
   DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
   DEFAULT);

   De nuevo, usted debe descifrar valores hexadecimales para obtener la información que está buscando.

   

   Y está de vuelta en el cuadrado uno como con fn_dblog – usted necesita reconstruir todos los valores de filas manualmente, necesita reconstruir cadenas de estados enteras para operaciones UPDATE y valores BLOB, y así sucesivamente.

   Si usted no desea realmente extractar transacciones de la copia de seguridad del registro de transacciones, pero desea restaurar la base de datos a un punto de tiempo antes de la ocurrencia de una operación específica, usted puede:

2. Determinar el LSN para esta transacción.

3. Convertir el LSN al formato usado in la cláusula WITH STOPBEFOREMARK = ´<mark_name>´, por ejemplo: 00000070:00000011:0001 debería ser transformado en 112000000001700001

4. Restaurar la cadena de la copia de seguridad completa del registro hasta que alcance el tiempo cuando las transacciones ocurrieron. Use la cláusula WITH STOPBEFOREMARK = ‘<mark_name>’ para especificar la transacción de referencia LSN.

   RESTORE LOG AdventureWorks2012
   FROM
       DISK = N'E:\ApexSQL\backups\AW2012_05232013.trn'
   WITH
       STOPBEFOREMARK = 'lsn:112000000001700001',
       NORECOVERY;

Usando DBCC PAGE

Otro comando útil pero también sin documentación es DBCC PAGE. Úselo para leer el contenido de archivos de bases de datos en línea –MDF y LDF. La sintaxis es:

DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Para vaciar la primera página en el archivo de registro de transacciones de la base de datos AdventureWorks2012, use:

SELECT FILE_ID ('AdventureWorks2012_Log') AS 'File ID' 
-- to determine Log file ID = 2
DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Obtendrá:

DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

Por defecto, la salida no se muestra. Si usted desea una salida en SQL Server Management Studio, active la bandera de rastreo 3604 primero.

DBCC TRACEON (3604, -1)

Luego reejecute:

DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Usted obtendrá un puñado de errores y malos encabezados y puede ignorar todo eso. AL final obtendrá una gloriosa salida hexadecimal de archivo LDF en línea:

Esta no es la presentación más amigable de los datos de su base de datos y básicamente no es diferente que verla en un editor hexadecimal (sólo más cómodo), aunque al menos tiene acceso a sus datos en línea.

Usando ApexSQL Log

ApexSQL Log es un lector de registros transaccionales de SQL Server que lee registros de transacciones en línea, registros de transacciones sueltos y copias de seguridad de registros, tanto nativos como comprimidos nativamente. Cuando sea necesario, también leerá copias de seguridad de bases de datos para obtener suficientes datos para una reconstrucción exitosa. Puede reproducir cambios en datos y objetos que hayan afectado una base de datos, incluyendo aquellos que hayan ocurrido antes de que fuera instalado. A diferencia de las funciones sin documentación ni soporte descritas anteriormente, usted obtendrá información perfectamente entendible acerca de lo que ocurrió, en qué objeto, y cuáles eran los valores antiguos y nuevos.

1. Iniciar ApexSQL Log

2. Conectarse a la base de datos para la cual usted desea leer los registros de transacciones.

   

3. En el paso Select SQL logs to analyze, seleccione los registros que desea leer. Asegúrese de que forman una cadena completa.

   

4. Para añadir copias de seguridad de registros de transacciones y archivos LDF sueltos, use el botón Add.

5. Use las opciones de Filter setup para reducir las transacciones leídas usando el rango de tiempo, tipo de operación, tabla y otros filtros disponibles.

   

6. Haga clic en Open

   Unos resultados muy completos serán mostrados en la cuadrícula de ApexSQL Log.

   Usted podrá ver el tiempo cuando la operación comenzó y finalizó, el tipo de operación, el esquema y nombre de objeto del objeto afectado, el nombre del usuario que ejecutó la operación, la computadora y aplicación usadas para ejecutar la operación. Para UPDATEs, usted verá los valores antiguo y nuevo de los campos actualizados.

   

Para evitar valores hexadecimales, funciones sin documentación, contenido de columnas poco claro, consultas largas, pasos de acción complejos, reconstrucciones UPDATE y BLOB incompletas cuando se lee registros de transacciones SQL Server, use ApexSQL Log. La herramienta leerá registros de transacciones por usted y presentará los resultados en “español simple”. Aparte de eso, los scripts para deshacer y rehacer están a un clic de distancia.

Traductor: Daniel Calbimonte