Wie kann ich herausfinden wer Daten aus der Datenbank gelöscht hat?

Question

Hallo zusammen,

uns ist etwas sehr merkwürdiges passiert, und zwar ist der Inhalt einer Datenbank bei uns gelöscht worden. Nun würde ich gerne wissen wann und mit welcher Anmeldung gelöscht wurde. Die Suchmaschine meines Vertrauens hat mir diesen Link ausgespukt:

https://www.mssqltips.com/sqlservertip/3160/recover-deleted-sql-server-data-and-tables-with-the-help-of-transaction-log-and-lsns/

Grundsätzlich funktioniert dieser Tipp, ich habe ihn auf einem Test-Server ausprobiert. Nur auf der Maschine, auf der die Daten verschwunden sind liefert die Abfrage kein Ergebnis.

Muss der SQL-Server explizit konfiguriert sein, damit das Löschen im Transaction Log File erfasst wird?

Grundsätzlich verhält es sich mit der Datenbank wie folgt:

Daten werden nie gelöscht.

Es gibt nur zwei User die auf die DB zugreifen. Das ist einmal der Hersteller von einer Anlage, es werden Prozessdaten in die DB geschoben und wir - wir lesen nur.

Ich könnte mir vorstellen, dass es vom Anlagenhersteller eine Routine gibt die Daten zu löschen. Bevor wir ihn kontaktieren hätte ich gerne etwas in der Hand.  

Was habe ich noch für Möglichkeiten die Ursache bzw. den Verursacher zu ermitteln?

Herzliche Grüße
Christoph

Answer 1

Ich konnte gerade an dem Produktiv System etwas testen, und zwar habe ich mit dem Management Studio einige unkritische Datensätze gelöscht. Jetzt gibt mir folgende Abfrage auch ein Wert zurück:

USE MeineDB
GO
SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

An der Konfiguration des Servers sollte es somit nicht liegen. Aber wie kann das sein, wie kann die Datenbank leer sein, ohne dass es im Transaction Log auftaucht?

Irgendwelche Ideen?

Herzliche Grüße
Christoph

Answer 2

Hallo Christoph,

wenn über das Transaction Log keine Hinweise auftauchen, klingt das nach "SIMPLE" Modus. Wenn dann die VLFS bereits überschrieben sind, keine Chance (Außer über ein zufällig im richtigen Moment gelaufenes Full backup). Mit Glück ist sie aber doch im FULL Modus. Dann hast Du die Transaktionsprotokoll-Backups.

viel Glück

Andreas

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com

Answer 3

Falls es kein Simple-Modus ist, könnte vielleicht ein Tool weiterhelfen:

Hilfe aus dem Transaction Log

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 4

Falls es (kein) Simple-Modus ist, könnte vielleicht ein Tool weiterhelfen:

Hilfe aus dem Transaction Log

---

..

Wie soll das Tool Daten herholen können, die im, um genau zu sein, DURCH den Simple Modus verschwunden sind, und manuell nicht mehr zu lesen sind? *wunder*

bezog sich auf die Version vor der Korrektur.

Ansonsten macht das Tool fast das selbe, was man von Hand auch kann, nur sicher bequemer.

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com

Answer 5

Was habe ich noch für Möglichkeiten die Ursache bzw. den Verursacher zu ermitteln?

Hallo Christoph,

wie Andreas bereits angedeutet hat, ist eine forensische Untersuchung so gut wie unmöglich, wenn die Datenbank im SIMPLE Recoverymodus läuft. Sofern das aber nicht der Fall sein sollte, habt Ihr sicherlich regelmäßig Protokollsicherungen gemacht, oder :)

Sollte das der Fall sein, kannst Du die Protokollsicherungen durchsuchen. Hier mal ein Beispiel mit einer meiner "Demo-Datenbanken":

USE demo_db;
GO

-- Datenbank im FULL recovery modus und Backup gemacht
ALTER DATABASE demo_db SET RECOVERY FULL;
GO
BACKUP DATABASE demo_db TO DISK = N'NUL';

SET ROWCOUNT 100;
GO

-- 100 Datensätze werden gelöscht!
DELETE FROM dbo.large_table;
GO

