⚙️ ALTER DATABASE – Die wichtigsten Befehle im Überblick
Datenbankoptionen, Dateiverwaltung, Kompatibilitätslevel, Sicherheit, Hochverfügbarkeit – alles mit Praxisbeispielen
📌 Was ist ALTER DATABASE?
GRUNDLAGEN
Der Befehl
ALTER DATABASE ist eines der mächtigsten Werkzeuge in SQL Server. Mit ihm ändern Sie die Eigenschaften einer Datenbank – von grundlegenden Zuständen (online/offline, read‑only) über Dateiverwaltung bis hin zu Sicherheits- und Leistungsoptionen. Die Syntax ist umfangreich, aber gut strukturiert. In diesem Artikel finden Sie die wichtigsten Varianten mit Erläuterungen und Best Practices.
💡 Hinweis: Viele Änderungen erfordern, dass niemand sonst die Datenbank verwendet. Planen Sie solche Operationen in Wartungsfenstern. Einige Optionen können online geändert werden, andere nicht.
🔄 Datenbankzustand ändern
ZUSTAND
Diese Optionen sind die am häufigsten genutzten – sie steuern, ob eine Datenbank verfügbar ist und wer sie nutzen kann.
-- Datenbank offline nehmen (für Wartungsarbeiten, Verschiebung von Dateien)
ALTER DATABASE [MeineDB] SET OFFLINE;
-- Datenbank wieder online schalten
ALTER DATABASE [MeineDB] SET ONLINE;
-- Datenbank auf schreibgeschützt setzen (Reporting, Archiv)
ALTER DATABASE [MeineDB] SET READ_ONLY;
-- Wieder beschreibbar machen
ALTER DATABASE [MeineDB] SET READ_WRITE;
-- Nur ein Benutzer darf gleichzeitig verbinden (für exklusive Wartung)
ALTER DATABASE [MeineDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Wieder für mehrere Benutzer freigeben
ALTER DATABASE [MeineDB] SET MULTI_USER;
-- Eingeschränkter Benutzermodus (nur Mitglieder der Rolle db_owner, dbcreator, sysadmin)
ALTER DATABASE [MeineDB] SET RESTRICTED_USER;
⚠️ Achtung:
SET SINGLE_USER WITH ROLLBACK IMMEDIATE bricht alle aktiven Verbindungen sofort ab. Nutzen Sie dies nur in Notfällen oder kontrollierten Wartungsfenstern.
📁 Dateiverwaltung – Pfade, Größen, neue Dateien
DATEIEN
Mit
ALTER DATABASE steuern Sie die physischen Dateien der Datenbank: Sie können vorhandene Dateien verschieben/umbenennen, neue hinzufügen oder alte entfernen.
-- Datei (Daten oder Log) in der Metadaten umleiten (physikalischer Pfad ändert sich erst nach manueller Verschiebung)
ALTER DATABASE [MeineDB]
MODIFY FILE (
NAME = 'MeineDB_Data',
FILENAME = 'D:\Data\MeineDB_New.mdf'
);
-- Neue sekundäre Datendatei (NDF) hinzufügen
ALTER DATABASE [MeineDB]
ADD FILE (
NAME = 'MeineDB_Data2',
FILENAME = 'E:\Data\MeineDB_Data2.ndf',
SIZE = 1GB,
MAXSIZE = 10GB,
FILEGROWTH = 512MB
);
-- Neue Log-Datei hinzufügen
ALTER DATABASE [MeineDB]
ADD LOG FILE (
NAME = 'MeineDB_Log2',
FILENAME = 'F:\Logs\MeineDB_Log2.ldf'
);
-- Eine Datei entfernen (nur möglich, wenn sie keine Daten mehr enthält, z. B. nach DBCC SHRINKFILE EMPTYFILE)
ALTER DATABASE [MeineDB]
REMOVE FILE [MeineDB_Data2];
💡 Best Practice: Verwenden Sie mehrere Datendateien für TempDB und große Benutzerdatenbanken, um Kontention zu reduzieren. Nutzen Sie
FILEGROWTH in Megabyte (nicht Prozent), um häufige Wachstumsschübe zu vermeiden.
📜 Wiederherstellungsmodell (Recovery Model)
PROTOKOLL
Das Wiederherstellungsmodell bestimmt, wie SQL Server das Transaktionsprotokoll verwaltet und welche Point-in-Time-Wiederherstellung möglich ist.
-- Vollständiges Wiederherstellungsmodell (für Produktion, mit regelmäßigen Log-Backups)
ALTER DATABASE [MeineDB] SET RECOVERY FULL;
-- Einfaches Wiederherstellungsmodell (Log wird bei Checkpoints automatisch abgeschnitten, keine Point-in-Time)
ALTER DATABASE [MeineDB] SET RECOVERY SIMPLE;
-- Massenprotokollierung (für große Ladevorgänge, reduziert Logging)
ALTER DATABASE [MeineDB] SET RECOVERY BULK_LOGGED;
📌 Wichtig: Wechseln Sie nur nach einem vollständigen Backup von SIMPLE zu FULL. Ohne regelmäßige Log-Backups wächst das Transaktionslog ins Unendliche.
🔢 Kompatibilitätslevel (COMPATIBILITY_LEVEL)
UPGRADE
Das Kompatibilitätslevel steuert, welche Optimierungen und Syntaxen der Abfrageoptimierer verwenden darf. Es wird beim Upgrade einer Datenbank auf eine neue SQL Server-Version nicht automatisch erhöht.
-- Aktuelles Level abfragen
SELECT name, compatibility_level FROM sys.databases;
-- Auf SQL Server 2022 stellen (Level 160)
ALTER DATABASE [MeineDB] SET COMPATIBILITY_LEVEL = 160;
-- Auf SQL Server 2019 (150), 2017 (140), 2016 (130), 2014 (120) ...
ALTER DATABASE [MeineDB] SET COMPATIBILITY_LEVEL = 150;
💡 Best Practice: Testen Sie neue Kompatibilitätslevel zuerst in einer Nicht-Produktionsumgebung mit Query Store. Meist bringt ein höheres Level Performancevorteile (z. B. bessere Kardinalitätsschätzung).
⚡ Wichtige Datenbankoptionen für Performance und Wartung
OPTIONEN
SQL Server bietet zahlreiche Datenbankoptionen, die das Laufzeitverhalten beeinflussen. Hier die wichtigsten:
-- Automatisches Schließen (AUTO_CLOSE) – Vorsicht! Nur für Desktop-Datenbanken
ALTER DATABASE [MeineDB] SET AUTO_CLOSE OFF; -- Standard für Produktion
-- Automatisches Verkleinern (AUTO_SHRINK) – generell nicht empfehlenswert!
ALTER DATABASE [MeineDB] SET AUTO_SHRINK OFF; -- Deaktivieren!
-- Automatische Statistikaktualisierung (sollte immer ON sein)
ALTER DATABASE [MeineDB] SET AUTO_UPDATE_STATISTICS ON;
-- Asynchrone Statistikaktualisierung (reduziert Auswirkungen auf laufende Abfragen)
ALTER DATABASE [MeineDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
-- Seitenüberprüfung (CHECKSUM, TORN_PAGE_DETECTION, NONE)
ALTER DATABASE [MeineDB] SET PAGE_VERIFY CHECKSUM;
-- Parameter sniffing deaktivieren (Vorsicht, kann zu schlechteren Plänen führen)
ALTER DATABASE [MeineDB] SET PARAMETERIZATION FORCED;
-- Datenbank auf Standard- und benutzerdefinierte Parameterisierung zurücksetzen
ALTER DATABASE [MeineDB] SET PARAMETERIZATION SIMPLE;
🚨 Warnung:
AUTO_SHRINK verursacht Fragmentierung und Performanceprobleme. Lassen Sie es deaktiviert. Auch AUTO_CLOSE ist für produktive Datenbanken ungeeignet, da es den Pufferpool ständig leert.
🔒 Transparent Data Encryption (TDE) aktivieren/deaktivieren
SICHERHEIT
TDE verschlüsselt die Datenbankdateien auf Festplattenebene, ohne dass Anwendungen geändert werden müssen. Voraussetzung ist ein Datenbankverschlüsselungsschlüssel (DEK).
-- TDE für eine Datenbank aktivieren
ALTER DATABASE [MeineDB] SET ENCRYPTION ON;
-- TDE deaktivieren
ALTER DATABASE [MeineDB] SET ENCRYPTION OFF;
📌 Wichtig: TDE verursacht eine geringe CPU-Last (ca. 3-5 %). Sichern Sie immer den Verschlüsselungsschlüssel (Certificate) im Master, sonst sind Ihre Daten unwiederbringlich verloren.
📊 Query Store – Performanceüberwachung aktivieren
QUERY PERFORMANCE
Der Query Store zeichnet Ausführungspläne und Laufzeitstatistiken auf und ist unabdingbar für die Erkennung von Planregressionen. Ab SQL Server 2016 verfügbar.
-- Query Store aktivieren
ALTER DATABASE [MeineDB] SET QUERY_STORE = ON;
-- Wichtige Query Store-Parameter (optional)
ALTER DATABASE [MeineDB] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 1024
);
-- Query Store deaktivieren (nicht empfehlenswert)
ALTER DATABASE [MeineDB] SET QUERY_STORE = OFF;
💡 Best Practice: Aktivieren Sie Query Store auf allen Produktionsdatenbanken. Der Overhead ist minimal, die Diagnosemöglichkeiten sind enorm.
🔄 Hochverfügbarkeit und Always On
HADR
Für Always On Availability Groups (AG) gibt es spezielle
ALTER DATABASE-Befehle, um Datenbanken in eine AG aufzunehmen oder aus ihr zu entfernen.
-- Datenbank für die Verwendung mit Always On vorbereiten (vollständiges Backup erforderlich)
ALTER DATABASE [MeineDB] SET HADR OFF;
-- Datenbank einer Verfügbarkeitsgruppe hinzufügen (auf dem primären Replikat)
ALTER DATABASE [MeineDB] SET HADR AVAILABILITY GROUP = [AG_Name];
-- Datenbank aus der AG entfernen (auf dem primären Replikat)
ALTER DATABASE [MeineDB] SET HADR OFF;
💡 Hinweis: Diese Befehle werden normalerweise über den AG-Assistenten oder T‑SQL-Skripte gesteuert. Direktes
SET HADR erfordert entsprechende Berechtigungen.
🌐 Kollation (Collation) ändern
SPEZIAL
Das Ändern der Datenbankkollation ist mit
ALTER DATABASE möglich, betrifft aber nur neue Objekte. Bestehende Spalten müssen separat umgestellt werden.
-- Kollation der Datenbank ändern
ALTER DATABASE [MeineDB] COLLATE Latin1_General_100_CI_AS_SC;
-- Datenbank umbenennen (natürlich auch mit ALTER DATABASE möglich – selten genutzt)
ALTER DATABASE [MeineDB] MODIFY NAME = [MeineNeueDB];
⚠️ Warnung: Die Änderung der Kollation kann zu Konflikten bei Vergleichsoperationen führen. Planen Sie dies sorgfältig.
✅ Best Practices für den Einsatz von ALTER DATABASE
CHECKLISTE
- Immer aktuelles Backup erstellen, bevor Sie kritische Änderungen durchführen.
- Verwenden Sie ein Wartungsfenster für
SET OFFLINEoderMODIFY FILE. - Testen Sie Änderungen zuerst auf einer Testumgebung – besonders Kompatibilitätslevel, Kollation, TDE.
- Vermeiden Sie
AUTO_SHRINKundAUTO_CLOSEin Produktionsumgebungen. - Aktivieren Sie Query Store auf allen Datenbanken, um Performanceprobleme zu erkennen.
- Dokumentieren Sie Änderungen – protokollieren Sie, wer wann welche ALTER DATABASE-Befehle ausgeführt hat.
- Nutzen Sie die Windows-Ereignisanzeige und das SQL Server-Protokoll zur Fehlersuche, falls eine Änderung fehlschlägt.
📋 Kurzreferenz: Häufige ALTER DATABASE-Klauseln
CHEAT SHEET
| Kategorie | Befehl (Beispiel) | Erläuterung |
|---|---|---|
| Zustand | SET OFFLINE / ONLINE | Datenbank für Wartung sperren / freigeben |
| Zugriff | SET READ_ONLY / READ_WRITE | Schreibschutz ein-/ausschalten |
| Benutzermodus | SET SINGLE_USER / MULTI_USER / RESTRICTED_USER | Zahl der gleichzeitigen Verbindungen beschränken |
| Dateien | MODIFY FILE (NAME=..., FILENAME=...) | Pfad einer Datei ändern (Metadaten) |
| Dateien | ADD FILE (...) / ADD LOG FILE (...) | Neue Daten-/Logdatei hinzufügen |
| Dateien | REMOVE FILE [Dateiname] | Datei löschen (nach Leerung) |
| Recovery | SET RECOVERY FULL / SIMPLE / BULK_LOGGED | Wiederherstellungsmodell |
| Kompatibilität | SET COMPATIBILITY_LEVEL = 160 | SQL Server-Version des Optimierers |
| Performance | SET PAGE_VERIFY CHECKSUM | Seitenüberprüfung (empfohlen) |
| Performance | SET AUTO_UPDATE_STATISTICS ON | Automatische Statistiken (Standard) |
| Sicherheit | SET ENCRYPTION ON / OFF | TDE aktivieren/deaktivieren |
| Query Store | SET QUERY_STORE = ON | Leistungsüberwachung einschalten |
| HADR | SET HADR AVAILABILITY GROUP = ... | Datenbank einer AG zuordnen |
| Kollation | COLLATE Latin1_General_CI_AS | Standardsortierung ändern |
| Umbenennung | MODIFY NAME = [NeuerName] | Logischen Datenbanknamen ändern |
💡 Tipp: Speichern Sie diese Tabelle als schnelle Referenz. Die genaue Syntax finden Sie in der offiziellen Microsoft-Dokumentation.