Nexus - AI Task Orchestrator

12-database-backup-verification
Procedimiento: Verificaci├│n de Backups de Base de Datos

Base de Datos
1 plugin(s)
Titulo
Descripcion
Contenido Markdown
13318 caracteres
# Procedimiento: Verificaci├│n de Backups de Base de Datos

## Metadata
- **ID**: PROC-12
- **Frecuencia**: Semanal (verificaci├│n), Mensual (test restore completo)
- **Duraci├│n estimada**: 30-90 min
- **Requiere**: Acceso a servidores DB, credenciales backup, ambiente staging
- **Dependencias**: PROC-10 (Database Performance Review)
- **Bloquea**: Ninguno (pero cr├¡tico para DR)

## Objetivo

Verificar que los backups autom├íticos de bases de datos funcionan correctamente, validar la capacidad de restauraci├│n, y documentar RTO/RPO reales del sistema.

## Prerrequisitos

### SQL Server
```powershell
# Verificar acceso a instancia
sqlcmd -S localhost -Q "SELECT @@VERSION"

# Verificar permisos de backup
sqlcmd -S localhost -Q "SELECT name, is_disabled FROM sys.server_principals WHERE type = 'S' AND name = 'backup_user'"
```

### PostgreSQL
```bash
# Verificar acceso
psql -h localhost -U postgres -c "SELECT version();"

# Verificar pg_dump disponible
pg_dump --version
```

## Checklist Ejecutable

### 1. Inventariar Bases de Datos

```powershell
# SQL Server - Listar databases y tama├▒os
$query = @"
SELECT 
    db.name AS DatabaseName,
    CAST(SUM(mf.size) * 8 / 1024.0 AS DECIMAL(10,2)) AS SizeMB,
    db.recovery_model_desc AS RecoveryModel,
    db.state_desc AS State
FROM sys.databases db
JOIN sys.master_files mf ON db.database_id = mf.database_id
WHERE db.database_id > 4  -- Excluir system DBs
GROUP BY db.name, db.recovery_model_desc, db.state_desc
ORDER BY SizeMB DESC
"@

sqlcmd -S localhost -Q $query -W
```

```bash
# PostgreSQL - Listar databases y tama├▒os
psql -h localhost -U postgres -c "
SELECT 
    datname AS database,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database 
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;
"
```

- [ ] Inventario de DBs documentado
- [ ] Tama├▒os registrados para comparaci├│n futura

### 2. Verificar Backups Recientes

```powershell
# SQL Server - ├Ültimos backups por database
$query = @"
SELECT 
    database_name,
    type AS backup_type,
    CAST(backup_start_date AS DATE) AS backup_date,
    CAST(backup_size / 1024 / 1024 AS INT) AS size_mb,
    DATEDIFF(HOUR, backup_start_date, GETDATE()) AS hours_ago
FROM msdb.dbo.backupset
WHERE backup_start_date > DATEADD(DAY, -7, GETDATE())
ORDER BY backup_start_date DESC
"@

sqlcmd -S localhost -Q $query -W
```

```bash
# PostgreSQL - Verificar archivos de backup (ubicaci├│n t├¡pica)
BACKUP_DIR="/var/backups/postgresql"

echo "=== Backups ├║ltimos 7 d├¡as ==="
find $BACKUP_DIR -name "*.sql.gz" -o -name "*.dump" -mtime -7 -exec ls -lh {} \;

echo ""
echo "=== Backup m├ís reciente ==="
ls -lt $BACKUP_DIR | head -5
```

| Database | ├Ültimo Full | ├Ültimo Diff/Incremental | Antig├╝edad | Estado |
|----------|-------------|-------------------------|------------|--------|
| | | | | |

- [ ] Todas las DBs tienen backup < 24h (o seg├║n pol├¡tica)
- [ ] Backups completos semanales existen
- [ ] No hay gaps en la cadena de backups

### 3. Verificar Integridad de Backups

```powershell
# SQL Server - Verificar integridad del ├║ltimo backup
$backupFile = "C:\Backups\database_full.bak"

$verifyQuery = @"
RESTORE VERIFYONLY 
FROM DISK = '$backupFile'
WITH CHECKSUM
"@

sqlcmd -S localhost -Q $verifyQuery

if ($LASTEXITCODE -eq 0) {
    Write-Host "Ô£à Backup integrity verified" -ForegroundColor Green
} else {
    Write-Host "ÔØî Backup integrity FAILED" -ForegroundColor Red
}
```

