Strategie einer Backup / Restore Lösung eines MS SQL Server 2005

[Lion1984]

Hallo @ all,

ich habe das "Glück" gehabt, auf einen aufgesetzten SQL Server zu stoßen und war von einem Tag auf den anderen für diesen zuständig.

Zum damaligen Zeitpunkt hatte ich noch nicht viel mit SQL 2005 zu tun, nun wäre meine Frage, was ist "ein/der" Weg, die Datenbanken gut zu sichern im Falle eines Datenverlustest? (Nur die Sicherung vom SQL 2005, Bandsicherung etc. ist bereits vorhanden).

Aktuell wird jede Datenbank gesichert:
- Full Backup: 1 mal wöchentlich
- Log Backup: stündlich
- Diff. Backup: alle 4 Stunden

Laut meinem Chef ist das ein "guter" Weg, wie ihn u.a. Microsoft vorschlägt.

Meine Frage wäre nun, ob dies tatsächlich so ist?
Und was machen muss, um die aktuelle Datenbank wiederherzustellen? (z.B.: Serverausfall am Mittwoch, Wiederherstellung bis Mittwoch sollte möglich sein?)

Mit welchen Dateien anfangen? welche EInstellungen sind zu beachten?

Wenn ich bei Dr. Goggle nachdem suche, finde ich immer nur wie es gemacht wird (mit den Dialogen vom Managment Studio), aber leider nicht, wie ich in meinem Fall vorgehen müßte, um die Datenbank korrekt wiederherzustellen.

lg Lion

[O. Helper]

Hallo Lion,

die "beste" Sicherungsstrategie hängt natürlich von den vorhandenen Resourcen und der Größe der DBs ab; ebenso wie sicher man sein will.

Grundsätzlich ist es so:
- Ein Vollsicherung sichert die gesamte Datenbank (wie der Name schon sagt ) und wird (unkomprimiert) etwas so groß wie die Datenbank selber
- Eine Differentialsicherung sichert alle Änderungen seit der letzten Vollsicherung; wächst von der Größe her immer weiter an, bis wieder voll gesichert wird.
- Die Log-Sicherung sichert alle Transaktionen seit der letzten Log-Sicherung, sind also i.d.R recht klein.

Wenn Du nun mal zurücksichern musst, dann musst Du
- Die letzte Vollsicherung
- Die letzte Differentialsicherung
- alle LOG-Sicherungen
wieder zurücksichern lassen, um auf den letzten Stand zu kommen. Durch die LOG-Sicherungen kannst Du dabei auch den Zeitpunkt auf die Sekunde angeben, zu der rückgesichert werden soll.

Ruf mal im Manangement Studio "Datenbank wiederherstellen" auf (Rechte Maus auf DB => Tasks => Wiederherstellen => Datenbank).
Hier wieder Dir schon die letzten Sicherungen angezeigt, was Du im Falle des Falles rücksichern müsstest.

Von daher ist euer Sicherungsmodel schon gut, auch wenn ich finde, das die Diff-Sicherung alle 4 Stunde etwas übertrieben ist; täglich würde reichen.


Wir machen hier täglich eine Vollsicherung aufs SAN und auf Band, stündlich eine LOG-Sicherung; keine Diff-Sicherungen.
LOG aber nur zur Sicherheit; ob ich die je zurücksichern würde, ist fraglich.
Es sind nämlich die DBs unserer ERP-Software und jeder Buchhalter kann einem sagen, was er heute erfasst hat, aber nicht was er bis 10:45:32 Uhr gemacht hat.
Dann raussuchen, was fehlt ist aufwendiger als es neu zu erfassen.

[Lion1984]

Hallo O. Helper,

erstmal vielen Dank für deine ausführliche Unterstützung Das bringt mich schon um einiges weiter.

D.h. wenn unser Server mal "ausfällt" und ich den Server neu aufsetzten müßte und keine Datenbanken vorhanden wären.

Dann brauch ich, wie ich dich verstanden habe folgendes:
die letzte Vollsicherung (bei uns Sonntags) und die letzte Diff. und alle Log Sicherungen ab dem Fullbackup.

