Narcis Miclaus

6 min di lettura

SQL Server, dalle fondamenta Lezione 31 / 40

Backup: FULL, DIFF, LOG, e l'esercitazione di restore

I tre tipi di backup, point-in-time recovery, come testare un backup che non hai mai testato, e la pianificazione che ogni sistema OLTP dovrebbe avere.

Un backup che non hai mai testato non è un backup. È un file. Potrebbe essere un buon backup, potrebbe essere corrotto, potrebbe star facendo il backup del database sbagliato — non lo saprai finché non arriva il giorno in cui provi a ripristinarlo. Quel giorno è sempre un sabato, è sempre quando il telefono sta per scaricarsi, ed è sempre quando il CEO è in copia nell’email.

Questa lezione parla di costruire una strategia di backup di cui ti fidi davvero. Tipi di backup, recovery model, l’esercitazione di restore che dovresti fare ogni trimestre, e la complicazione specifica per l’UE che è la regolamentazione sulla data residency.

I tre tipi di backup

Backup FULL

Una copia completa del database in un dato momento. Contiene ogni pagina di dati e abbastanza log da rendere il backup internamente consistente.

BACKUP DATABASE Runehold
TO DISK = N'D:\Backups\Runehold_FULL_20260315.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;
  • FORMAT, INIT — sovrascrivi il file se esiste. Sicurezza: vogliamo quasi sempre un backup fresco, non in append.
  • COMPRESSION — file molto più piccolo. SQL Server Enterprise moderno include la compression gratis; Standard dal 2022.
  • CHECKSUM — verifica le pagine mentre vengono salvate; cattura la corruzione prima.
  • STATS = 10 — stampa il progresso ogni 10%.

Pianificazione tipica di Runehold: un backup FULL notturno intorno alle 02:00 UTC. Impiega circa 25 minuti.

Backup DIFFERENTIAL

Tutto ciò che è cambiato dall’ultimo FULL. Più piccolo e veloce di un FULL, ma dipende dalla disponibilità del FULL.

BACKUP DATABASE Runehold
TO DISK = N'D:\Backups\Runehold_DIFF_20260315_12.bak'
WITH DIFFERENTIAL, FORMAT, INIT, COMPRESSION, CHECKSUM;

Pattern comune: FULL notturno, DIFF ogni 6 ore. Il restore coinvolge FULL + l’ultimo DIFF — due file invece di tanti.

Backup LOG

Tutti i record di log dall’ultimo backup del log. Essenziale per il point-in-time recovery e per evitare che il log file cresca senza limiti nel recovery model FULL.

BACKUP LOG Runehold
TO DISK = N'D:\Backups\Runehold_LOG_20260315_1245.trn'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM;

Pianificazione tipica: ogni 15 minuti durante l’orario lavorativo. Significa che se il database va giù, perdi al massimo 15 minuti di cambiamenti (RPO — Recovery Point Objective = 15 min).

Recovery model, di nuovo

Dalla lezione 30:

  • SIMPLE — niente backup del log possibili. RPO = ultimo FULL o DIFF.
  • FULL — backup del log richiesti; point-in-time recovery; il log resta piccolo tra i backup.
  • BULK_LOGGED — di nicchia.

Regola: ogni database di produzione che contiene dati di cui piangeresti la perdita dovrebbe essere in recovery FULL con backup del log regolari. Tutto il resto — DW di reporting, dev, test, scratch del data lake — può essere SIMPLE.

Restore: l’esercitazione vera

Non puoi dire di avere backup se non hai fatto un restore. Ecco l’esercitazione che ogni team dovrebbe fare almeno trimestralmente.

Step 1: ripristina l’ultimo FULL

RESTORE DATABASE Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_FULL_20260315.bak'
WITH
    MOVE N'Runehold'     TO N'E:\RestoreTest\Runehold.mdf',
    MOVE N'Runehold_log' TO N'E:\RestoreTest\Runehold_log.ldf',
    NORECOVERY,
    REPLACE,
    STATS = 5;
  • MOVE — perché stai ripristinando in una posizione diversa (o macchina diversa) con path diversi. Senza MOVE, SQL Server prova a mettere i file dove erano originalmente, il che fallisce se il path non esiste.
  • NORECOVERY — lascia il database in stato “restoring”; pronto ad applicare altri backup.
  • REPLACE — sovrascrivi se esiste un database con lo stesso nome (raro, ma utile).

Step 2: applica l’ultimo DIFF

RESTORE DATABASE Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_DIFF_20260315_12.bak'
WITH NORECOVERY;

Step 3: applica i backup del log fino al punto temporale desiderato

RESTORE LOG Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_LOG_20260315_1215.trn'
WITH NORECOVERY;

RESTORE LOG Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_LOG_20260315_1230.trn'
WITH NORECOVERY;

-- Fermati a un momento specifico
RESTORE LOG Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_LOG_20260315_1245.trn'
WITH STOPAT = N'2026-03-15 12:38:42', NORECOVERY;

Step 4: porta il database online

RESTORE DATABASE Runehold_Recovery WITH RECOVERY;

Step 5: verifica

Connettiti. Esegui query. Confronta i conteggi delle righe con quello che ti aspettavi. Prendi una manciata di tabelle e verifica che righe campione sembrino giuste.

Step 6: documenta cosa hai fatto e quanto è durato