-- Logsicherung wird aufgehoben um sie später zu untersuchen
BACKUP LOG demo_db TO DISK = N'S:\Backup\lb.trn' WITH INIT, FORMAT, COMPRESSION;
GO

-- Auslesen aller LOP_DELETE_RECORD Einträge
SELECT  [Current LSN] ,
        [Transaction ID] ,
        [Transaction Name] ,
        [Operation] ,
        [Begin Time] ,
        [PartitionId] ,
        [Transaction SID]
FROM    fn_dump_dblog(NULL, NULL, N'DISK', 1,
                      N'S:\Backup\lb.trn', DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                      DEFAULT)
WHERE   Operation = 'LOP_DELETE_ROWS';
GO

Statt fn_dblog verwendest Du fn_dump_dblog() - ansonsten ist der Hinweis von Christoph Muthmann nicht so abwegig, wenn Deine Datenbank (wie von ihm vermutet) im FULL Recovery Modus läuft.

---

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)

Answer 6

Deshalb stand da ja: Falls es kein Simple-Modus ...

Solltest Du aber eine Mail mit meiner Nachricht bekommen haben, dann war da ein Tippfehler drin und das "kein" fehlte! ;-))

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 7

Stimmt, ich hatte mich auf die mail bezogen

witzigerweise habe ich es nichtmal bemerkt, als ich den "Zitieren"-Knopf verwendet habe.

Man sollte kostenfreien Foren nie trauen - die Leute könnten eventuell während ihrer eigentlichen Arbeit mal ein Wörtchen übersehen :-D

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com

Answer 8

Guten Morgen zusammen,

der Wiederherstellungsmodus steht auf vollständig. Backups wurden von der DB noch nicht gemacht, wobei die Priorität auch nicht so hoch ist, aber dennoch möchte ich nicht, dass die Datenbank nochmal geleert wird.

Gestern hatte ich ja geschrieben, das ich einige Datensätze gelöscht habe und das durch die Abfrage -

USE TestDB
GO
SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

- Bestätigt wird. Heute Morgen liefert die Abfrage allerdings kein Ergebnis mehr. Woran liegt das?

Ich werde mir heute das erwähnte Tool besorgen, und wenn ich an das System kann damit testen.

Vielen Dank schon mal für die ganzen Tipps und Anregungen, wirklich sehr nett!!

Herzliche Grüße
Christoph

Answer 9

Guten Morgen zusammen,

der Wiederherstellungsmodus steht auf vollständig. Backups wurden von der DB noch nicht gemacht, wobei die Priorität auch nicht so hoch ist, aber dennoch möchte ich nicht, dass die Datenbank nochmal geleert wird.

Gestern hatte ich ja geschrieben, das ich einige Datensätze gelöscht habe und das durch die Abfrage -

USE TestDB
GO
SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

- Bestätigt wird. Heute Morgen liefert die Abfrage allerdings kein Ergebnis mehr. Woran liegt das?

...

Hallo Christoph,

wenn von der Datenbank noch nie ein Full backup gemacht wurde seit der Umstellung auf "Full", dann ist sie nicht wirklich auf Full sondern weiterhin im Simple Modus. Und danach "riecht" es ja irgendwie. Denn sonst wäre das Log ja noch gefüllt. Rein äußerlich merkt man es ja, ob sein Transaktionsprotokoll immer weiter wächst, oder wie eben im Simple Modus gleichbleibt. Das ist schon mal der erste Indikator.

Hundertprozentig weißt Du es, wenn in der Spalte Systemview database_recovery_status die  "last_log_backup_lsn" NULL steht - dann ist sie noch im einfachen Wiederhertellungsmodel.

SELECT DB_NAME(database_id) AS DB, last_log_backup_lsn from sys.database_recovery_status

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com

Answer 10

Der Modus ist leider simple. Das bedeutet, dass ich mir alle weiteren Aktionen sparen kann, auch das genannte Tool, oder?

Answer 11

Yep!

---

Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

Answer 12

ja das stimmt

Answer 13

Ok, schade!

Vielen Dank für die kompetente Unterstützung!

Herzliche Grüße

Christoph

Answer 14

Der Modus ist leider simple. Das bedeutet, dass ich mir alle weiteren Aktionen sparen kann, auch das genannte Tool, oder?

Einfache Antwort: Ja!

---

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)