Genera reporte de Restores en SQL Server

Hemos visto la importancia de tener políticas de obtención y recuperación de respaldos para cualquier lugar de trabajo y sobre todo para el DBA. Para controlar los backups que generamos en el servidor, en una publicación anterior vimos el cómo listar todos los backups realizados. Ahora es momento que revisemos un reporte de restores en SQL Server para los seguimientos de recuperación.

Si quieres ver más respecto a qué significa ser un DBA, sigue el enlace.

¿Para qué me sirve este reporte?

Un concepto clave en la Seguridad Informática es la generación de pistas de auditoría. Dentro de esta necesidad, podemos imaginar diferentes escenarios en los que requerimos esta información, por ejemplo, reporte de restauraciones de respaldos que:

1. puedan identificar recuperaciones no autorizadas
2. permitan una evidencia de acciones de recovery
3. demuestre la actualización de ambientes no productivos
4. den a conocer los archivos orígenes de una recuperación

Para este propósito nos apoyaremos del procedimiento sp_ReportRestores. Este reporte está basado en la información almacenada en la base de datos de sistema msdb respecto al historial de respaldos.

 
CREATE OR ALTER PROC sp_ReportRestores
	@Days smallint = 0,
	@OrderBy nvarchar(10) = NULL

AS
BEGIN

	DECLARE 
		@Query nvarchar(max) = NULL,
		@LineFeed NVARCHAR(10)
	

	SELECT @LineFeed = CHAR(13) + CHAR(10)


		SET @Query = N'
		SELECT 
			''RESTORES'' AS [REPORT],
			@@SERVERNAME AS [ServerName],
			[bs].[database_name] AS [SourceDatabaseName], 
			[bmf].[physical_device_name] AS [SourceFileForRestore],
			[bs].[backup_start_date] AS [BkpStartDate], 
			[bs].[backup_finish_date] AS [BkpFinishDate],
			[rh].[destination_database_name] AS [DestinationDatabaseName],
			[rh].[restore_date] AS [RestoreDate],
			CASE [rh].[restore_type] 
				WHEN ''D'' THEN ''Database''
				WHEN ''F'' THEN ''File''
				WHEN ''G'' THEN ''Filegroup''
				WHEN ''I'' THEN ''Differential''
				WHEN ''L'' THEN ''Log''
				WHEN ''V'' THEN ''Verifyonly''
				ELSE ''''
			END AS [RestoreType]
			FROM msdb..restorehistory AS [rh]
			INNER JOIN msdb..backupset AS [bs]
				ON [rh].[backup_set_id] = [bs].[backup_set_id]
			INNER JOIN msdb..backupmediafamily AS [bmf] 
				ON [bs].[media_set_id] = [bmf].[media_set_id] 
		'

	IF(@Days != 0)
		SET @Query = @Query + @LineFeed + N'WHERE (CONVERT(datetime, [rh].[restore_date], 102) >= GETDATE() - ' + CONVERT(nvarchar(5), @Days) + ')' + @LineFeed


	SET @Query = CASE @OrderBy
		WHEN 'dbname' THEN @Query + @LineFeed + N'ORDER BY [bs].[destination_database_name]'
		WHEN 'date' THEN @Query + @LineFeed + N'ORDER BY [rh].[restore_date] DESC'
		ELSE @Query + @LineFeed + N'ORDER BY [bs].[database_name], [bs].[backup_finish_date]'
	END

	EXEC(@Query)

END

@Days

Podemos asignar un valor numérico que represente la cantidad de días y generar un reporte solamente de las últimas restauraciones realizadas.

@OrderBy

Por defecto consideramos un reporte que muestra los datos ordenados por nombre de base de datos de origen y fecha de finalización de obtención del respaldo. Podemos considerar dos opciones adicionales: “dbname” (orden alfabético de nombres de bases restauradas) y “date” (muestra los respaldos más recientemente restaurados).

Consideraciones para el Reporte de Restores

Al igual que la información obtenida para el Reporte de Backups, el origen de datos viene de la base de datos msdb. Es importante considerar también el respaldo de ella.

Generar este reporte servirá de respaldo de acciones de restauración realizadas, sin embargo, usualmente documentar físicamente esta información también es importante. Más aún para temas de auditoría.