Nur wie Spiel ich das ein? Zuerst dass Fullbackup und anschlißend die Diff Sicherung? Anschließend die LOG's ?

Wenn das so ist, werde ich wohl mal die Order Sturktur umstellen, denn aktuell ist ein Ordner vorhanden, der so wie die Datenbank benannt ist und da landen alle Backups. Denn die soweit ich es gesehen habe, haben die Diff. und Full Backups beide die Endung *.bak. Hier die letzte rauszusuchen könnte u.a. mühsam werden.

Bezüglich dem Interval von einer Differentialsicherung hängt, wie du es geschrieben hast, natürlich von der Datenbank ab und von den User, die diese verwenden bzw. welche Daten in der Datenbank enthalten sind.

Ansonsten vielen Dank!
lg Lion

[Lion1984]

Ich nochmal
Ich habe nun ein wenig rumversucht (wie auch zuvor). Nur diesmal habe ich mir die Optionen eines Restores genauer angeschaut.

Wichtig hier ist wohl, dass man erst beim letzen Restore die Datenbank freigibt (RESTORE WITH RECOVERY), bis dahin sollte man die Option "Restore with NORECOVERY" verwenden.

Hier hab ich mich von den Namen ableiten lassen, "RESTORE WITH RECOVERY" hätte ich so gedeutet, dass man weitere Recoveries verwenden könnte

Aber nun habe ich mir die Frage selbst beantwortet:
- letztes Full Backup einspielen (Restore with NORECOVERY)
- letzes Diff. Backup einspielen (Restore with NORECOVERY)
- Alle Logs seit dem letzten Diff. Backup einspielen (Restore with NORECOVERY), das letzte mit "Restore with RECOVERY" sozusagen abschließen.

Habe ich das nun richtig "auspropiert" / verstanden oder mängelt es da noch an etwas?

[O. Helper]

Ganz genau, so läuft das ab. Wie erwähnt, wird Dir das für den letzen Sicherungsstand im M-Studio auch so vorgeschlagen.

Eine gute Doku gibt es auch in der Online-Hilfe unter "Einführung zu Sicherungs- und Wiederherstellungsstrategien in SQL Server" dazu.

D.h. wenn unser Server mal "ausfällt" und ich den Server neu aufsetzten müßte

Bei dem Szenario gibt es noch etwas bezüglich der "master" Datenbank zu beachten.
Ich hatte für mich das mal dokumentiert, da in dem Fall eh schon Hektik sein wird und man nicht an alles denken kann.
Ist zwar noch für MSSQL 2000, geht bei 2005 aber genauso:

Desaster Recovery für MS SQL 2000

Im recht unwahrscheinlichen Fall, das die Datenbank "master" zerstört ist, bedarf es eine gesonderte
Vorgehensweise, um diese wieder rückzusichern.

1. Neuanlage einer leeren "master" Datenbank
Dazu gibt es das bei einer Standard-Installation das Tool "RebuildM", um die Datenbank neu
anzulegen. Benötigt wird hierfür entweder die "MS SQL Server 2000" Installations CD oder ein
Verzeichnis mit einwandfreien "master", "model" und "msdb" Datenbanken; diese dürfen zu dem
Zeitpunkt nicht in Benutzung sein.
Das Tool kann mit
..\Program Files\Microsoft SQL Server\80\Tools\Binn\rebuildm.exe
gestartet werden.
Mit Browse kann das Verzeichnis mit der master-Vorlage (CD oder andere SQL Instanz)
ausgewählt werden.
Über Settings kann man, wie bei der Server-Installation die Optionen für Sortierung etc.
vorgegeben werden. Wenn die master-DB zurückgesichert werden soll, sind diese nicht erheblich.


