SQL Server Express Backup & Restore (DB_Keyvi)

Überblick

Diese Dokumentation beschreibt das produktive Backup- und Restore-Setup für:

• VM: VM11-01-Keyvi
• SQL-Instanz: .\KEYVI
• Datenbank: DB_Keyvi
• Backup-Ziel: D:\SQLBackup\DB_Keyvi
• Backup-Script: C:\Script\Backup_DB_Keyvi.ps1
• Restore-Script: C:\Script\Restore_DB_Keyvi.ps1
• Geplanter Task: Backup DB_Keyvi Daily

---

Hintergrund / Besonderheiten

SQL Server Express und Compression

Auf dieser SQL Server Express Edition wird WITH COMPRESSION nicht unterstützt.
Backups laufen daher ohne Kompression.

TLS/ODBC 18 (sqlcmd)

Auf dem System wird ODBC Driver 18 verwendet.
Daher wird sqlcmd mit -C genutzt (Zertifikat vertrauen), um Verbindungsfehler zu vermeiden.

---

Zielarchitektur

Backup

• Tägliches Backup per Windows Aufgabenplanung
• Dateiname mit Zeitstempel
• Ordnerstruktur nach Jahr/Monat (YYYY\MM)
• Aufbewahrung: 365 Tage
• Automatisches Löschen alter .bak-Dateien
• Automatisches Löschen leerer Ordner nach Cleanup

Restore

• Ein Restore-Script mit Modusumschaltung:
  • Test → Restore nach DB_Keyvi_RestoreTest
  • Production → Restore nach DB_Keyvi (mit Sicherheitsabfrage)

---

Rechte- und Sicherheitsmodell

Windows-Benutzer

• Laufender Benutzer für Task: VM11-01-Keyvi\Keyvi

SQL-Rechte

Für Backup erforderlich:

• DB-Rolle: db_backupoperator auf DB_Keyvi

Für Test-Restore auf neue DB erforderlich:

• Serverrolle: dbcreator (oder alternativ sysadmin)

NTFS-Rechte auf Backup-Pfad

Auf D:\SQLBackup\DB_Keyvi benötigen Schreibrechte:

• VM11-01-Keyvi\Keyvi
• NT Service\MSSQL$KEYVI (SQL-Dienstkonto)

---

Verzeichnisstruktur

Beispiel nach einigen Läufen:

D:\SQLBackup\DB_Keyvi\
└── 2026\
    └── 07\
        ├── DB_Keyvi_20260729_205555.bak
        ├── DB_Keyvi_20260730_020000.bak
        └── ...

---

Script 1: Backup (C:\Script\Backup_DB_Keyvi.ps1)

Dieses Script:

1. Erzeugt Zielordner YYYY\MM
2. Führt Backup aus
3. Löscht .bak älter als 365 Tage
4. Löscht leere Ordner rekursiv

Dieses Script wird täglich über den Scheduled Task ausgeführt.

---

Script 2: Restore (C:\Script\Restore_DB_Keyvi.ps1)

Dieses Script:

1. Nutzt eine fest konfigurierte Backup-Datei ($BackupFile)
2. Nutzt einen Modus ($Mode = Test|Production)
3. Liest logische Dateinamen aus dem Backup (T-SQL intern)
4. Stellt Datenbank mit MOVE ... REPLACE ... RECOVERY wieder her

Restore-Modi

• Testmodus

  • Ziel-DB: DB_Keyvi_RestoreTest
  • Keine Produktionsüberschreibung

• Production-Modus

  • Ziel-DB: DB_Keyvi
  • Sicherheitsabfrage (JA) vor Überschreiben

---

Installation / Einrichtung (einmalig)

1) SQL-Login/Benutzer vorbereiten (falls noch nicht vorhanden)

-- Als sysadmin in SSMS ausführen
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'VM11-01-Keyvi\Keyvi')
    CREATE LOGIN [VM11-01-Keyvi\Keyvi] FROM WINDOWS;
GO

USE [DB_Keyvi];
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'VM11-01-Keyvi\Keyvi')
    CREATE USER [VM11-01-Keyvi\Keyvi] FOR LOGIN [VM11-01-Keyvi\Keyvi];
GO

ALTER ROLE [db_backupoperator] ADD MEMBER [VM11-01-Keyvi\Keyvi];
GO

