Backup & Restore

1. Waarom Backups?

Dataverlies kan optreden door software-fouten, menselijke fouten (van de DBA zelf), system crashes, diskproblemen, rampen of diefstal. Kortom: alles.

Server backup ≠ database backup

Zelfs als de serverbeheerder al backups neemt, zijn aparte databasebackups noodzakelijk. Applicatiefouten en menselijke fouten vallen buiten het bereik van serverbackups.

2. Welke Databases Backuppen?

System databases

Database	Wanneer backuppen
`master`	Regelmatig — voor engine upgrade, na toevoegen nieuwe database
`msdb`	Regelmatig — bevat job- en alerthistorie
`model`	Na elke wijziging

Productiedatabases

Zeker na: - Toevoegen nieuwe database - Aanmaken van indexes - Uitvoeren van niet-gelogde procedures

Zeker voor: - Aanpassen van de database-structuur - Verwijderen of leegmaken van de transaction log

3. Physical vs Logical Backup

Type	Beschrijving	Voordelen
Physical	Kopieert onderliggende databestanden (.mdf, .ldf)	Sneller, compacter
Logical	Schrijft SQL-statements (CREATE, INSERT) om database te reconstrueren	Compatibel tussen DBMS-versies, flexibeler

4. Backup Types

Type	Bevat	Gebruik
Full	Alle data, structuur + transaction log — status op moment van backup	Basis voor alle andere backups — verplicht startpunt
Differential	Alle gewijzigde data tov de laatste full backup	Sneller dan full, kleiner bestand
Transaction log	Historiek van wijzigingen (INSERT/UPDATE/DELETE) since laatste log backup	Point-in-time recovery
Tail log	Laatste deel van de transaction log — vóór restore	Voorkomt dataverlies net voor restore
Copy-only	Volledige backup zonder invloed op backupsequentie	Eenmalige extractie
File/filegroup	Individueel bestand of filegroup	Grote databases
Partial	Primaire filegroup + read-write filegroups	Gedeeltelijke backup

5. Recovery Modellen

Model	Log backups	Dataverliesrisico	Point-in-time recovery
simple	Niet mogelijk	Wijzigingen na laatste backup	Enkel tot einde van backup
full	Vereist	Normaal geen	Ja — tot specifiek moment
bulk-logged	Vereist	Bulk-operaties mogelijk niet gedekt	Tot einde van elk backup

-- Recovery model instellen
ALTER DATABASE ClassicModels SET RECOVERY FULL;
GO

6. Backup Schema

Een typisch schema combineert meerdere backup types:

Wekelijks:  Full backup          (10 GB basis)
Dagelijks:  Differential backup  (groeit op door de week)
Elk uur:    Transaction log backup

Frequentie: wekelijks | dagelijks | elk uur | continu
Retention: hoe lang bijhouden

7. Full Backup

Neemt alle informatie mee: structuur, data én transaction log.

Via SSMSVia T-SQL

Object Explorer → rechtermuisklik op database → Tasks → Back Up...

Stel in: Backup type = Full, Destination = Disk, bestandspad.

BACKUP DATABASE [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.bak'
    WITH NOFORMAT, INIT;
GO

8. Differential Backup

Neemt enkel de data die gewijzigd is tov de laatste full backup.

Via SSMSVia T-SQL

Object Explorer → rechtermuisklik op database → Tasks → Back Up...

Stel in: Backup type = Differential

BACKUP DATABASE [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.bak'
    WITH DIFFERENTIAL,
         NAME = N'ClassicModels-Full Database Backup';
GO

Note

Differential backup beperken in grootte — bij te grote bestanden geen snelheidswinst meer.

9. Transaction Log Backup

Enkel mogelijk bij full of bulk-logged recovery model.

Bewaart historiek van alle INSERT/UPDATE/DELETE die nog niet in een eerdere log backup zaten
Maakt point-in-time recovery mogelijk

Via SSMSVia T-SQL

Back Up... → Backup type = Transaction Log

Destination: .trn bestand

BACKUP LOG [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.trn'
    WITH NAME = N'ClassicModels-Full Database Backup';
GO

10. Backup Devices

Een backup device is een named alias voor een fysiek backupbestand.

Via SSMSAanmaken via T-SQLGebruikenVerwijderen

Object Explorer → Server Objects → rechtermuisklik op Backup Devices → New Backup Device...

Stel in: Device name, File path.

USE [master];
GO

EXEC sp_addumpdevice
    @devtype      = N'disk',
    @logicalname  = N'localDiskBackup',
    @physicalname = N'D:\Microsoft SQL Server\Backup\localDiskBackup.bak';
GO

USE [master];
GO

BACKUP DATABASE [ClassicModels]
    TO CM_Device;
GO

USE [master];
GO

EXEC sp_dropdevice @logicalname = 'localDiskBackup';
GO

11. Database Recovery

Automatic Recovery

Wordt automatisch uitgevoerd bij (her)starten van de SQL Server service:

Elke database wordt gecontroleerd op logische datatoestand obv de transaction log
Niet-voltooide transacties (uncommitted) worden teruggedraaid
Voltooide maar niet-doorgeschreven transacties worden opnieuw uitgevoerd

Manual Recovery

Bewust de database overschrijven met data vanuit een backup:

Steeds vertrekken van een full backup (overschrijft fysieke bestanden, maakt alle tabellen opnieuw)
Aanvullen met differential backup (indien aanwezig)
Aanvullen met transaction log backup(s)

12. Database Restore

Via SSMSVia T-SQL

Object Explorer → rechtermuisklik op database → Tasks → Restore → Database...

Source: Database
Destination: Database + eventueel Timeline voor point-in-time
Backup sets to restore: selecteer welke sets

USE [master];
GO

-- 1. Tail log backup maken (laatste log vóór restore)
BACKUP LOG [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels_LogBackup_2021-09-18_11-02-25.trn'
    WITH NAME = N'ClassicModels_LogBackup_2021-09-18_11-02-25';

-- 2. Full backup restoren (NORECOVERY = nog niet klaar)
RESTORE DATABASE [ClassicModels]
    FROM DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.bak'
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5;

-- 3. Transaction log restoren (NOUNLOAD = database blijft online na restore)
RESTORE LOG [ClassicModels]
    FROM DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.trn'
    WITH FILE = 1, NOUNLOAD, STATS = 5;
GO

NORECOVERY vs RECOVERY

Gebruik NORECOVERY zolang je nog extra backups wil toepassen. Gebruik RECOVERY (of laat weg) bij de laatste restore-stap — dan wordt de database operationeel gezet.