```bash
# PostgreSQL - Verificar archivo no corrupto
BACKUP_FILE="/var/backups/postgresql/latest.sql.gz"

# Verificar integridad del gzip
if gzip -t "$BACKUP_FILE" 2>/dev/null; then
    echo "Ô£à Backup file integrity OK"
    
    # Verificar contenido SQL v├ílido
    if zcat "$BACKUP_FILE" | head -100 | grep -q "PostgreSQL database dump"; then
        echo "Ô£à Backup content appears valid"
    else
        echo "ÔÜá´©Å Backup content may be invalid"
    fi
else
    echo "ÔØî Backup file CORRUPTED"
fi
```

- [ ] Integridad de archivos verificada
- [ ] Checksums v├ílidos
- [ ] Sin archivos corruptos

### 4. Test de Restore (Ambiente Staging)

```powershell
# SQL Server - Restore a database de test
$sourceBackup = "C:\Backups\Production_full.bak"
$testDbName = "Production_RestoreTest_$(Get-Date -Format 'yyyyMMdd')"

$restoreQuery = @"
-- Obtener nombres l├│gicos del backup
RESTORE FILELISTONLY FROM DISK = '$sourceBackup';

-- Restore con nuevos nombres
RESTORE DATABASE [$testDbName]
FROM DISK = '$sourceBackup'
WITH 
    MOVE 'Production_Data' TO 'C:\SQLData\${testDbName}_Data.mdf',
    MOVE 'Production_Log' TO 'C:\SQLData\${testDbName}_Log.ldf',
    RECOVERY,
    STATS = 10;
"@

$startTime = Get-Date
sqlcmd -S localhost -Q $restoreQuery
$duration = (Get-Date) - $startTime

Write-Host "Restore completed in: $($duration.TotalMinutes) minutes"

# Verificar DB restaurada
sqlcmd -S localhost -Q "SELECT COUNT(*) AS TableCount FROM [$testDbName].sys.tables"

# Limpiar despu├®s del test
# sqlcmd -S localhost -Q "DROP DATABASE [$testDbName]"
```

```bash
# PostgreSQL - Restore a database de test
BACKUP_FILE="/var/backups/postgresql/production_latest.sql.gz"
TEST_DB="production_restore_test_$(date +%Y%m%d)"

echo "=== Creating test database ==="
createdb -h localhost -U postgres $TEST_DB

echo "=== Restoring backup (this may take a while) ==="
START_TIME=$(date +%s)

zcat $BACKUP_FILE | psql -h localhost -U postgres -d $TEST_DB

END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))

echo "=== Restore completed in $DURATION seconds ==="

# Verificar datos
echo "=== Verification ==="
psql -h localhost -U postgres -d $TEST_DB -c "
SELECT 
    schemaname,
    COUNT(*) as table_count
FROM pg_tables 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
GROUP BY schemaname;
"

# Limpiar
# dropdb -h localhost -U postgres $TEST_DB
```

| M├®trica | Valor | Target | Estado |
|---------|-------|--------|--------|
| Tiempo de restore | min | < 60 min | |
| Tablas restauradas | | | |
| Datos verificados | S├¡/No | S├¡ | |

- [ ] Restore completado sin errores
- [ ] Tiempo de restore documentado (RTO real)
- [ ] Datos verificados post-restore
- [ ] Database de test eliminada

### 5. Verificar Pol├¡ticas de Retenci├│n