2. SQL Server im Einzelbenutzermodus starten.
Dadurch kann nur ein User aus der SysAdmin bzw. BackupAdmin Gruppe eine! einzige
Verbindung herstellen. Es ist deshalb darauf zu achten, das man nicht zur Kontrolle den MS
Enterprise Manager startet oder das der SQL Server Agent gestartet wird; damit wäre die eine
mögliche Verbindung bereits belegt.
..\Program Files\Microsoft SQL
Server\MSSQL$InstanzenName\Binn\SQLServr.exe –m -s <Instanzenname>
Der SQL Server startet so mit einer DOS-Box, in der auch das Protokoll ausgegeben wird.

3. "master" Datenbank rücksichern
Nun kann die "master" Datenbank zurückgesichert, sei es mit dem MS SQL Enterprise Manager
von einer Festplattensicherung oder mit dem Backup Client von einer Sicherung aus der Tape-Library.
Hinweis: Sobald die Rücksicherung erfolgt ist, wird sofort der SQL Server Dienst beendet. Deshalb
können keine weiteren Rücksicherungen in einem Lauf durchgeführt werden und es ist völlig
normal, wenn im Anschluß z.B. der TSM einen Fehler meldet, da ja die Connection getrennt
wurde.

4. Neustart des MS SQL Server Dienst
Nun kann der MS SQL Server Dienst wieder im normalen Modus gestartet werden und ggf. weitere
Rücksicherungen durchzuführen.

[Lion1984]

Danke für deine Hilfe und Ergänzungen.

[Lion1984]

Hallo Forum,

ich bin gerade dabei, einen "Ernstfall" des SQL Ausfalles nachzubilden und versuche in einer virtuellen Umgebung, den letzten aktuellen Stand nachzubilden.

Dabei habe ich O. Helper eine IGM geschrieben, ob er Tipps hätte, da man bei Suchmaschinen relativ wenig findet.

Er schrieb mir natürlich, ich soll die IGM ins Forum posten, was ich natürlich mache (weiß nicht warum ich die Frage direkt hier gestellt habe).

Zitat:
aber bei meiner google suche finde ich nur, wie man backups macht

Wohl war, man findet herzlich wenig. Auch in der sonst sehr guten Online Hilfe findet man nur die paar Sätze "Bitte Rücksichern". Ich vermute mal, das MS absichlich keine konkrete Vorgehensweise angibt für den Fall, dass das schief geht und man dann bei MS deshalb reklamiert.
Ist übrigens ein guter Grund, diese Mail eher ins Forum zu posten, a) damit man zukünftig beim googlen etwas findet und b) es auch noch andere Leute gibt, die etwas dazu beitragen können.

Deine Vorgehensweise stimmt soweit schon mal, also

- Installation Betriebssystem + aller Updates, da dürfen es auch die aktuellsten sein. SQL2008 erwartet z.B. für Win2003 das SP2, sonst lässt es sich gar nicht erst installiern
- Installation SQL Server + den Update-Stand zur Sicherung; neueres sollte man besser nicht einspielen, das teilweise mit den SPs auch DB-interne Änderungen gibt

So, nun hast Du bereits eine voll lauffähigen SQL Server. Jetzt könntest Du schon die User-DBs rücksichern.
Aber: In der master-DB sind die ganzen User-Accounts gespeichert. Wenn es nur Win-NT Accounts gibt, könntest Du die von Hand wieder anlegen. Aber spätestens, wenn es auch SQL-Accounts gibt, kommt das Problem mit den Passwörtern auf. Die der realen Usern könnte man auf Default-Werte setzen; ist nur blöd, wenn der Chef im Uralub ist und er es nicht ändern kann; dann kann jeder seinen Account nutzen. Bei SQL-Account, die von externen Applikationen genutzt werden, weiß man meistens nicht die Pwds, auch schlecht.

Also, nächster Step:
- die master-DB rücksichern
- Nach Dienst-Neustart die User-DBs rücksichern

Wenn Du mit SSIS arbeitest oder mehr als nur ein paar Wartungspläne hast
- msdb-DB rücksichern; wichtig: Zuvor den SQL-Server Agent beendet, da der sich an die DB connected, was ein Restore verhindert

