Script–Situación de los backup de base de datos

Lo sabemos. Debemos tener copias de respaldo de las bases de datos. ¿Pero, estamos seguros de que las tenemos? ¿Para todas las bases de datos? ¿De la forma correcta?.

No son pocas las veces que recibo llamadas para consultarme si hay una forma alternativa de recuperar una base de datos, porque “se acaban de dar cuenta” que no hay backup; o también “se acaban de dar cuenta” que los backup automáticos no han estado funcionando.

Bueno, ya en el blog hemos tratado el tema de la estrategia de recuperación de desastres y técnicas relativas a los backup (debajo de este artículo están los links relacionados).

En esta oportunidad compartimos un par de scripts que nos van a decir rápidamente la situación de los backup de las bases de datos de un servidor SQL Server.

Este primer script se basa en la tabla del sistema dbo.backupset, la cual se ubica en la base de datos del sistema msdb, y nos lista las copias de seguridad de las bases de datos, con la fecha de ejecución de la última copia completa (FULL). Sí, es cierto que podemos estar combinando copias completas con diferenciales o de transaction log, pero la copia completa marca el punto de partida para una recuperación y no debe ser muy antigua. El script incluye mensajes sobre la situación de la copia de seguridad, con un toque de humor que siempre ayuda.

Este es el script, el cual esté diseñado para ejecutarse en SQL Server 2008 o superior:

WITH CTE_BackupFull AS (
SELECT  
   database_name,
   MAX(backup_finish_date) AS [Última copia]
FROM  msdb.dbo.backupset
WHERE  type = 'D'
GROUP BY database_name)
SELECT 
  CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Instancia],
  db.name,
  ISNULL(CAST([Última copia] as CHAR(50)), 'nunca!!') AS [Última copia Full],
  CASE 
    WHEN [Última copia] IS NULL THEN ''
    WHEN DATEDIFF(dd,[Última copia],GETDATE())=0 THEN 'Hoy!'
    WHEN DATEDIFF(dd,[Última copia],GETDATE())=0 THEN 'Ayer'
    ELSE 'Hace ' + CAST(DATEDIFF(dd,[Última copia],GETDATE()) AS varchar(10)) + ' días' 
    END [Días],
  CASE 
    WHEN db.name like 'AdventureWorks%' THEN 'Ah! esto quiere decir que estas practicando...bien!' 
    WHEN db.name = 'tempdb' 
      THEN 'Esta no es necesario respaldar' 
    WHEN db.name in ('master','model','msdb') AND DATEDIFF(dd,[Última copia],GETDATE()) < 30 THEN 'Razonable, dependiendo de la estrategia de backup' 
    WHEN db.name in ('master','model','msdb') AND DATEDIFF(dd,[Última copia],GETDATE()) > 30 THEN 'Las bds del sistema tambipen se respaldan' 
    WHEN [Última copia] IS NULL THEN 'Haz una copia ya! Por si acaso ve actualizando tu CV'
    WHEN DATEDIFF(dd,[Última copia],GETDATE())< 3 THEN 'Bien' 
    WHEN DATEDIFF(dd,[Última copia],GETDATE()) BETWEEN 4 AND 7 THEN 'Razonable, dependiendo de la estrategia de backup' 
    WHEN DATEDIFF(dd,[Última copia],GETDATE()) BETWEEN 8 AND 20 THEN 'Vaya! Esta debe ser una base de datos de prueba, o no?'
    WHEN DATEDIFF(dd,[Última copia],GETDATE()) > 20 THEN 'Esta base de datos sirve para algo. Si es así...respaldala ya!'
  END Mensaje  
  FROM sys.databases db
    LEFT JOIN CTE_BackupFull b
      ON db.name=b.database_name
ORDER BY [Última copia] DESC;
GO

WITH CTE_BackupFull AS (

SELECT

database_name,

MAX(backup_finish_date) AS [Última copia]

FROM msdb.dbo.backupset

WHERE type = 'D'

GROUP BY database_name)

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Instancia],

db.name,

