Genera reporte de Backups en SQL Server

Después de haber comprendido la importancia de la obtención de respaldos de nuestras bases de datos, es momento de hacer un seguimiento a todos ellos. Gran parte del trabajo del DBA se centra en garantizar la recuperación de información ante una contingencia, por eso, es importante poder revisar periodicamente un reporte de backups en SQL Server.

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

¿Qué debemos revisar?

Es vital tener un control de qué respaldos son los que estamos obteniendo y qué respaldos están siendo restaurados. Tanto para un control personal o de nuestros dependientes, como para efectos de auditoría.

Para este propósito nos apoyaremos del procedimiento sp_ReportBackups. 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_ReportBackups
	@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 
			''BACKUPS'' AS [REPORT],
			@@SERVERNAME AS [ServerName],
			[bs].[database_name] AS [DatabaseName],
			[bs].[backup_start_date] AS [BkpStartDate],
			[bs].[backup_finish_date] AS [BkpFinishDate],
			[bs].[expiration_date] AS [BkpExpirationDate],
			CASE [bs].[type]
				WHEN ''D'' THEN ''Full''
				WHEN ''I'' THEN ''Differential''
				WHEN ''L'' THEN ''Log''
				WHEN ''F'' THEN ''File/Filegroup''
				WHEN ''G'' THEN ''Differential File''
				WHEN ''P'' THEN ''Partial''
				WHEN ''Q'' THEN ''Differential Partial''
			END AS [BkpType],
			[bs].[backup_size] AS [BkpSize],
			[bmf].[logical_device_name] AS [LogicalDevName],
			[bmf].[physical_device_name] AS [PhysicalDevName],
			[bs].[name] AS [BackupsetName],
			[bs].[description] AS [Description]
		FROM msdb.dbo.backupmediafamily AS [bmf]
		INNER JOIN msdb.dbo.backupset AS [bs]
			ON [bmf].[media_set_id] = [bs].[media_set_id]
		'

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


	SET @Query = CASE @OrderBy
		WHEN 'size' THEN @Query + @LineFeed + N'ORDER BY [bs].[backup_size] DESC'
		WHEN 'dbname' THEN @Query + @LineFeed + N'ORDER BY [bs].[database_name]'
		WHEN 'date' THEN @Query + @LineFeed + N'ORDER BY [bs].[backup_start_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 los últimos requeridos.

@OrderBy

Por defecto consideramos un reporte que muestra los datos ordenados por nombre de base de datos y fecha de finalización de obtención del respaldo. Podemos considerar tres opciones adicionales: «size» (tamaños desde los más pesados), «dbname» (orden alfabético de nombres de bases), «date» (muestra los respaldos más recientes)

Consideraciones para el reporte de backups

La generación de reportes sin acción no aporta valor. Es importante considerar la revisión de estos reportes para realizar tareas correctivas o futuras preventivas.

Si hemos programado la obtención de respaldos de manera automática, el sp_ReportBackups nos permitirá una validación respecto a la correcta ejecución de estas tareas.

Puedes ver también Cómo Generar un Reporte de Restores de SQL Server.