```powershell
# SQL Server - Contar backups por antig├╝edad
$query = @"
SELECT 
    CASE 
        WHEN DATEDIFF(DAY, backup_start_date, GETDATE()) <= 7 THEN '0-7 days'
        WHEN DATEDIFF(DAY, backup_start_date, GETDATE()) <= 30 THEN '8-30 days'
        WHEN DATEDIFF(DAY, backup_start_date, GETDATE()) <= 90 THEN '31-90 days'
        ELSE '90+ days'
    END AS age_bucket,
    COUNT(*) AS backup_count,
    SUM(backup_size) / 1024 / 1024 / 1024 AS total_size_gb
FROM msdb.dbo.backupset
GROUP BY 
    CASE 
        WHEN DATEDIFF(DAY, backup_start_date, GETDATE()) <= 7 THEN '0-7 days'
        WHEN DATEDIFF(DAY, backup_start_date, GETDATE()) <= 30 THEN '8-30 days'
        WHEN DATEDIFF(DAY, backup_start_date, GETDATE()) <= 90 THEN '31-90 days'
        ELSE '90+ days'
    END
ORDER BY 1
"@

sqlcmd -S localhost -Q $query -W
```

```bash
# PostgreSQL/Files - Verificar retenci├│n
BACKUP_DIR="/var/backups/postgresql"

echo "=== Backups by age ==="
echo "Last 7 days:"
find $BACKUP_DIR -mtime -7 -type f | wc -l

echo "8-30 days:"
find $BACKUP_DIR -mtime +7 -mtime -30 -type f | wc -l

echo "31-90 days:"
find $BACKUP_DIR -mtime +30 -mtime -90 -type f | wc -l

echo "90+ days (should be deleted per policy):"
find $BACKUP_DIR -mtime +90 -type f | wc -l

echo ""
echo "=== Total backup storage ==="
du -sh $BACKUP_DIR
```

**Pol├¡tica de retenci├│n esperada:**

| Tipo | Retenci├│n | Actual | Cumple |
|------|-----------|--------|--------|
| Diarios | 7 d├¡as | | |
| Semanales | 4 semanas | | |
| Mensuales | 12 meses | | |

- [ ] Retenci├│n cumple pol├¡tica
- [ ] Espacio de storage adecuado
- [ ] Backups antiguos purgados correctamente

### 6. Verificar Backup Offsite/Cloud

```powershell
# Verificar sincronizaci├│n a storage remoto (ejemplo Azure Blob)
az storage blob list `
    --account-name mystorageaccount `
    --container-name sql-backups `
    --query "[?properties.lastModified > '$(Get-Date (Get-Date).AddDays(-1) -Format 'yyyy-MM-ddTHH:mm:ssZ')'].[name, properties.contentLength]" `
    --output table

# O AWS S3
aws s3 ls s3://my-backup-bucket/sql-backups/ --recursive | tail -10
```

```bash
# Verificar rsync a servidor remoto
REMOTE_HOST="backup-server"
REMOTE_PATH="/backups/postgresql"

ssh $REMOTE_HOST "ls -lt $REMOTE_PATH | head -5"

# Verificar ├║ltima sincronizaci├│n
ssh $REMOTE_HOST "stat -c '%y' $REMOTE_PATH/latest.sql.gz"
```

- [ ] Backups replicados offsite
- [ ] ├Ültima sincronizaci├│n < 24h
- [ ] Acceso a backup remoto verificado

### 7. Documentar RTO/RPO

```markdown
## Recovery Metrics - [Fecha]

### RPO (Recovery Point Objective)
- **Target**: 1 hora
- **Actual**: [X] horas (basado en frecuencia de backup)
- **Cumple**: S├¡/No

### RTO (Recovery Time Objective)  
- **Target**: 2 horas
- **Actual**: [X] minutos (basado en test de restore)
- **Cumple**: S├¡/No

### Capacidad de Recovery
| Escenario | Tiempo Estimado | ├Ültimo Test |
|-----------|-----------------|-------------|
| Tabla borrada accidentalmente | 15 min | |
| Database corrupta | 45 min | |
| Servidor completo down | 2 horas | |
| Disaster recovery (otro DC) | 4 horas | |
```

- [ ] RPO documentado y cumple objetivo
- [ ] RTO documentado y cumple objetivo
- [ ] Escenarios de recovery documentados

### 8. Crear Alerta si Backup Falla

