Skip to content

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.

www.datoptim.com
I love working on SQL Server Performance Tuning and finding the origin of the problems. Music and SQL Server passionate.

Esta entrada tiene 4 comentarios

  1. Hola Pablo,
    Soy algo nuevo en administacion.
    Al ejecutar el Query me da este error:
    ———————————————————————
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘OR’.
    Msg 111, Level 15, State 1, Line 6
    ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.
    ———————————————————————-
    He cambiado varias cosas pero me da otros errores, que podria ser?
    Tengo Windows Server 2016 Standard 6.3 Hyper-V

    Gracias!

    1. Hola Jonathan! La sintaxis ‘CREATE OR ALTER’ que ves al principio del procedimiento es una sentencia nueva de SQL Server 2016 SP1, es posible que tú no tengas esa actualización. Pueden intentar utilizando solamente ‘CREATE PROC…’.
      Cuéntame cómo te va

Los comentarios están cerrados.

Carrito
Volver arriba