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.