Estrategia Básica de Backup para SQL Server
Introducción
Contar con una estrategia de respaldo confiable es fundamental para garantizar la disponibilidad y seguridad de los datos en SQL Server. Este documento describe una estrategia básica de backup basada en una programación específica que cubre backups completos (FULL), diferenciales (DIFF) y de registros de transacciones (LOG).
Objetivos
- Garantizar la protección de los datos mediante copias de seguridad periódicas.
- Minimizar la pérdida de información en caso de fallo del sistema.
- Optimizar el uso de almacenamiento con backups comprimidos.
- Automatizar el proceso mediante SQL Server Agent.
Plan de Backup DIARIO SIMPLE, depende de las necesidades de la empresa
Teniendo en cuenta que la actividad de la empresa empieza a las 9:00 am y no hay actividad después de las 00 horas.
| Hora | Tipo de Backup |
|---|---|
| 07:00 | FULL BACKUP |
| 08:00 | LOG BACKUP |
| 09:00 | LOG BACKUP |
| 10:00 | LOG BACKUP |
| 11:00 | LOG BACKUP |
| 12:00 | LOG BACKUP |
| 13:00 | LOG BACKUP |
| 14:00 | LOG BACKUP |
| 15:00 | LOG BACKUP |
| 16:00 | LOG BACKUP |
| 17:00 | LOG BACKUP |
| 18:00 | LOG BACKUP |
| 19:00 | LOG BACKUP |
| 20:00 | LOG BACKUP |
| 21:00 | LOG BACKUP |
| 22:00 | LOG BACKUP |
| 23:00 | DIFFERENTIAL BACKUP |
| 00:00 – 06:00 | Sin actividad en la BBDD |
Script SQL Server para Backups
El siguiente script automatiza la ejecución de backups según el horario programado.
/* SCRIPT DE BACKUP BÁSICO PARA UNA BASE DE DATOS SENCILLA EN SQL SERVER */
/* https://sql-ninja.com */
USE master;
GO
DECLARE @BackupPath NVARCHAR(255) = 'C:\SQLBackups\';
DECLARE @DatabaseName NVARCHAR(100) = 'TuBaseDeDatos';
DECLARE @CurrentTime TIME = CONVERT(TIME, GETDATE());
DECLARE @CurrentHour INT = DATEPART(HOUR, GETDATE());
DECLARE @BackupType NVARCHAR(10);
DECLARE @FileName NVARCHAR(255);
-- Determinar el tipo de backup según la hora
IF @CurrentHour = 7
SET @BackupType = 'FULL';
ELSE IF @CurrentHour BETWEEN 8 AND 22
SET @BackupType = 'LOG';
ELSE IF @CurrentHour = 23
SET @BackupType = 'DIFF';
ELSE
SET @BackupType = 'NONE'; -- No se ejecuta backup entre 00:00 y 06:00 AM
-- Definir el nombre del archivo de backup
SET @FileName = @BackupPath + @DatabaseName + '_' + @BackupType + '_' +
FORMAT(GETDATE(), 'yyyyMMdd_HHmm') + '.bak';
-- Ejecutar el backup según el tipo
IF @BackupType = 'FULL'
BEGIN
BACKUP DATABASE @DatabaseName
TO DISK = @FileName
WITH FORMAT, INIT, COMPRESSION, STATS = 10;
END
ELSE IF @BackupType = 'LOG'
BEGIN
BACKUP LOG @DatabaseName
TO DISK = @FileName
WITH INIT, COMPRESSION, STATS = 5;
END
ELSE IF @BackupType = 'DIFF'
BEGIN
BACKUP DATABASE @DatabaseName
TO DISK = @FileName
WITH DIFFERENTIAL, INIT, COMPRESSION, STATS = 10;
END
ELSE
PRINT 'No hay actividad en la base de datos en este horario, no se ejecuta backup.';
GO
Aquí tienes las diferencias entre los tipos de backup en SQL Server:
1. Full Backup (Respaldo Completo)
📌 Qué hace:
- Guarda toda la base de datos, incluyendo la estructura, los datos y el estado en ese momento.
- Sirve como punto de restauración principal.
📌 Ventajas:
- Restauración completa en un solo paso.
- Base para otros tipos de backup (diferencial y log).
📌 Desventajas:
- Es el más lento y ocupa más espacio en disco.
2. Differential Backup (Respaldo Diferencial)
📌 Qué hace:
- Guarda solo los cambios realizados desde el último Full Backup.
- Es acumulativo, es decir, cada respaldo diferencial contiene todos los cambios desde el último backup completo.
📌 Ventajas:
- Más rápido que el full backup.
- Menos espacio en disco en comparación con el full backup.
📌 Desventajas:
- Para restaurar necesitas el último full backup y el último diferencial.
/*COMO CREAR UN BACKUP DIFERENCIAL*/
BACKUP DATABASE nombre_base_de_datos
TO DISK = ‘E:\SQLDATA\MSSQL15.MSSQLSERVER\MSSQL\Backup\BDA_DIFF_1.bak’
WITH DIFFERENTIAL, INIT, COMPRESSION, STATS = 10;
3. Transaction Log Backup (Respaldo del Log de Transacciones)
📌 Qué hace:
- Guarda solo los cambios realizados en la base de datos desde el último backup de log.
- Permite restaurar hasta un punto exacto en el tiempo (Point-in-Time Recovery).
📌 Ventajas:
- Permite minimizar la pérdida de datos en caso de falla.
- Es el más rápido y ocupa poco espacio.
📌 Desventajas:
- Para restaurar se necesita:
- Full Backup
- Differential Backup (si existe)
- Todos los Transaction Log Backups en orden hasta el punto deseado
/*COMO CREAR UN BACKUP TRANSACCIONAL*/
BACKUP LOG Nombre_base_de_datos
TO DISK = ‘E:\SQLDATA\MSSQL15.MSSQLSERVER\MSSQL\Backup\BDA_LOG_02.bak’
WITH INIT, COMPRESSION, STATS = 5;
Ejemplo de Restauración
Si tu estrategia es:
- Full Backup a las 7:00 AM
- Differential Backup a las 23:00 PM
- Log Backups cada hora
🔹 Para restaurar la base de datos a las 18:30 PM:
1️⃣ Restauras el último Full Backup (7:00 AM)
2️⃣ Aplicar todos los Transaction Log Backups (LOG) desde las 8 am hasta las 18 y aceptar que has perdido 30 minutos de datos…..
3️⃣ Perder 30 minutos de datos es mejor que perder un día de trabajo….
De acá puedes concluir que dependiendo del tipo de empresa puedes hacer una política de recuperación más agresiva de tener más archivos (LOG) cada 5 minutos y un diferencial cada 4 horas, etc. Esto siempre depende de la empresa, si lo necesita o no y acepte que cada hora estará bien.
No todos los días se rompe un servidor pero siempre hay que estar preparados. Y si viene una migración de desarrollo es mejor tener un backup completo antes de un paso a producción.
Resumen
| Tipo de Backup | Contenido | Espacio en Disco | Restauración |
|---|---|---|---|
| Full | Toda la base de datos | Más grande | Solo este backup |
| Diferencial | Cambios desde el último full | Intermedio | Full Backup + Último Diferencial |
| Log | Cambios desde el último log | Pequeño | Full Backup + Diferencial (si hay) + Todos los logs |
Script Creación de Roles en SQL Server
Cómo Obtener el tamaño de los archivos de Datos en SQL Server
Error 15025 SQL Server. The server principal already exists