Es empfiehlt sich durchaus, anschließend alle DBs mit DBCC CheckDB zu kontrollieren (sollte man regelmäßig mal machen), ist aber nicht zwingend notwendig.
Das wars.

Das sollte man ruhig auch mal auf einem Testsystem durchspielen. Zum einen um sicher zu stellen, das man wirklich nichts vergessen hat und zum anderen, um zu ermitteln, wie lange es wirklich dauert, bis das System wieder einwandfrei läuft.
Allein das Rücksichern der DBs dauert merklich länger, als das Sichern, den zunächst wird die Sicherungsdatei geprüft, ob sie einwandfrei ist.

Ich werde nächste versuchen, den aktuellen SQL Bestand wieder herzustellen, schon mal jetzt ein großes Dankeschön an O. Helper.

Natürlich dürfen andere auch "Ihren" Senf dazugeben.

lg Lion

[Lion1984]

Hallo @ all,

ich bin nun soweit und versuche die master und die msdb Datenbanken wieder herzustellen. Da ich mich auf einer virtuellen Maschine befinde, sind dort die Pfade anders. Wenn ich nun die master DB wiederherstelle, startet anschließend mein SQL Server nicht mehr, da er bestimmte Dateien (von tempdb, msdb und model) nicht finden kann.

Abhilfe für das Problem schafft folgendens:

source server = server from which backups originated
target server = server onto which we wish to restore the system databases

Presuming that paths to system files are different (more difficult to do than if paths are the same)


1. Ensure target server is same build revision as source server. Patch accordingly.

2. Start target server in single user mode (sqlservr -c -m -f)

3. Connect to SQL Server using sqlcmd

4. Restore master database (Note - does not require WITH MOVE option).
When done, SQL Server stops automatically.

5. Start target server in single user mode (sqlservr -c -m -f -T3608)

6. Connect to SQL Server using sqlcmd

7. Use ALTER DATABASE command to point SQL Server to the mssqlsystemresource database:

ALTER DATABASE mssqlsystemresource
MODIFY FILE (name = data, filename = ' \mssqlsystemresource.mdf')
GO

ALTER DATABASE mssqlsystemresource
MODIFY FILE (name = log, filename = ' \mssqlsystemresource.ldf')
GO

8. Stop SQL Server (Ctrl-C). Start in single user mode.

9. Use ALTER DATABASE command to point SQL Server to the model, msdb & tempdb databases:

modeldev = model.mdf
modellog = modellog.ldf
msdbdata = msdbdata.mdf
msdblog = msdblog.ldf
tempdev = tempdb.mdf
templog = templog.ldf

10. Stop SQL Server. Start SQL Server (either from cmd or as service).

11. Restore msdb, model if required.

Ich habe den Restore aber mit dem Managment Studio gemacht. Anschließend habe ich auch die msdb wiederhergestellt. Und siehe da, wenn ich mich einlogge sehe ich alle Datenbanken, User und auch Pläne.

So weit so gut, nur ich krieg meinen SQL Agent nicht mehr zum laufen.
Hat einer von euch schon ein Restore mit anderen Pfade vollbracht?

Auch ein kleiner Hinweiß, wenn anschließend der SQL Server gestartet wird erscheinen im EventLog massig Fehlermeldungen, da er die Datenbankdateien nicht mehr finden kann.

Eine User - DB habe ich anschließend bereits erfolgreich wiederhergestellt, Probleme machen eben noch der Agent und die Reporting Services.

lg Lion

[O. Helper]

Hallo Lion,

sieh mal hier nach zum Thema "Prozedur zur Wiederherstellung nach Fehlern" / "Verschieben der master- und Resource-Datenbanken":
http://msdn.microsoft.com/de-de/libr...8(SQL.90).aspx

Vielleicht hilft es Dir schon; leider fehlt mir Zeit & Möglichkeit, das selbst mal nachzuvollziehen; sollte ich aber bald auch mal wieder durchführen, man sieht ja, was einem so alles erwarten kann.

[Lion1984]

Hallo O.Helper,

