Ki törölte a táblát???

Hol volt, hol nem volt, volt egyszer egy SQL Server 2008 R2 ahol se DDL Trigger, se audit, se semmi sem volt beállítva és dbo joga volt a fejlesztőknek az éles környzetben. Történt egy nap – nevezetesen szerdán – hogy valaki törölt egy táblát. E miatt sajnos a program nem az elvárásoknak megfelelően működött. Szerencsére mentés volt, de meg szerettük volna tudni, hogy ki követte el a dolgot, illetve, hogy mikor is történt az egész. Az időpontra azért volt szükség, hogy a point-in-time restore rendben működjön. Itt most a helyreállítás nem fontos, csak az, hogyan kaptunk vissza a “ki és mikor” információkat.

Szerencsés esetben legalább a default trace be van kapcsolva, így abban jó eséllyel meg lehet találni, hogy ki és mikor törölt egy adott objektumot. Az alábbi példa során a table1 tábla lesz kitörölve, amit az utolsó lekérdezés segítségével meg lehet keresni a default trace-ben:

USE [master];
GO
--létrehozok egy teszt adatbázist
IF DATABASEPROPERTYEX('TrLogRead', 'Version') > 0
	DROP DATABASE [TrLogRead];
GO

CREATE DATABASE [TrlogRead];
GO
--átállítom a recovery model-t FULL-ra
ALTER DATABASE [TrLogRead] SET RECOVERY FULL;
GO

USE [TrlogRead];
GO
--teszt tábla létrehozása
CREATE TABLE Table1
(
	col1 int identity,
	col2 char(1) default 'A'
);
GO
--teszt adatok létrehozása
SET NOCOUNT ON;
INSERT INTO Table1 DEFAULT VALUES
GO 1000

--adatbázis mentés
BACKUP DATABASE [TrlogRead] TO DISK = N'C:\temp\trlogread_full.bak' WITH INIT, STATS=10;

BACKUP LOG [TrlogRead] TO DISK = N'C:\temp\trlogread_1.trn' WITH INIT, STATS=10;
GO

--most kitörlöm a táblát
DROP TABLE Table1;

--újabb tr log mentés
BACKUP LOG [TrlogRead] TO DISK = N'C:\temp\trlogread_2.trn' WITH INIT, STATS=10;
GO

--a default trace mindig id=1
DECLARE @DefaultTracePath nvarchar(255);
SET @DefaultTracePath = (SELECT [Path] FROM sys.traces WHERE id = 1)

SELECT 
	[LoginName],
	[HostName],
	[ApplicationName],
	[StartTime],
	[DatabaseName],
	[ObjectName]
FROM 
	sys.fn_trace_gettable(@DefaultTracePath, DEFAULT)  T
JOIN
	sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE
	EventClass = 47
AND
	[DatabaseID] = DB_ID()
AND
	[ObjectName] LIKE '%table1%'

 

Ha nem vagyok ennyire szerencsés, akkor a transaction log-ban kell turkálni. Itt is, ha szerencsém van, akkor elég az aktív transaction log-ba belenézni, ha nem, akkor a mentésekben kell. Ezt az fn_dblog és az fn_dump_dblog nem dokumentált TVF-ek segítségével lehet.

Comments (5) -

Bécsy Márk 8/18/2012 6:01:42 PM

Ezt mintha direkt nekünk írtad volna, köszi! Smile

Érdekel a restore is Márk Smile ? Az egy kicsit viccesebb volt, mert volt más is, amit a transaction logok turkálásával szedtem vissza.

Bécsy Márk 8/18/2012 6:50:15 PM

Minden érdekel ami SQL Smile

Egyébként - szerencsére - nem egy konkrét, kitörölt tábla miatt lettem ilyen lelkes, csak az dobott fel, hogy a db-mókolásokat ilyen szépen vissza lehet követni. Ahol egy db-ben sokan, sok mindent csinál(hat)nak, ott ez néha jól jön(ne).

De az is igaz, hogy egy hét múlva már valószínűleg más típusú kihívásokkal kell majd szembenéznem. ;)

Bécsy Márk 8/18/2012 7:02:16 PM

Mintha eltűnt volna a kommentem... Szóval, érdekel, persze, (SQL Server körül minden) de bevallom, nem konkrét eset miatt lettem lelkes, hanem mert ez a pár infó sokszor jól jött volna és jól jöhet még. Bár az is igaz, hogy én tran logokat valószínűleg már csak kb. egy hétig túrhatok, utána legfeljebb kedvtelésből csinálhatok ilyet Smile

Bécsy Márk 8/18/2012 7:03:25 PM

"The comment is now awaiting moderation".

Ja, akkor nem tűnt el, csak nem tudok olvasni. Na akkor most válogathatsz, melyik megfogalmazás tetszik jobban Laughing

Add comment