¡Hola, chicos!
¿Todo muy bien?
En este artículo compartiré contigo una solución que puede salvar vidas en casos desesperados cuando alguien elimina un objeto de la base de datos (Vista, Procedimiento almacenado, Vista o Función), no tienes una rutina de control de versiones para los objetos de la base de datos y necesitas intentar recuperar ese objeto rápidamente, sin tener que volver a una copia de seguridad (lo que puede llevar una cierta cantidad de tiempo, dependiendo del tamaño de la base de datos).
Si quieres evitar este tipo de situaciones (donde no siempre es posible recuperar el código del objeto), te sugiero implementar el control de versiones para los objetos de la base de datos, disponible en el post Cómo crear un activador de auditoría para registrar la manipulación de objetos en SQL Server.
Si sólo necesita saber si se eliminó un objeto específico y quién lo eliminó, puede utilizar el seguimiento estándar de SQL Server (que está habilitado de forma predeterminada) para obtener esta información. Descubre más en la publicación Monitoreo de operaciones DDL y DCL usando fn_trace_gettable de SQL Server.
Cuando no tienes ningún control de versión sobre los objetos de tu base de datos (Ver, Procedimiento almacenado, Función y Trigger) y alguien elimina accidentalmente este objeto, es posible que se haya creado un gran problema para el DBA, especialmente cuando este objeto no existe o no está actualizado en otros entornos (DEV, QA, etc.), ya que será necesario cargar una base de datos paralela con la última copia de seguridad para recuperar el código fuente de este objeto, lo que puede requerir mucho tiempo y esfuerzo.
El propósito de este artículo es brindar algunas alternativas que pueden ayudarlo a recuperar el origen de estos objetos, incluso sin control de versiones (lo cual no es lo ideal) y sin tener que volver a una copia de seguridad.
Entorno de prueba
Para las pruebas de este artículo, crearé una nueva base de datos, configuraré el modelo de recuperación en COMPLETO y crearé algunos objetos. Después de eso, usaré estos objetos e intentaremos recuperarlos nuevamente.
Script utilizado para crear el banco:
CREATE DATABASE [Teste]
ON PRIMARY
(
NAME = N'Teste',
FILENAME = N'C:\Dados\Teste.mdf',
SIZE = 100MB,
FILEGROWTH = 500MB
)
LOG ON
(
NAME = N'Teste_log',
FILENAME = N'C:\Dados\Teste_log.ldf',
SIZE = 100MB,
FILEGROWTH = 500MB
);
GO
ALTER DATABASE [Teste] SET RECOVERY FULL
GO
Script utilizado para crear objetos:
USE [Teste]
GO
CREATE FUNCTION fncRecupera_Objeto_ID(@Nome sysname)
RETURNS INT
AS BEGIN
RETURN (SELECT TOP(1) [object_id] FROM sys.objects WHERE [name] = @Nome)
END
GO
SELECT dbo.fncRecupera_Objeto_ID('sysallocunits')
SELECT dbo.fncRecupera_Objeto_ID('fncRecupera_Objeto_ID')
GO
CREATE PROCEDURE stpRecupera_Informacoes_Objeto(@Nome sysname)
AS
BEGIN
SELECT *
FROM sys.objects
WHERE [name] = @Nome
END
GO
EXEC stpRecupera_Informacoes_Objeto @Nome = 'fncRecupera_Objeto_ID'
GO
CREATE VIEW vwObjetos
AS
SELECT *
FROM sys.objects
WHERE is_ms_shipped = 0
GO
SELECT * FROM dbo.vwObjetos
Ahora voy a eliminar los objetos:
DROP FUNCTION fncRecupera_Objeto_ID
GO
DROP PROCEDURE stpRecupera_Informacoes_Objeto
GO
DROP VIEW vwObjetos
GO
Cómo recuperar objetos eliminados leyendo T-log
Una de las alternativas para recuperar el código fuente de objetos eliminados es leer transacciones y datos del Registro de transacciones de SQL Server, en un consejo que recibí de MVP Rafael Santos en un grupo de Whatsapp.
En caso de que no lo sepa, el Registro de transacciones es un componente crítico de la base de datos que almacena todos los cambios realizados en una base de datos, tanto de estructura como de datos. Cuando se ejecuta el comando CHECKPOINT (ya sea manual o automáticamente), los datos del registro de transacciones se almacenan en el archivo de datos de la base de datos y se eliminan del registro de transacciones. Para comprender mejor cómo funciona el proceso CHECKPOINT, lea la excelente publicación El proceso del PUNTO DE CONTROL, de Vítor Fava.
Dicho esto, ya está claro que la vida útil de los datos almacenados en el Registro de transacciones es limitada y esto deja a esta solución un poco deficiente en términos de confiabilidad para recuperar los datos deseados. Además, para recuperar los datos del registro de transacciones, el modelo de recuperación de la base debe estar configurado en COMPLETO.
Dicho esto, intentemos recuperar los objetos eliminados al principio de esta publicación. Para hacer esto, usaremos la función no documentada sys.fn_dblog en el Procedimiento almacenado a continuación:
USE [master]
GO
CREATE PROCEDURE [dbo].[stpRecupera_Objetos_Excluidos]
@Database NVARCHAR(MAX),
@Dt_Inicial DATETIME,
@Dt_Final DATETIME
AS
BEGIN
DECLARE @Compatibility_Level INT = (SELECT A.[compatibility_level] FROM master.sys.databases AS A WHERE A.name = @Database)
IF (ISNULL(@Compatibility_Level, 0) < = 80) BEGIN
RAISERROR('O modo de compatibilidade deve ser igual ou maior que 90 (SQL SERVER 2005)', 16, 1);
RETURN
END
DECLARE @Query VARCHAR(MAX) = '
USE [' + @Database + '];
SELECT
''' + @Database + ''' AS [DatabaseName],
A.AllocUnitName,
A.[Page ID],
A.[Slot ID],
CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM
sys.fn_dblog(NULL, NULL) A
WHERE
[AllocUnitName] = ''sys.sysobjvalues.clst''
AND A.Operation = ''LOP_DELETE_ROWS''
AND A.Context = ''LCX_MARK_AS_GHOST''
AND EXISTS (
SELECT
NULL
FROM
sys.fn_dblog(NULL, NULL) X
WHERE
[Transaction Name] IN (''CREATE/ALTER PROCEDURE'', ''CREATE/ALTER FUNCTION'', ''CREATE TABLE'', ''DROPOBJ'')
AND (CONVERT(DATETIME, [Begin Time]) BETWEEN ''' + CONVERT(VARCHAR(19), @Dt_Inicial, 121) + ''' AND ''' + CONVERT(VARCHAR(19), @Dt_Final, 121) + ''')
AND A.[Transaction ID] = X.[Transaction ID]
)
AND SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0'
EXEC(@Query)
END
Ejemplo de uso:
Como puede ver, pudimos recuperar fácilmente el código de los 3 objetos que eliminamos. Esto solo fue posible porque el modelo de recuperación estaba configurado en COMPLETO y los datos aún estaban almacenados en el Registro de transacciones.
En entornos que tienen respaldos de registros frecuentes (imagino que son la mayoría), el tiempo que tienes entre borrar los datos e intentar recuperarlos puede ser muy corto, ya que al hacer un respaldo de registros y/o ejecutar un CHECKPOINT, estos datos ya no son accesibles usando esta función.
Cómo recuperar objetos eliminados leyendo copias de seguridad de registros
Utilizando una solución ligeramente diferente a la presentada anteriormente, demostraré cómo recuperar objetos eliminados leyendo las copias de seguridad del registro, que es cuando se hace una copia de seguridad del registro de transacciones y los datos que estaban disponibles usando la función no documentada sys.fn_dblog ya no son accesibles, ya que los datos del registro de transacciones se almacenan en los archivos de copia de seguridad del registro y el registro de transacciones se trunca.
Para superar esta situación, Microsoft nos proporciona la función fn_dump_dblog, donde podemos leer archivos de copia de seguridad del registro y extraer información sobre las transacciones realizadas en la instancia.
Para hacer esto, eliminaré los objetos creados, realizaré una copia de seguridad completa de la base, recrearé los objetos, los eliminaré nuevamente y realizaré una copia de seguridad del registro:
USE [Teste]
GO
IF (OBJECT_ID('dbo.fncRecupera_Objeto_ID') IS NOT NULL) DROP FUNCTION fncRecupera_Objeto_ID
GO
IF (OBJECT_ID('dbo.stpRecupera_Informacoes_Objeto') IS NOT NULL) DROP PROCEDURE stpRecupera_Informacoes_Objeto
GO
IF (OBJECT_ID('dbo.vwObjetos') IS NOT NULL) DROP VIEW vwObjetos
GO
-------------------------------------------------------------------------------
BACKUP DATABASE [Teste] TO DISK ='C:\Backups\SQL\sql2016\Teste_20180707_231600.bak'
GO
-------------------------------------------------------------------------------
CREATE FUNCTION fncRecupera_Objeto_ID(@Nome sysname)
RETURNS INT
AS BEGIN
RETURN (SELECT TOP(1) [object_id] FROM sys.objects WHERE [name] = @Nome)
END
GO
SELECT dbo.fncRecupera_Objeto_ID('sysallocunits')
SELECT dbo.fncRecupera_Objeto_ID('fncRecupera_Objeto_ID')
GO
CREATE PROCEDURE stpRecupera_Informacoes_Objeto(@Nome sysname)
AS
BEGIN
SELECT *
FROM sys.objects
WHERE [name] = @Nome
END
GO
EXEC stpRecupera_Informacoes_Objeto @Nome = 'fncRecupera_Objeto_ID'
GO
CREATE VIEW vwObjetos
AS
SELECT *
FROM sys.objects
WHERE is_ms_shipped = 0
GO
SELECT * FROM dbo.vwObjetos
-------------------------------------------------------------------------------
IF (OBJECT_ID('dbo.fncRecupera_Objeto_ID') IS NOT NULL) DROP FUNCTION fncRecupera_Objeto_ID
GO
IF (OBJECT_ID('dbo.stpRecupera_Informacoes_Objeto') IS NOT NULL) DROP PROCEDURE stpRecupera_Informacoes_Objeto
GO
IF (OBJECT_ID('dbo.vwObjetos') IS NOT NULL) DROP VIEW vwObjetos
GO
-------------------------------------------------------------------------------
CHECKPOINT
GO
BACKUP LOG [Teste] TO DISK ='C:\Backups\SQL\sql2016\Teste_20180707231600.trn'
GOAhora, leamos los datos de la copia de seguridad del registro:
USE [master]
GO
CREATE PROCEDURE [dbo].[stpRecupera_Objetos_Excluidos_Backup]
@Ds_Arquivo NVARCHAR(MAX)
AS
BEGIN
DECLARE @Query VARCHAR(MAX) = '
SELECT
A.[Page ID],
A.[Slot ID],
CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM
sys.fn_dump_dblog(NULL, NULL, N''DISK'', 1, ''' + @Ds_Arquivo + ''', 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) A
WHERE
A.Operation = ''LOP_DELETE_ROWS''
AND A.Context = ''LCX_MARK_AS_GHOST''
AND SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0'
EXEC(@Query)
END
Resultado:
Como pudo ver arriba, mientras hacía la copia de seguridad del registro, stpRecupera_Objetos_Excluidos (sys.fn_dblog) ya no devolvía los comandos para crear los objetos que queríamos recuperar.
Sin embargo, usando stpRecupera_Objetos_Excluidos_Backup (sys.fn_dump_dblog) y especificando el archivo de copia de seguridad del registro que creamos, fue posible recuperar los comandos de creación de objetos.
Usando el registro de ApexSQL
Otra forma de recuperar el código fuente de objetos eliminados e incluso ver los datos modificados/eliminados en tablas es mediante el uso de la herramienta paga. Registro de ApexSQL. La característica distintiva de esta herramienta es que es fácil de usar, intuitiva y le permite analizar los datos del Registro de transacciones y también puede recuperar información de las copias de seguridad del Registro de transacciones.
Operaciones sujetas a recuperación:
Demostración de registro de ApexSQL:
Cómo recuperar consultas leyendo el caché del plan
Otra alternativa para intentar recuperar código objeto y de consulta es consultar la memoria caché del plan de ejecución. Cuando se ejecuta una consulta u objeto, SQL Server analiza si almacenar en caché esta ejecución (por motivos de rendimiento) o no.
Aunque no es muy aplicable al escenario propuesto en esta publicación (ya que al cambiar/eliminar un objeto, generalmente también se elimina su ejecución en caché), la siguiente consulta puede ser útil para recuperar consultas realizadas anteriormente:
USE [Teste]
GO
SELECT
[cp].[refcounts],
[cp].[usecounts],
[cp].[objtype],
[st].[dbid],
[st].[objectid],
[st].[text],
[qp].[query_plan]
FROM
sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp;Resultado:
Cabe mencionar que la vida útil de una ejecución de caché es muy limitada y corta, donde el caché se puede borrar a medida que se ejecutan consultas o incluso si se reinicia el servicio SQL.
Cómo recuperar consultas usando el símbolo SQL
Como ya se demostró en el post. SQL Server: escriba T-SQL como un Ninja utilizando el indicador SQL de Redgate, SQL Prompt tiene una característica realmente interesante llamada "Historial de pestañas", que almacena todas las consultas u objetos que ha abierto desde que instaló SQL Prompt.
Si recientemente abrió/cambió este objeto que fue eliminado, es muy probable que pueda recuperarlo usando la pestaña Historial.
Esta característica es realmente muy práctica, útil y a veces incluso puede salvar vidas... jejeje... En mi empresa anterior, mi Historial de pestañas alcanzó casi 2 GB, con todas las consultas/objetos para los que ya había abierto/visto el código en los últimos 4 años (y ya me ahorró MUCHO tiempo).
¡Bueno, eso es todo, amigos!
Espero que te haya gustado esta publicación.
Un abrazo y hasta la próxima.
Comentários (0)
Carregando comentários…