```powershell
# SQL Server - Query para monitoreo (ejecutar desde job o Sentry)
$query = @"
DECLARE @MaxHoursWithoutBackup INT = 24

SELECT 
    d.name AS DatabaseName,
    COALESCE(
        DATEDIFF(HOUR, MAX(b.backup_start_date), GETDATE()),
        999
    ) AS HoursSinceLastBackup,
    CASE 
        WHEN MAX(b.backup_start_date) IS NULL THEN 'NEVER'
        WHEN DATEDIFF(HOUR, MAX(b.backup_start_date), GETDATE()) > @MaxHoursWithoutBackup THEN 'ALERT'
        ELSE 'OK'
    END AS Status
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.database_id > 4
GROUP BY d.name
HAVING COALESCE(DATEDIFF(HOUR, MAX(b.backup_start_date), GETDATE()), 999) > @MaxHoursWithoutBackup
"@

$result = sqlcmd -S localhost -Q $query -W -h -1
if ($result) {
    Write-Host "ÔÜá´©Å DATABASES WITHOUT RECENT BACKUP:" -ForegroundColor Red
    Write-Host $result
    # Aqu├¡ enviar alerta
}
```

- [ ] Alertas configuradas para backups fallidos
- [ ] Notificaci├│n probada

## Troubleshooting

| S├¡ntoma | Causa Probable | Acci├│n |
|---------|----------------|--------|
| Backup no existe | Job fallido, espacio lleno | Verificar SQL Agent jobs, espacio |
| Restore falla | Backup corrupto, versi├│n incompatible | Verificar integridad, usar backup anterior |
| Restore lento | I/O saturado, archivo fragmentado | Restore en horario bajo, desfragmentar |
| Backup muy grande | Sin compression, datos innecesarios | Habilitar compression, purgar datos |

## Resultado

- **├ëxito**:
  - Todos los backups existen y son recientes (< 24h)
  - Integridad verificada
  - Test restore exitoso
  - RTO/RPO documentados y cumplen targets

- **Parcial**:
  - Algunos backups antiguos pero < 48h
  - Test restore con warnings menores

- **Fallo**:
  - Backup > 48h antiguo ÔåÆ CR├ìTICO
  - Backup corrupto ÔåÆ CR├ìTICO
  - Restore falla ÔåÆ CR├ìTICO

## Escalaci├│n

- **Backup > 24h**: Notificar equipo de infraestructura
- **Backup corrupto**: Escalar a DBA senior inmediatamente
- **Sin backup offsite**: Notificar responsable de DR
- **RTO/RPO no cumplen**: Revisar arquitectura con arquitecto

## Mensaje de Finalizaci├│n

**ACCI├ôN REQUERIDA AL FINALIZAR:**

```bash
echo "====== PROCESO TERMINADO [$(date +%H%M%S)] ======" && echo "RESULTADO: Backup Verification - STATUS, DBs: X, ├Ültimo backup: Yh ago, Restore test: Z min"
```


---

## Output Estructurado (Nexus)

Al finalizar, el agente DEBE generar un bloque JSON con el siguiente formato para que Nexus pueda procesarlo automaticamente:

```json
{
  "result": "success",
  "summary": "Ejecucion de PROC-12 completada. [Descripcion breve de resultados]",
  "metrics": {
    "issues_found": 0,
    "issues_resolved": 0,
    "queries_analyzed": 50, "slow_queries": 3,
    "custom": {
      "procedure_specific_metric": "value"
    }
  },
  "backlog_items": [
    {
      "title": "Titulo del item de seguimiento",
      "description": "Descripcion detallada si se requiere accion futura",
      "priority": "medium",
      "type": "improvement",
      "tags": ["proc-12"]
    }
  ],
  "next_steps": [
    "Accion recomendada 1",
    "Accion recomendada 2"
  ],
  "warnings": [
    "Advertencias encontradas durante la ejecucion"
  ]
}
```

**Campos requeridos:**
- `result`: `"success"` | `"partial"` | `"failed"`
- `summary`: Resumen ejecutivo en 1-3 lineas

**Metricas especificas de este procedure:**
- backups_verified, backup_age_hours, integrity_check

**Criterios de resultado:**
- `success`: Procedimiento completado sin errores criticos
- `partial`: Completado con algunos problemas menores o items pendientes
- `failed`: Error critico o no se pudo completar

## Historial de Ejecuciones

| Fecha | Ejecutor | Status | DBs Verificadas | Restore Test | RTO Real | Issues |
|-------|----------|--------|-----------------|--------------|----------|--------|
| | | | | | | |