Backup, Recovery und Database Snapshots

Datenbanken sichern (Backup)

Schritte der Datensicherung

1. Sicherungsmedium erstellen

2. Datenbank in den Modus SINGLE_USER umschalten

3. Datenbankkonsitenz prüfen

4. Datenbank sichern

Sicherungsmedium erstellen

Ein Sicherungsmedium wird durch einen Eintrag in der master.sysdevices- Tabelle erstellt. Dabei wird einem Bandlaufwerk oder einem physischen Dateiname ein logischer Name hinzugefügt, der später als Beschreibung im BACKUP- Befehl genutzt werden kann. Die Erstellung erfolgt mit der Systemprozedur sp_addumpdevice:

sp_addumpdevice [@devtype= ]       '<device_type>',
                [@logicalname = ]  '<logical_name>,
                [@physicalname= ]  '<physical_name>'               

Nach erfolgreicher Ausführung des Kommandos befindet sich im Enterprise- Manager unter Verwaltung/Sicherung ein neuer Eintrag für das Sicherungsmedium.

Beispiel:

use master
go
exec sp_addumpdevice 'disk',
                     'geoinfo-disk-bak',
                     'c:\backup\mko-geoinfo.bak'
go

Aufbau eines Bandmediums

Datenbankkonsitenz prüfen

Die Konststenzprüfung erfolgt mit dem Befehl dbcc checkdb:

dbcc checkdb ('<database_name>' [, NOINDEX | {REPAIR_FAST | REPAIR_REBUILD | REPAIR_ALLOW_DATA_LOSS}] )
             [with {[ALL_ERRORMSGS | NO_INFOMSGS]
                    [, TABLOCK]
                    [, ESTIMATEONLY]
                    [, PHYSICAL_ONLY]
                    [, TABRESULTS]
                   }
             ]

Bsp.:

use master
go
-- Wieviel Speicherplatz wird die Prüfung belegen
dbcc checkdb ('geoinfo')with estimateonly
go
-- Prüfung der Datenbank ohne Ausgabe von allg. Infos
dbcc checkdb ('geoinfo') with no_infomsgs
go
-- Reparatur einer Inkonsitenten Datenbank, garantiert ohne Datenverlust.
dbcc checkdb ('geoinfo'), REPAIR_FAST with no_infomsgs
go
-- Die Datenbank kraftstoff wurde zuvor mittels des Tools bomber.pl partiell beschädigt
-- Reparatur mittels dbcc
dbcc checkdb ('kraftstoff', REPAIR_ALLOW_DATA_LOSS)
go

Datenbank sichern

Die Sicherung erfolgt mit dem Befehl backup database:

Einfache Sicherung

use master
go
backup database geoinfo
to [geoinfo-disk-bak]
go

Sicherung mit Passwort und Angabe der Mindesthaltbarkeit der Sicherung

backup database geoinfo
to bakfile_geoinfo     
go

Differentielle Sicherung

Bei der differentiellen Sicherung werden nur die Änderungen zur vorausgegangenen Vollständigen Sicherung aufgezeichnet.

• differentielle Sicherung kann nur angewendet werden, wenn mindestens eine vollständige Sicherung vorausgegangen ist

• differentielle Sicherung ist kumulativ, dh. die dritte differentielle Sicherung enthält alle Daten der 1. und 2. differentiellen Sicherung

use master
go
backup database geoinfo to [geoinfo-disk-bak]
go
use geoinfo
go
insert into kennzeichen (kz, land) values ('txt', 'testland')
use master
go
backup database geoinfo to [geoinfo-disk-bak] with differential, noinit
go

Sicherung der Protokolle

Die Sicherung der Protokolle setzt voraus, das eine vollständige Datensicherung stattgefunden hat.

Zwei zusätzliche Optionen zur vorausgeangenen Datensicherung:

Bsp.: Siehe S. 316

Sicherung mit SSMS

siehe unter: <Serverinstanz>/Datenbanken/<Datenbank-Kontextmenü>/Alle Tasks/Datenbank sichern...

Wiederherstellung der Datenbank

Automatische Wiederhestellung bei Systemstart

Beim Hochfahren erfolgt eine Automatische Wiederherstellung über die Transaktionsprotokolle der Datenbanken. Begonnen wird mit der Masterdatenbank, da die Speicherorte der restlichen Datenbanken in der Tabelle master.sysdatabases abgelegt ist.

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters\
  |
  +-> master.sysdatabases.files
  |       |
  |       +-> Dateipfad von geoinfo
  |       |       |
  |       |       +-> geoinfo.sysfiles
  |       |       +-> geoinfo.sysfilegroups
  |       |
  |       +-> Dateipfad von Kraftstoff
  |       |       |
  |       |       +-> Kraftstoff.sysfiles
  |       |       +-> Kraftstoff.sysfilegroups
  |       |

Der Prozess der automatischen Wiederherstellung ist auch dokumentiert im Protokoll der Serverinstanz unter:

<server>/Verwaltung/SQL Serverprotokolle

