Cómo recuperar vistas, procedimientos almacenados, funciones y desencadenadores

Independientemente de las precauciones tomadas para proteger su Servidor SQL, los accidentes aún pueden suceder, causando serias consecuencias como pérdida de datos y objetos. Ahora analizaremos dos posibles maneras de recuperar objetos SQL – vistas, procedimientos almacenados y desencadenadores, perdidos debido al uso accidental de una sentencia DROP.

La primera manera de recuperar objetos SQL perdidos es usar la función sin documentación fn_dblog, que lee registros de transacciones de bases de datos en línea y puede proveer información acerca de los objetos. Más precisamente, la función puede ayudar en los siguientes casos:

En el caso de que una base de datos esté en un modelo de recuperación completa – si el registro de transacciones no estaba truncado después de que el objeto haya sido borrado.

En el caso de que una base de datos esté en un modelo de recuperación simple – si el registro de transacciones está intacto (no sobre-escrito por entradas más nuevas).

Qué pasos son necesarios para recuperar un objeto borrado vía la función fn_dblog? El procedimiento es el mismo para todos los objetos. Abajo se muestra el ejemplo para procedimientos almacenados:

1. Ejecute la función fn_dblog

   SELECT
          *
     FROM sys.fn_dblog(NULL, NULL);
   

   La table resultante contiene datos de registros de transacciones completes, dividida en 129 columnas:

   

2. Para reducir los resultados a aquellos que representan a los objetos borrados, ejecute el siguiente script SQL, usando “DROPOBJ” como el valor para el nombre dela columna de la transacción:

   SELECT
          *
     FROM sys.fn_dblog(NULL, NULL)
   WHERE [transaction name] IN ('DROPOBJ');
   

   

   Como se puede ver, aún hay 129 columnas, con ninguna información legible para un humano.

   Para traducir estas columnas, el usuario necesita estar familiarizado con el formato, bits de estado, y su número total, y con algunas otras características además. Desafortunadamente, no hay documentación oficial disponible para esta función, lo que hace la tarea un poco más difícil.

   Las siguientes columnas contienen la información acerca de los objetos afectados por la transacción aplicada: RowLog Contents 0, RowLog Contents 1, RowLog Contents 2, RowLog Contents 3, and RowLog Contents 4.

   

   Los datos de las filas se almacenan en diferentes columnas, basados en el tipo de operación. Para ver la información requerida exacta usando la función fn_dblog, usted necesita saber el contenido de columna para cada tipo de transacción.

3. Finalmente, para obtener el script CREATE PROCEDURE, de manera de recrear el procedimiento borrado, el siguiente script SQL completo necesita ser ejecutado.

   SELECT
          CONVERT(varchar(max),
   			SUBSTRING([RowLog Contents 0],
   			33,
   			LEN([RowLog Contents 0]))) AS Script
     FROM fn_dblog(NULL, NULL)
   WHERE
          Operation
          =
          'LOP_DELETE_ROWS'
      AND
          Context
          =
          'LCX_MARK_AS_GHOST'
      AND
          AllocUnitName
          =
          'sys.sysobjvalues.clst'
      AND [TRANSACTION ID] IN (SELECT DISTINCT
                                      [TRANSACTION ID]
                                 FROM sys.fn_dblog(NULL, NULL)
                               WHERE
                                     Context IN ('LCX_NULL')
                                 AND Operation IN ('LOP_BEGIN_XACT')
                                 AND
                                      [Transaction Name]
                                      =
                                      'DROPOBJ'
                                 AND CONVERT(nvarchar(11), [Begin Time])
   					BETWEEN
   						'2013/07/31'
   					AND
   						'2013/08/1')
      AND
          SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0;
   GO
   

   Como se puede observer, el script encuentra todas las transacciones relacionadas, usando un marco de tiempo especificado por el usuario para reducer la búsqueda, y convertir valores hexadecimales en texto legible.

   La función fn_dblog es poderosa, pero tiene limitaciones. Por ejemplo, leer registros del registro de transacciones para cambios en estructura de objetos usualmente involucra la reconstrucción de muchos estados de tablas de sistema, mientras que sólo la porción activa del registro de transacciones en línea puede ser leída.

Como se puede ver en los ejemplos provistos anteriormente, éste método es bastante complejo. Hay otra manera de realizar la recuperación, y es muy simple. Usted puede usar ApexSQL Log, una herramienta de recuperación de SQL Server capaz de leer los datos del registro de transacciones y recuperar objetos SQL perdidos a su estado original retrotrayendo las transacciones.

Imaginemos que había un procedimiento almacenado llamado usp.LogError en la base de datos ApexSQLLogDEMO que fue borrado por una sentencia DROP PROCEDURE.

Para recuperar un procedimiento borrado usando ApexSQL Log:

1. Conectarse a la base de datos ApexSQLLogDEMO

   

2. Añadir copias de seguridad de bases de datos y/o copias de seguridad de registros de transacciones y/o registros de transacciones separados que contienen los datos requeridos para crear la cadena completa y proveer todas las transacciones hasta el punto en el tiempo cuando el procedimiento fue borrado.

   

3. Usar el filtro Time range para especificar el punto de tiempo objetivo para el proceso de recuperación (el lapso de tiempo cuando el procedimiento fue borrado). Esto permitirá reducir la búsqueda y agilizar el proceso de lectura.

   

4. Finalmente, usar el filtro Operations para reducir la búsqueda a las sentencias DROP PROCEDURE solamente. Para hacer esto, des-seleccione todas las operaciones DML y DDL excepto DROP PROCEDURE. Para otros objetos borrados (vistas, procedimientos almacenados, funciones y desencadenadores), una opción apropiada debería selecciones en su lugar.

   

5. En el paso final de la sesión de la sesión del asistente, escoja la opción para abrir los resultados en la cuadrícula de opciones

Cuando el proceso ha terminado de leer, la cuadrícula principal mostrará la transacción que fue retrotraída para recuperar el procedimiento borrado

Finalmente, para realizar la recuperación, haga clic derecho en la fila seleccionada, y elija la opción Create undo script del menú contextual. Esto abrirá el diálogo Undo script, el cual contiene el script SQL, el cual puede ser ejecutado inmediatamente o guardado para un uso posterior.