Zuletzt aktualisiert am 25. Dezember 2023 von Lars
Dieser Artikel zu SQL Server 2000 ist schon sehr alt, wird aber noch erstaunlich oft aufgerufen. Vielleicht interessiert Sie auch mein Artikel zu MS SQL Server 2014 Express?
Medium einrichten
Im SQL Server Enterprise Manager Sicherung wählen - Rechte Maustaste - Neues Sicherungsmedium
Name für das Sicherungsmedium vergeben sowie ein Dateiname festlegen. Die Sicherung wird dann in die angegebene Datei installiert.
Alle erstellten Sicherungsmedien werden im Enterprise-Manager angezeigt.
MS-SQL Datenbank sichern
Wieder rechte Maustaste auf Sicherung
Datenbank sichern... wählen
Folgende Angaben müssen / können gemacht werden:
1) Datenbank
2) Name der Sicherung
3) Beschreibung (nicht zwingend nötig)
4) Art der Sicherung
- Datenbank – vollständig
Führt eine vollständige Datenbanksicherung durch. - Datenbank – differenziell
Führt eine differenzielle Datenbanksicherung durch. Bei einer differenziellen Datenbanksicherung werden nur die Änderungen an den Daten der Datenbank aufgezeichnet, die nach der letzten vollständigen Datenbanksicherung ausgeführt wurden. - Transaktionsprotokoll
Sichert das Transaktionsprotokoll. - Datei und Dateigruppe
Gibt den Namen und die Dateigruppe der Datei an, die gesichert werden soll. Klicken Sie auf die Schaltfläche mit den drei Punkten, um eine Datei oder Dateigruppe zu suchen.
Wenn Sie direkt auf Band sichern, können sie die entsprechende Option hier
auswählen.
5) Wählen Sie das Medium aus
6) Wählen Sie aus, ob alte Sicherungen überschrieben werden sollen oder
Sicherungen angehängt werden sollen
7) Hier können sie einen Zeitplan für die Sicherung festlegen (mehr dazu
weiter unten)
Registerkarte Optionen
1) Sicherung bei Abschluss überprüfen. Gibt an, dass nach Abschluss der Sicherung die Medienintegrität überprüft wird.
2) Nach der Sicherung Band auswerfen. Wirft das Bandsicherungsmedium aus, nachdem die Sicherung beendet ist.
3) Inaktive Einträge aus dem Transaktionsprotokoll entfernen. Entfernt nach Abschluss der Sicherung alle Einträge für abgeschlossene Transaktionen aus dem Transaktionsprotokoll.
4) Mediensatzname und Sicherungssatz-Ablaufdatum überprüfen. Überprüft den Mediensatznamen und das Sicherungssatz-Ablaufdatum, bevor die Medien überschrieben werden.
5) Mediensatzname - gibt den Mediensatznamen an. Diesen Namen müssen die Medien aufweisen, damit sie überschrieben werden können.
6) Sicherungssatz läuft ab. Legt die Bedingungen für das Sicherungssatz-Ablaufdatum fest. Microsoft® SQL Server™ verwendet lediglich die Sicherungsablaufinformationen des ersten Sicherungssatzes auf den Medien, um festzustellen, ob die Medien insgesamt überschrieben werden können.
7) nach -
Gibt die Anzahl der Tage an, die nach dem Abschluss der Sicherung vergehen müssen, bevor die Medien überschrieben werden können.
8) am -
Gibt das Datum an, an dem die Medien überschrieben werden können.
9) Medien initialisieren und bezeichnen -
Schreibt an den Anfang der Medien einen Header im Microsoft Tape Format (MSTF). Auf diese Weise werden der gesamte Inhalt sowie gegebenenfalls vorhandene Medienheaderinformationen gelöscht. Das Sicherungssatz-Ablaufdatum und der Mediensatzname werden bei der Initialisierung von Medien nicht überprüft.
10) Mediensatzname -
Schreibt den Mediennamen als Teil des MTF-Headers auf die Medien.
11) Mediensatzbeschreibung -
Schreibt die Medienbeschreibung als Teil des MTF-Headers auf die Medien. In der Regel enthält diese Beschreibung den Verwendungszweck der Medien oder ihren Ort.
MS-SQL Sicherung planen
Achtung - hierzu muss der SQL Server-Agent laufen! Am Besten unter
Systemsteuerung-Verwaltung-Dienste auf Startart "Automatisch" umstellen.
Aktivieren sie den Haken Zeitplan in der Maske SQL-Server Sicherung
Klicken Sie auf die Schaltfläche mit den 3 Punkten.
1) Name -
Gibt den Namen des Zeitplanes an. Die Länge ist auf 128 Zeichen beschränkt. In einem Auftrag darf jeder Zeitplanname nur einmal vorhanden sein.
2) Aktiviert -
Aktiviert den neuen Auftragszeitplan.
3) Automatisch starten, wenn der SQL Server-Agent startet -
Startet den Auftrag automatisch, wenn der SQL Server-Agent gestartet wird.
4) Starten, sobald die CPU(s) im Leerlauf ist (sind) -
Startet den Auftrag bei Leerlauf der CPU(s). Die Leerlaufzeit der CPU(s) wird auf der Registerkarte Erweitert des Dialogfeldes SQL Server-Agent-Eigenschaften angegeben.
5) Einmal -
Der Auftrag wird einmal am angegebenen Datum zur angegebenen Uhrzeit gestartet.
Am (Datum) - Gibt das Datum an, an dem der Auftrag gestartet werden soll.
Um (Uhrzeit) -
Gibt die Uhrzeit an, zu der der Auftrag gestartet werden soll.
Wiederholt
6) Der Auftrag wird entsprechend dem angezeigten Wiederholungszeitplan gestartet.
7) Ändern -
Zeigt das Dialogfeld Zeitplan für wiederkehrende Aufträge bearbeiten an, in dem Sie den aktuellen Zeitplan für wiederkehrende Aufträge ändern können.
Empfohlene Aktionen für Datenbanken, die als suspect oder corrupt gekennzeichnet sind
(Übersetzung von karaszi.com - Link nicht mehr aktiv + eigene Ergänzung)
Eine als suspect (fehlerverdächtig) oder corrupt markierte Datenbank wird hoffentlich sehr selten auftreten. Meistens ist eine fehlerhafte Hardware oder ein gelöschtes Transaction Log File daran schuld.
Mehr zum Fehler sollte im SQL-Log (zu finden im SQL-Server-Verzeichnis - Unterordner log) zu finden sein.
Die jeweils aktuellste Datei heisst ERRORLOG.
Hier steht zum Beispiel:
2005-02-07 20:27:23.24 server SQL Server überwacht 127.0.0.1: 1433. 2005-02-07 20:27:23.41 server SQL Server überwacht TCP, Shared Memory, Named Pipes. 2005-02-07 20:27:23.42 server SQL Server ist bereit für Clientverbindungen 2005-02-07 20:27:23.42 spid3 Servername ist 'SRV1'. 2005-02-07 20:27:23.43 spid8 Startet Datenbank 'msdb'. 2005-02-07 20:27:23.43 spid9 Startet Datenbank 'pubs'. 2005-02-07 20:27:23.43 spid10 Startet Datenbank 'Northwind'. 2005-02-07 20:27:23.44 spid11 Startet Datenbank 'Personal'. 2005-02-07 20:27:24.35 spid5 Löscht tempdb-Datenbank. 2005-02-07 20:27:24.84 spid9 Startet Datenbank 'Personalkap2'. 2005-02-07 20:27:24.85 spid10 Startet Datenbank 'test'. 2005-02-07 20:27:24.92 spid11 Startet Datenbank 'Kurzwahl'. 2005-02-07 20:27:25.23 spid9 Medienaktivierungsfehler. Der physische Dateiname 'e:ProgrammeMicrosoft SQL ServerMSSQLdataPersonalkap2_Log.LDF' ist möglicherweise falsch. 2005-02-07 20:27:26.59 spid5 Startet Datenbank 'tempdb'. 2005-02-07 20:27:26.86 spid3 Wiederherstellung abgeschlossen. 2005-02-07 20:27:29.71 spid51 'xpsqlbot.dll', Version '2000.80.194', wird verwendet, um die erweiterte gespeicherte Prozedur 'xp_qv' auszuführen.
Es fehlt also wahrscheinlich die korrekte Log-Datei.
Stellen Sie immer sicher, dass eine vernünftige Backup-Strategie vorhanden ist, die Ihnen sowohl bei Hardwareausfall als auch bei korrupten Datenbanken hilft. Es empfiehlt sich i. d. R. sowohl ein Backup der Datenbank als auch der Log-Dateien.
Falls möglich, lassen sie DBCC CHECKDB und DBCC CHECKCATALOG laufen.
Diese liefern einen Rückgabewert, nach dem sie in der Onlinehilfe oder im Internet suchen können. Mit etwas Glück finden Sie Empfehlungen zu Ihrer Fehlermeldung.
Unter Umständen hilft Ihnen die NO_INFOMSGS Option von DBCC, DBCC liefert dann nur Error-Meldungen zurück.
Finden sie heraus, was passiert ist. Überprüfen sie das Ereignisprotokoll (Start - Ausführen - "eventvwr"), die SQL-Server Protokolle, die Hardware. Sollte Hardware defekt sein, muss diese unbedingt ersetzt werden.
Führen sie ein Backup des Logfiles aus. Falls die database "suspect" ist, muss das Backup-Command mit der NO_TRUNCATE option ausgeführt werden.
Syntax MS SQL Backup Befehl
BACKUP
Legt eine Sicherung der gesamten Datenbank, des Transaktionsprotokolls oder einer oder mehrerer Dateien bzw. Dateigruppen an.
Syntax
Sichern der gesamten Datenbank:
BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
Sichern eines Transaktionsprotokolls:
BACKUP LOG { database_name | @database_name_var }
{
TO < backup_device > [ ,...n ]
[WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
}
NO_TRUNCATE
Ermöglicht es, das Protokoll in Situationen zu sichern, in denen die Datenbank beschädigt ist.
Beispiel:
BACKUP LOG personalkap2 TO medium1 with NO_TRUNCATE
Es empfiehlt sich außerdem ein Datei-Backup der mdf und ldf Dateien.
a) Ein Restore sollte immer die erste Wahl sein. Wenn im obigen Schritt ein Restore der Log-Datei noch geklappt hat, haben sie gute Chancen keinen Datenverlust zu haben. Stellen sie die letzte saubere Datenbank wieder her sowie die nachfolgenden Log-Files sowie das im letzten Schritt gesicherte.
b) Wenn die Datenbank nicht "suspect" ist, könnten Sie auch DBCC mit der REPAIR Option benutzen. Dies hat jedoch oft einen Datenverlust zur Folge.
c) Ist die Datenbank "suspect", kann die Datenbank unter Umständen mit sp_resetstatus in einen fehlerfreien Status zurückgebracht werden.
sp_resetstatus Setzt den Status einer fehlerverdächtigen Datenbank zurück. Syntax sp_resetstatus [ @DBName = ] 'database' Argumente [@DBName =] 'database' Der Name der zurückzusetzenden Datenbank. database ist vom Datentyp sysname und hat keinen Standard. Rückgabecodewerte 0 (Erfolg) oder 1 (Fehler)
Es könnte helfen, jedoch kann es auch sein, dass die Datenbank sofort wieder in den "Suspect" Status zurückfällt.
Es gibt auch noch einen "emergency mode", der als Panik-Status gesetzt werden kann und es in manchen Fällen erlaubt noch Daten aus einer zerstörten Datenbank zu bekommen.
Datenverlust minimieren
(Übersetzung von karaszi.com - Link nicht mehr aktiv + eigene Ergänzung)
Überblick
"Hilfe! Ich habe alle Zeilen in der Rechnungstabelle gelöscht! Kannst Du diese wieder zurück holen?"
Eine Chance besteht, wenn die Operation nicht innerhalb einer Transaktion gemacht wurde
Transaktionen sind Vorgänge, die nur als ganzes ablaufen. Praktisch jede Änderung an eine Datenbank kann in eine Transaktion eingebunden werden. Kann ein Schritt innerhalb einer Transaktion nicht erfolgreich abgeschlossen werden, werden alle anderen Änderungen ebenfalls verworfen.
Beispiel: Der Fehler ist um 15:45 passiert. Der User kommt zu Ihnen um 15:50. Ein Backup der Datenbank wird jede Nacht um 01:00 ausgeführt. Das Transaction Log
wird jede Stunde gesichert.
Zur Verfügung stehende Optionen:
1. Restore der letzten Datenbank
2. Restore der letzten Datenbank und aller nachfolgend gesicherten Log-Dateien
3. Backup des Log-Files; Restore der letzten Datenbank und aller nachfolgend gesicherten Log-Dateien einschließlich der gerade gesicherten
4. Restore in eine andere Datenbank und Herauskopieren der betroffenen Daten in die Produktions-Datenbank
5. Verwenden von Tools, die eine "undo" Operation aufgrund der Log-Files ausführen können
"Schmeissen" Sie auf jeden Fall erst einmal alle User aus der Datenbank, so dass keine weiteren Änderungen in der Datenbank vorgenommen werden können. In unserem Beispiel ist es danach 15:52.
1. Restore der letzten Datenbank
Es ist offensichtlich, dass sie mit dieser Aktion praktisch die gesamte Arbeit des Tages verlieren. Diese Option dürfte daher i. d. R. nicht in Frage kommen.
2. Restore der letzten Datenbank und aller nachfolgend gesicherten Log-Dateien
In diesem Fall verlieren sie die Arbeit bis 15:00 Uhr. Unter Umständen akzeptabel, aber es geht eventuell noch besser.
3. Backup des Log-Files; Restore der letzten Datenbank und aller nachfolgend gesicherten Log-Dateien einschliesslich der gerade gesicherten
- Führen Sie ein reguläres Backup der Log-Datei aus
- Führen Sie ein Restore der letzten Datenbank und aller nachfolgend
gesicherten Log-Dateien aus. Dabei müssen Sie STOPAT spezifizieren,
damit Sie die Datenbank nicht auf den Stand von 15:52 bringen
RESTORE
Stellt Sicherungen wieder her, die mit dem BACKUP-Befehl erstellt wurden. Weitere Informationen über das Sichern und Wiederherstellen von Datenbanken finden Sie unter Sichern und Wiederherstellen von Datenbanken.
Syntax
Wiederherstellen einer ganzen Datenbank:
RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ] [ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
STOPAT = date_time | @date_time_var
Gibt an, dass die Datenbank in dem Status wiederhergestellt wird, der zum Zeitpunkt des angegebenen Datums und der angegebenen Uhrzeit bestand. Falls eine Variable für STOPAT verwendet wird, muss die Variable den Datentyp varchar, char, smalldatetime oder datetime haben. Nur Transaktionsprotokolleinträge, die vor dem angegebenen Datum und der angegebenen Uhrzeit geschrieben wurden, werden auf die Datenbank angewendet.
- Was wäre aber zu tun, wenn nicht bekannt ist, zu welchem Zeitpunkt der Fehler aufgetreten ist?
- Stop-Zeit der Transaction-Log-Datei entnehmen:
Leider hat SQL Server kein Tool um die Log-Dateien direkt lesen zu können. Es gibt zwar die DBCC LOG und die fn_loginfo(), aber diese liefern keine lesbaren Informationen. Sie sind also auf 3:rd party Tools angewiesen. - Restore der letzten Log Backups mehrere Male um herauszufinden, wann
der Fehler aufgetreten ist.
Sie könnten auch die letzten Restore-Operationen mehrmal ausführen und dabei den STOPAT-Parameter immer weiter nach vorne treiben. Nach jedem Restore schauen Sie mit SELECT nach, ob der Fehler aufgetreten ist oder nicht. Dann wissen Sie, wann der Fehler aufgetreten ist und können diese Zeit für ein erneutes Restore benutzen.Die Möglichkeit des mehrmaligen Restores (bei einer Veränderung des STOPAT Parameters) ist ein kleines verstecktes Feature in SQL Server. Die ist aber
trotzdem eine von Microsoft unterstützte Methode.
4. Restore in eine andere Datenbank und Herauskopieren der betroffenen Daten
in die Produktions-Datenbank.
Der Vorteil ist dass Sie nur Daten von den betroffenen Tabellen verlieren. Allerdings enthält ihre Datenbank nun Daten aus zwei verschiedenen Zeitepochen. Dadurch können Relationen zwischen den Datenbanken verloren gehen. Diese Methode ist als nur etwas für Datenbankadministratoren, die genau wissen, wie Ihre Datenbank aussieht.
5. Verwenden von Tools, die eine "undo" Operation aufgrund der Log-Files ausführen können
Auch hier könnten nach dem Fehler noch Daten geändert worden sein, so dass eine inkonsistente Datenbank die Folge sein kann.
Wie viel Daten haben wir nun verloren?
- Hätte der User (z.B. Entwickler oder DBA) die fehlerhafte Operation innerhalb einer Transaktion ausgeführt, hätte ein einfaches ROLLBACK die Daten wieder auf den Ausgangsstand gebracht.
- Szenario 1: 15 Stunden 52 Minuten Arbeit gehen verloren
- Szenario 2: 52 Minuten Arbeit
- Szenario 3: 7 Minuten Arbeit gehen verloren
- Szenario 4: 7 Minuten Arbeit gehen verloren, aber die Datenbank ist unter
Umständen inkonsistent - Szenario 5: 7 Minuten Arbeit gehen verloren, aber die Datenbank ist unter
Umständen inkonsistent
Was haben wir gelernt?
- Führen sie regelmäßig ein Backup der Transaction Log-Datei aus.
- Trainieren sie ihre DBAs riskante Operationen innerhalb Transaktionen durchzuführen
- Lassen sie Entwickler nicht auf Produktionsmaschinen arbeiten.
- Trainieren Sie Ihre User sich sofort nach einer fehlerhaften Operationen zu melden.
- Machen Sie sofort ein Restore der aktuellen Log-Datei.
Skript aus dem Internet karaszi.com - Adresse nicht mehr online.
"Script to RESTORE the same log backup several times, using different time for STOPAT
The script below creates a database with a table in it, and then does a BACKUP DATABASE. It also creates a temp table to keep track on when each INSERT operation is performed, to be used for the RESTORE operations. Then a number of INSERT operations are performed, with a delay of 5 seconds. The initial stage finishes with a BACKUP LOG.
Finally, the RESTORE operations. First RESTORE DATABASE. Then a cursor is used over the temp table so we can restore the same log backup, using STANDBY so we can do SELECT in between each RESTORE and , of course, using a different time for each RESTORE operation.
Warning: Below will delete a database named 'test', if such exists!"
-- Initial stage: create the database, tables, BACKUP DATABASE,
-- some INSERT operations and finally a BACKUP LOG.
SET NOCOUNT ON
USE master
IF OBJECT_ID('tempdb..#dt') IS NOT NULL DROP TABLE #dt
CREATE TABLE #dt(seq int, when_ datetime)
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'test') DROP DATABASE test
GO
CREATE DATABASE test
GO
CREATE TABLE test..t(c1 int)
BACKUP DATABASE test TO DISK = 'C:test.bak' WITH INIT
GO
INSERT test..t VALUES(1)
INSERT #dt (seq, when_) SELECT 1, GETDATE()
WAITFOR DELAY '00:00:5'
INSERT test..t VALUES(2)
INSERT #dt (seq, when_) SELECT 2, GETDATE()
WAITFOR DELAY '00:00:5'
INSERT test..t VALUES(3)
INSERT #dt (seq, when_) SELECT 3, GETDATE()
WAITFOR DELAY '00:00:5'
INSERT test..t VALUES(4)
INSERT #dt (seq, when_) SELECT 4, GETDATE()
BACKUP LOG test TO DISK = 'C:test.bak'
GO
-- RESTORE stage: First RESTORE DATABASE,
-- then use a CURSOR so we can construct the RESTORE LOG command.
-- Use both STANDBY and STOPAT and restore the same backup several times,
-- with different time for each.
RESTORE DATABASE test FROM DISK = 'C:test.bak' WITH NORECOVERY
DECLARE c CURSOR FOR SELECT when_ FROM #dt ORDER BY seq
DECLARE @dt datetime
OPEN c
FETCH NEXT FROM c INTO @dt
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dt = DATEADD(ms, 50, @dt)
RESTORE LOG test FROM DISK = 'c:test.bak'
WITH FILE = 2, STANDBY = 'C:test.und'
,STOPAT = @dt
SELECT * FROM test..t
FETCH NEXT FROM c INTO @dt
END
CLOSE c
DEALLOCATE c
GO
--Clean up stage
DROP DATABASE test
Hallo, hier schreibt Lars. Dipl-Ing. Ingenieurinformatik (FH). Seit Jahrzehnten in der IT tätig. Geprüfter (und begeisterter) Webmaster. Ebenso begeisterter Windows-, Apple-, und Office-User. Ich schreibe über alle möglichen Themen rund um IT. Mehr über mich erfährst du hier: Über mich. Danke für deinen Besuch!