ISNULL(CAST([Última copia] as CHAR(50)), 'nunca!!') AS [Última copia Full],

CASE

WHEN [Última copia] IS NULL THEN ''

WHEN DATEDIFF(dd,[Última copia],GETDATE())=0 THEN 'Hoy!'

WHEN DATEDIFF(dd,[Última copia],GETDATE())=0 THEN 'Ayer'

ELSE 'Hace ' + CAST(DATEDIFF(dd,[Última copia],GETDATE()) AS varchar(10)) + ' días'

END [Días],

CASE

WHEN db.name like 'AdventureWorks%' THEN 'Ah! esto quiere decir que estas practicando...bien!'

WHEN db.name = 'tempdb'

THEN 'Esta no es necesario respaldar'

WHEN db.name in ('master','model','msdb') AND DATEDIFF(dd,[Última copia],GETDATE()) < 30 THEN 'Razonable, dependiendo de la estrategia de backup'

WHEN db.name in ('master','model','msdb') AND DATEDIFF(dd,[Última copia],GETDATE()) > 30 THEN 'Las bds del sistema tambipen se respaldan'

WHEN [Última copia] IS NULL THEN 'Haz una copia ya! Por si acaso ve actualizando tu CV'

WHEN DATEDIFF(dd,[Última copia],GETDATE())< 3 THEN 'Bien'

WHEN DATEDIFF(dd,[Última copia],GETDATE()) BETWEEN 4 AND 7 THEN 'Razonable, dependiendo de la estrategia de backup'

WHEN DATEDIFF(dd,[Última copia],GETDATE()) BETWEEN 8 AND 20 THEN 'Vaya! Esta debe ser una base de datos de prueba, o no?'

WHEN DATEDIFF(dd,[Última copia],GETDATE()) > 20 THEN 'Esta base de datos sirve para algo. Si es así...respaldala ya!'

END Mensaje

FROM sys.databases db

LEFT JOIN CTE_BackupFull b

ON db.name=b.database_name

ORDER BY [Última copia] DESC;

Aquí va el resultado (se han ocultado algunos nombres por tema de confidencialidad):

Lo que podemos notar aquí es que hay ciertas bases de datos que tienen muchos días sin ser respaldadas y algunas que nunca han sido respaldadas nunca. Esto es solo informativo. Hay que evaluar cada caso para conocer si es grave o no.

Ahora vamos a mostrar un segundo script que nos da información detallada sobre el historial de los backup. Además de utilizar la tabla del sistema dbo.backupset, también utiliza la tabla dbo.backupmediafamily para obtener detalles de la copia. Ambas tablas se ubican en la base de datos del sistema msdb. Aquí si consideramos todos los tipos de copia de seguridad para conocer la secuencia completa de cómo se está respaldando la base de datos. Aquí podemos encontrar información útil para planificación como el tamaño y tiempos de duración de los backup.

También podemos ver la unidad, ubicación y nombre del backup resultante. Tener en cuenta que esto es un historial, no necesariamente el backup se encuentra allí en el momento que se ejecuta el script, porque el archivo puede haber sido borrado o movido a otra ubicación.

Tener en cuenta que al inicio del script hay una variable que indica la cantidad de días de historia que se desea consultar. En este caso son 60 días.

Aquí va el script:

DECLARE @Dias smallint = 60

SELECT DISTINCT
   RTRIM(CONVERT(CHAR(100), SERVERPROPERTY('Servername'))) AS [Instancia], 
   s.database_name AS [Base Datos],  
   s.backup_start_date AS [Inicio],  
   s.backup_finish_date AS [Fin], 
   CAST(s.backup_finish_date-s.backup_start_date AS TIME) AS [Duración], 
   CASE s.type   
	 WHEN 'D' THEN 'D - Full'
     WHEN 'I' THEN 'I - Diferencial'
     WHEN 'L' THEN 'L - Log'
     WHEN 'F' THEN 'F - Archivo/Grupo archivo'
     WHEN 'G' THEN 'G - Archivo diferencial'
     WHEN 'P' THEN 'P - Parcial'
     WHEN 'Q' THEN 'Q - Parcial diferencial'
   END AS [Tipo], 
   CASE s.is_copy_only
	   WHEN 1 THEN 'Si'
	   ELSE 'No'
   END [Solo copia?], 
   s.backup_size/1048576.00 AS [Tamaño KB],  
   ISNULL(mf.logical_device_name,'') AS [Dispositivo lógico],  
   mf.physical_device_name AS [Archivo físico], 
   LEFT(mf.physical_device_name,2) AS [Unidad],   
   ISNULL(s.name,'') AS [Backup set], 
   ISNULL(s.description,'') AS [Descripción]