Entscheidend für die Dauer der automatischen Wiederherstellung ist die Länge des Prüfpunktintervalles.

Manuelle Wiederherstellung

Varianten

Voraussetzungen für die Wiederherstellung

1. Datenbank darf nicht benutzt werden (Kein Benutzer hat Datenbank mit use geöffnet bzw. auf DB läuft keine Abfrage)

2. Nur Bnutzer der Serverrolle sysadmin, dbcreator oder user dbo dürfen einen Datenbank wiederherstellen

3. Transaktonsprotokolle müssen in der Reihenfolge ihrer Entstehung wiederhergestellt werden

Schritte der Wiederherstellung

1. Den richtigen Sicherungssatz finden

2. Prüfen, ob der Sicherungssatz verwendbar ist

3. Wiederherstellung

Den richtigen Sicherungssatz finden

Mit folgenden drei Anweisungen können Infos über den Inhalt von Sicherungsmedien gewonnen werden.

-- Auslesen Kopfinformationen von einem Sicherungsband
restore labelonly form [geoinfo-disk-bak]
-- Auslesen der Informationen zum gesamten Sicherungssatz
restore headeronly from [geoinfo-disk-bak]
-- Auslesen der Liste der gesicherten Datenbank- und Protokolldateien
restore filelistonly from [geoinfo-disk-bak]

Prüfen, ob der Sicherungssatz verwendbar ist

Mittels folgenden Befehls kann die Lesbarkeit der Medienfamilie überprüft werden

restore verifyonly from [geoinfo-disk-bak]

Wiederherstellungsoptionen

Sollen einer Wiederherstellung weitere Wiederherstellungen (differentiell oder aus Transaktionsprotokollen) folgen, dann dürfen offene Transaktionen durch die aktuelle Wiedeherstellung nicht zurückgerollt werden (Option Norecovery), um ein nahtloses aneinandersetzen der Aufzeichnungen zu ermöglichen. Die letzte Wiederherstellung muß immer mit der Option recovery abgeschlossen werden, um eine funktionsfähige Datenbank zu erhalten.

Vollständige Wiederherstellung

Die vollständige Wiederherstellung einer Datenbank kann mit dem Befehl restore database erfolgen:

use master
go
-- Wiederherstellung simpel
restore database geoinfo from [geoinfo-disk-bak]
go
-- Bei der Wiederherstellung werden die Speicherorte der Datenbank- und Protokolldateien verändert
use master
go
restore database kraftstoff from [kraftstoff-disk-bak]
with move 'kraftstoff' to 'c:\db-neu\kraftstoff.mdf',
     move 'kraftstoff_log' to 'c:\db-neu\kraftstoff.ndf'
go
-- Überschreiben der laufenden Datenbank durch die Sicherung erzwingen

Differenzielle Wiederherstellung

Bei der Differenziellen Wiederherstellung ist wie folgt vorzugehen:

1. Vollständige Wiederherstellung mit Option NORECOVERY (Kein Rollback offener Transaktionen)

2. Wiederherstellung aus dem Medium mit differenziellen Sicherungssatz

Diefferenzielle Sicherung sind kumulativ- wurden nach der letzten vollständigen Sicherung drei Differenzielle angelegt, dann ist nur die letzte differentielle Sicherung wiederherzustellen.

Wiederherstellung von Transaktionsprotokollen

Bei der Wiederherstellung mittels Transaktionsprotokollen ist wie folgt vorzugehen:

1. Wiederherstellung der Datenbank mit Option NORECOVERY

2. Wiederherstellung der Protokolldatei mittels Restore log

use master
go
-- Vollständige Wiederherstellung mit Transportprotokollen
restore database kraftstoff from [kraftstoff-disk-bak] with norecovery
go
restore log kraftstoff from [kraftstoff-log-disk-bak]
go

Wiederherstellung bis zum Zeitpunkt X

Es wird eine Testdatenbank namens test-restore angelegt:

create database [test-restore]
go
use [test-restore]
go
create table data (
  uhrzeit datetime
)
go

Mittels eines folgender Insert- Anweisung, die periodisch aus dem SQL- Server Agent angestartet wird, werden fortlaufend Datensätze mit einem Zeitstempel in der Datenbank produziert.

insert into data values(getdate())
go

Als erstes erfolg eine vollständige Sicherung:

backup database [test-restore] to [test-restore-bak]

Zu einem späteren Zeitpunkt erfolgt eine Sicherung des Transaktionsprotokolles

backup log [test-restore] to [test-restore-bak]

Die Datenbank wird gelöscht, und anschließend mit folgenden Anweisungen bis zum Zeitpunkt X wiederhergestellt

use master
go
restore database [test-restore] from [test-replikation-bak] with norecovery
go
restore log [test-restore] from [test-replikation-bak] with file=2, stopat='13.11.2003 23:15:00', recovery
go

Wiederherstellung mit SSMS

siehe unter: siehe unter: <Serverinstanz>/Datenbanken/<Datenbank-Kontextmenü>/Alle Tasks/Datenbank wiederherstellen

Protokollierung der Wiederherstellungsschritte in msdb- Tabellen