Datensicherung SQL-Server

Inhalt

Medium einrichten

Datenbank sichern

Sicherung planen

Empfohlene Aktionen für als Datenbanken, die als suspect oder corrupt gekennzeichnet sind

Datenverlust minimieren

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.

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.

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 als Datenbanken, die als suspect oder corrupt gekennzeichnet sind

(Übersetzung von http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp + Ergänzung)

Eine als suspect (fehlerferdä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 heißt 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:\Programme\Microsoft SQL Server\MSSQL\data\Personalkap2_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.

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.

(Additional solutions, depending on the errors, may be to manually rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on?)

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 http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp + 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

"Schmeißen" 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 Artbeit bis 15:00 Uhr. Unter Umständen akzeptabel, aber es geht besser.

3. Backup des Log-Files; Restore der letzten Datenbank und aller nachfolgend gesicherten Log-Dateien einschließlich 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 http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp:

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