Für Test-Restore auf neue DB zusätzlich:

ALTER SERVER ROLE [dbcreator] ADD MEMBER [VM11-01-Keyvi\Keyvi];
GO

2) NTFS-Rechte setzen

$BackupDir = "D:\SQLBackup\DB_Keyvi"

New-Item -ItemType Directory -Path $BackupDir -Force | Out-Null
icacls $BackupDir /grant "VM11-01-Keyvi\Keyvi:(OI)(CI)M" /T
icacls $BackupDir /grant "NT Service\MSSQL`$KEYVI:(OI)(CI)M" /T

3) Scheduled Task erstellen/anpassen

Action:

powershell.exe -NoProfile -ExecutionPolicy Bypass -File "C:\Script\Backup_DB_Keyvi.ps1"

Task läuft als:

VM11-01-Keyvi\Keyvi

---

Betrieb

Backup manuell testen

powershell -ExecutionPolicy Bypass -File "C:\Script\Backup_DB_Keyvi.ps1"

Task manuell starten

Start-ScheduledTask -TaskName "Backup DB_Keyvi Daily"

Task-Status prüfen

Get-ScheduledTaskInfo -TaskName "Backup DB_Keyvi Daily" | Select-Object LastRunTime,LastTaskResult,NextRunTime

LastTaskResult = 0 bedeutet Erfolg.

Letzte Backups anzeigen

Get-ChildItem "D:\SQLBackup\DB_Keyvi" -Recurse -File -Filter *.bak |
  Sort-Object LastWriteTime -Descending |
  Select-Object -First 20 FullName, LastWriteTime, Length

---

Restore ausführen

Restore-Script konfigurieren

Im Script C:\Script\Restore_DB_Keyvi.ps1:

• $Mode = "Test" oder "Production"
• $BackupFile = "D:\SQLBackup\DB_Keyvi\2026\07\DB_Keyvi_20260729_205555.bak"

Restore starten

powershell -ExecutionPolicy Bypass -File "C:\Script\Restore_DB_Keyvi.ps1"

Ergebnis prüfen (Beispiel Test-DB)

SELECT name, state_desc
FROM sys.databases
WHERE name IN ('DB_Keyvi','DB_Keyvi_RestoreTest');

---

Troubleshooting

Fehler: WITH COMPRESSION wird ... nicht unterstützt

Ursache: SQL Server Express unterstützt in dieser Edition keine Backup-Kompression.
Lösung: COMPRESSION entfernen.

Fehler: Zertifikatkette nicht vertrauenswürdig (ODBC 18)

Ursache: TLS-Zertifikat nicht vertrauenswürdig.
Lösung: sqlcmd mit -C ausführen.

Fehler: CREATE DATABASE-Berechtigung ... verweigert

Ursache: Benutzer hat keine Rechte für neue Datenbank (Test-Restore).
Lösung: ALTER SERVER ROLE [dbcreator] ADD MEMBER [VM11-01-Keyvi\Keyvi];

Fehler: Zugriff auf Backup-Ordner

Ursache: NTFS-Rechte fehlen für SQL-Dienstkonto.
Lösung: Rechte auf NT Service\MSSQL$KEYVI setzen.

---

Sicherheits-Hinweise

• Produktions-Restore nur bewusst im Production-Modus ausführen.
• Vor produktivem Restore Benutzer informieren (Downtime möglich).
• Regelmäßig Test-Restore durchführen (z. B. monatlich), um Restore-Fähigkeit sicherzustellen.
• Zugriff auf Script- und Backup-Ordner auf notwendige Benutzer beschränken.

---

Empfohlene Ergänzungen (optional)

• Logging in Datei (Backup/Restore-Laufzeit, Exitcode)
• Benachrichtigung bei Fehler (E-Mail/EventLog)
• Verschlüsselung/Signierung von Backup-Archiven (falls Compliance-relevant)
• Offsite-Kopie (z. B. getrenntes Storage, immutable backup target)

---

Versionshistorie

• 2026-07-29: Erstaufbau Backup-Automation
• 2026-07-29: Restore-Script mit Test/Production-Modus ergänzt
• 2026-07-29: Retention (365 Tage) + Cleanup leerer Ordner umgesetzt