FROM msdb.dbo.backupmediafamily  mf
   INNER JOIN msdb.dbo.backupset s
     ON mf.media_set_id = s.media_set_id  
WHERE (CONVERT(datetime, s.backup_start_date, 102) >= GETDATE() - @Dias)  
  AND DB_ID(database_name) IS NOT NULL  --<-- Solo BDs que existen
ORDER BY  
   s.database_name, 
   s.backup_finish_date

DECLARE @Dias smallint = 60

SELECT DISTINCT

RTRIM(CONVERT(CHAR(100), SERVERPROPERTY('Servername'))) AS [Instancia],

s.database_name AS [Base Datos],

s.backup_start_date AS [Inicio],

s.backup_finish_date AS [Fin],

CAST(s.backup_finish_date-s.backup_start_date AS TIME) AS [Duración],

CASE s.type

WHEN 'D' THEN 'D - Full'

WHEN 'I' THEN 'I - Diferencial'

WHEN 'L' THEN 'L - Log'

WHEN 'F' THEN 'F - Archivo/Grupo archivo'

WHEN 'G' THEN 'G - Archivo diferencial'

WHEN 'P' THEN 'P - Parcial'

WHEN 'Q' THEN 'Q - Parcial diferencial'

END AS [Tipo],

CASE s.is_copy_only

WHEN 1 THEN 'Si'

ELSE 'No'

END [Solo copia?],

s.backup_size/1048576.00 AS [Tamaño KB],

ISNULL(mf.logical_device_name,'') AS [Dispositivo lógico],

mf.physical_device_name AS [Archivo físico],

LEFT(mf.physical_device_name,2) AS [Unidad],

ISNULL(s.name,'') AS [Backup set],

ISNULL(s.description,'') AS [Descripción]

FROM msdb.dbo.backupmediafamily mf

INNER JOIN msdb.dbo.backupset s

ON mf.media_set_id = s.media_set_id

WHERE (CONVERT(datetime, s.backup_start_date, 102) >= GETDATE() - @Dias)

AND DB_ID(database_name) IS NOT NULL --<-- Solo BDs que existen

ORDER BY

s.database_name,

s.backup_finish_date

Aquí va el resultado (se han ocultado algunos nombres por tema de confidencialidad):

Aquí podemos ver si las fechas de los backups están acorde al plan de respaldos, que forma parte de la estrategia de recuperación de desastres. Seguramente estos backups se han programado mediante un trabajo del Agente de SQl Server (job) o un plan de mantenimiento, los cuales también deben ser revisados permanentemente para garantizar su correcto funcionamiento.

Espero que les sirvan estos scripts. No olviden compartirlo con la comunidad. ¡Hasta la próxima!

2 comentarios en “Script–Situación de los backup de base de datos”

Mauricio dice:

31/01/2019 a las 18:33

Hola, me puedes ayudar para que solo pueda generar el reporte de una sola BD, creo que en script me muestra el de todas las BD que tengo.

Responder
- Alberto De Rossi dice:
  
  04/02/2019 a las 10:05
  
  Hola Mauricio, para que muestres solo una base de datos agrega una condición WHERE db.name = ‘MiBaseDeDatos’ Agrga esa línea justo antes del ORDER BY
  
  Suerte!
  
  Responder

Comparte Este artículo:

2 comentarios en “Script–Situación de los backup de base de datos”

Deja una respuesta Cancelar la respuesta