In Schulungen werde ich oft nach einer Möglichkeit gefragt, ob man versehentlich gelöschte Datensätze wiederherstellen könnte. Japp, das geht ;-)

 

Auf die Frage, ob denn das SQL Backup auch so was wie eine Papierkorbfunktion besitzt, muss man erklären, daß eine vollständige, differentielle oder Transaktionsprotokoll Sicherung die gesamte Datenbank zu einem bestimmten Zeitpunkt wiederherstellt. An dieser Stelle hört man sofort ein allgemeines Gemurmel und leicht dezente Unmutsbekundungen. Eine Papierkorbfunktion ist nicht implementiert! (Zumindest bis SQL 2005)

Problem: Wiederherstellen einzelner Datensätze

Eine Ausnahme bildet eine Dateigruppensicherung übrigens auch nicht, da hier das Transaktionsprotokoll anschließend wiederhergestellt werden muss und die Tabellen so wieder auf den letzten Stand gebracht werden. Warum ist das so? Die Lösung ist absolut einleuchtend. Sofern wir - wie in > 99% der Fällen üblich - ein relationales Datenbankdesign vor uns haben, werden Daten mit Primärschlüssel und Fremndschlüssel versehen, um auf diesen Weg Inkonsistenzen vorzubeugen. Würden wir nun beispielsweise angeben Tabellenzeilen 2000-2050 aus der Bestelltabelle zurückzusichern, so könnten wir nicht mit Bestimmtheit sagen, ob die entsprechenden Käufer aus der Kundentabellen auch tatsächlich vorhanden sind. Bei kleineren Tabellen mag dies noch rel. überschaubar sein, aber bei 2 Millionen Zeilen sieht da die Sache schon anders aus.

Lösung: Trigger mit Papierkorb- und Überwachungsfunktion

Daher muss die Lösung ein wenig anders angegangen werden, in die ich zudem noch eine kleine Überwachungsfunktion implementiere. Die Lösung dafür bieten Trigger. Zugegeben, performance technisch gesehen sind diese grottenschlecht. Sofern aber nichts gegen den Einsatz spricht, sind sie ein absolut praktisches "Ding".

Sehen wir unsere den Trigger in der Praxis an.

Beispiel: Auffangbecken für gelöschte Datensätze

Das ist unsere zu überwachende Tabelle:

KDID | Nachname | Vorname 

Zu dieser Tabelle legen wir eine weitere Tabelle an. Ich nenne diese mal  Kunden_Survey mit folgenden Spalten:

KDID | Nachname | Vorname | Verbrecher | PC | Software | Tatzeit

Neben der Originalwerten der Datenzeile, sollen in der Überwachungstabelle der ausführende User, sowie die Zeit des Änderns bzw, Löschens oder Einfügens, als auch der Rechner, von dem dies aus geschah und mit welcher Software dies geschehen ist, aufgezeichnet werden.

Wie gehts?

Create trigger tr_pkorb_del on Kunden
For delete
as
insert into kunden_Survey
select 
 kdid, Nachname, vorname, verbrecher, pc, softeware, tatzeit,
 system_user,hostname,program_name, getdate()
 from master.dbo.sysprocesses, deleted
where spid= @@spid

Das Beispiel ist natürlich für updates oder inserts übertragbar. Allerdings müssen sie dementsprechend auch die Triggertabellen inserted zugreifen.

Erklärung

Alle Aktivitäten werden in der Tabelle sysprocesses (SQL 2000) bzw. sys.processes im SQL Server 2005 in der master DB aufgezeichnet. Mit Hilfe der globalen Variablen @@spid können sie daraus ihre eigenen Aktionen abrufen. Dazu gehören eben auch hostname=Ausführender PC, program_name = ausführendes Programm oder auch system_user=ausführender user.

Sobald der Trigger ein delete Statement abfängt, können sie also diese Werte auslesen, sowie auch die gelöschten Werte aus der deleted Tabelle.

Simple Sache;-)  Allerdings bleiben hier einige Wermutstropfen:

1. Um die Überwachung komplett zu machen, müssten sie das auf alle Tabellen anwenden.
2. Sofern Ihr System bereits auf Hochtouren fährt sollten Sie von Triggern absehen. Zumindest diejenigen Trigger vermeiden, die häufig ausgelöst werden würden.
3. Diese Lösung ersetzt kein Backup!

Fazit:

Auf diese Lösung bin ich übrigens gekommen, als immer wieder gemunkelt wurde, dass Datensätze, die erst vor kurzem angelegt wurden, nun verschwunden seien. Hier hab ich die Lösung, um solche Aussagen kontrollieren zu können.

Also viel Spaß bei Big brother is watching you... ;-)