L’RTO — Recovery Time Objective — è il downtime massimo accettabile. Se la tua esercitazione di restore ha impiegato 45 minuti, il tuo RTO è 45 minuti. Se la dirigenza si aspetta 15 minuti, hai un gap e devi lavorarci sopra.

Fai questa esercitazione ogni trimestre. Ruota chi la guida così più persone conoscono i passi.

Trovare e applicare la giusta catena di log

Quando il disastro colpisce, devi sapere ogni backup nella catena. Script veloce:

-- Storico FULL + DIFF + LOG per un database
WITH history AS (
    SELECT bs.database_name,
           bs.type,
           bs.backup_start_date,
           bs.backup_finish_date,
           bmf.physical_device_name
    FROM msdb.dbo.backupset AS bs
    JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bs.media_set_id
    WHERE bs.database_name = 'Runehold'
      AND bs.backup_start_date >= DATEADD(DAY, -7, GETDATE())
)
SELECT * FROM history ORDER BY backup_start_date;

Mostra ogni backup che SQL Server Agent ha registrato. Comodo durante un disastro.

Verificare i backup prima del disastro

RESTORE VERIFYONLY controlla un file di backup per corruzione di base senza ripristinarlo davvero:

RESTORE VERIFYONLY FROM DISK = N'D:\Backups\Runehold_FULL_20260315.bak';

Veloce, economico, sicuro. Ogni job di backup dovrebbe eseguire RESTORE VERIFYONLY immediatamente dopo il completamento del backup. Cattura backup corrotti prima che ti servano.

Inoltre: periodicamente fai un test completo di restore (l’esercitazione qui sopra) per catturare problemi che VERIFYONLY non vede. Ogni qualche mese.

Backup su Azure Blob o S3

SQL Server moderno può fare backup direttamente su Azure Blob Storage (e con qualche tooling, su S3). Copie off-site senza alcuno scripting di copia file.

BACKUP DATABASE Runehold
TO URL = N'https://runehold.blob.core.windows.net/backups/Runehold_20260315.bak'
WITH CREDENTIAL = N'AzureStorageCredential',
     COMPRESSION, CHECKSUM, FORMAT, INIT;

La CREDENTIAL è un oggetto SQL Server che contiene il SAS token Azure. Si configura una volta, si usa per sempre.

Per le aziende UE come Runehold, i backup off-site tipicamente vivono in una regione Azure solo-UE o in un bucket S3 solo-UE. La conformità sulla data residency conta.

GDPR, backup, e il “diritto all’oblio”

Una questione scomoda. Quando un cliente esercita il suo diritto GDPR alla cancellazione, devi eliminare i suoi dati. Facile nelle tabelle live. Non facile nei backup — un backup fatto ieri contiene la riga che dovresti cancellare oggi.

Approccio standard alla compliance: documenta nella tua privacy policy che i backup sono conservati per un periodo definito (spesso 30-90 giorni), e la cancellazione si applica ai dati live immediatamente più nei backup man mano che i vecchi backup escono dalla finestra di retention. Non modifichi i vecchi backup; li ruoti via.

Un’analisi legale completa è fuori scopo qui. Discuti con chi gestisce la funzione di DPO (Data Protection Officer) della tua azienda. Sii solo consapevole che l’intersezione tra backup e GDPR ha risposte documentate; non inventartene di tue.

Esegui questo sulla tua macchina

USE master;
GO

-- 1. Fai un backup completo sul tuo disco
BACKUP DATABASE Runehold
TO DISK = N'C:\Temp\Runehold_FULL.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;

-- 2. Verificalo
RESTORE VERIFYONLY FROM DISK = N'C:\Temp\Runehold_FULL.bak';

-- 3. Fai backup del log (richiede recovery FULL)
ALTER DATABASE Runehold SET RECOVERY FULL;
BACKUP DATABASE Runehold TO DISK = N'C:\Temp\Runehold_FULL.bak' WITH FORMAT, INIT;
BACKUP LOG Runehold TO DISK = N'C:\Temp\Runehold_LOG.trn' WITH FORMAT, INIT;

-- 4. Ripristina con un nuovo nome (l'esercitazione)
RESTORE DATABASE Runehold_Recovery
FROM DISK = N'C:\Temp\Runehold_FULL.bak'
WITH
    MOVE N'Runehold'     TO N'C:\Temp\Runehold_R.mdf',
    MOVE N'Runehold_log' TO N'C:\Temp\Runehold_R.ldf',
    NORECOVERY, REPLACE;

RESTORE LOG Runehold_Recovery
FROM DISK = N'C:\Temp\Runehold_LOG.trn'
WITH RECOVERY;

-- 5. Verifica il database ripristinato
SELECT COUNT(*) FROM Runehold_Recovery.Sales.Customer;
SELECT COUNT(*) FROM Runehold.Sales.Customer;
-- Dovrebbero coincidere.

-- 6. Elimina il restore di test
DROP DATABASE Runehold_Recovery;

Eseguilo da capo a coda una volta. Fanne memoria muscolare. Il giorno in cui dovrai farlo in produzione non sarà una bella giornata, ma sarà una giornata veloce.

Prossimo: SQL Server Agent — come schedulare i backup di cui sopra, più tutta l’altra roba ripetibile che un DBA automatizza.

Cerca