ja, auf diesen Link bin ich bereits auch gestoßen und es ist eigentlich die selbe Prozedur. Den Link habe ich auch gefunden, ich steck mitten drin und wollte ihn posten wenn alles gepasst hat, oder wo es eventuell Probleme bei mir gab.

Aber bislang verlief das Verschieben Problemlos. Jetzt habe ich für die Systemdatenbanken den selben Pfad wie am Live System als auch am virutellen System. Bin mal gespannt ob es einfach wird. (Die Annahme ist ja, dass das system ersetzt wird bzw. neuinstalliert und somit die gleiche Pfade haben sollte).

Was ich jedoch nicht verstehe ist das Verschieben von Benutzerdatenbanken (http://msdn.microsoft.com/de-de/libr...3(SQL.90).aspx). Laut MSDN wird die Datenbank Offline genommen, per ALTER der Pfad geändert, verschoben und wieder Online genommen. Doch leider lässt mich der SQL Server die Dateien nicht verschieben, solang dieser rennt.

Lion

[O. Helper]

Wenn Du eine Datenbank "Offline" nimmst, ist sie bloss von extern nicht mehr verfügbar (zugreifbar), bleibt aber weiter in Verwendung durch den MSSQL; sie ist weiter gesperrt und kann nicht verschoben werden.

Bei User-DBs ist es recht einfach, Du musst Sie detachen (trennen), verschiebst die Datenbank-Dateien ins Zielverzeichnis (auf den der MSSQL Zugriffsrechte hat!) und attach (anhängen) sie wieder; fertig.

Siehe
sp_detach_db
sp_attach_db

[Lion1984]

ja so habe ich es auch gemacht bzw. immer gemacht.

Nur laut MSDN soll eben Offline bzw. Online nehmen reichen.

Aber wie du gesagt hast, es reicht nicht und ich verwende weiter hin de/attach.

Lion

[Lion1984]

Hallo,

also ich hatte heute fast den ganzen Tag Zeit, mich mit meiner virtuellen Kiste zu spielen und ich habe es (mit ausnahmen) geschafft den SQL Server "komplett" zu reproduzieren.

Was mich oft aufgeschmissen hat ist die Tatsache, dass ich einen anderen Pfad habe als im Live System, d.h. nach dem Restore von der master Datenbank startet der SQL Server erstmal nicht.

Der SQL Server muss wieder im Einzelbenutzermodus und mit dem Flag -T3608 gestartet werden. ( Verschieben der master- und Resource-Datenbanken). Dabei wird natürlich nicht die Datenbank selbst verschoben, sondern der Pfad auf den richtigen geändert. Bei mir war dass z.B. der Fall, dass ic hauf einem 64Bit OS den 32Bit SQL Server installiert habe, dadurch sind folgende Pfadangaben entstanden: C:\Program Files (x86)... Da ich diese nicht auf der virtuellen Maschine habe, musste ich die abändern.

Vorrausgesetzt dass die anderen Systemdatenbanken im selben Verzeichniss wie bei Original System liegen, startet anschließend der SQL Server ohne Probleme und mann kann die msdb und Benutzer Datenbanken wiederherstellen.

Nach der Wiederherstellung von der msdb Datenbank mußte ich den Service Broker wieder aktivieren und erhielt trotzdem folgende Meldung im EventLog:
Subsystem SSIS could not be loaded.

Abhilfe fand ich hier:
http://support.microsoft.com/kb/914171/en-us/

Das einzige Problem was ich wohl noch habe ist, dass alle Backup Aufträge und ähnliches auf den Org. Server verweißen (z.B. virtuelle Maschine ist ServerB, Pläne zeigen aber auf ServerA). Dachte eigentlich, dass die Angabe localhost gespeichert wurde bzw. wurden diese Pläne wohl schlecht / falsch aufgesetzt.

Die ReportingServices konnte ich auch ohne Probleme wieder herstellen (habe alle Ordner, Reports usw.). Doch auch hier ging die Datenbankverbindung unter Eigenschaften verloren. Ich denke, dafür habe ich etwas vergessen.

Nochmals Danke für die großartige Hilfe!
Lion