How to recover deleted data without backups Vedran

How to recover. . . deleted data without backups? Vedran Kesegić

Gold sponsors

Platinum sponsor

About me § M. Sc. , FER, Zagreb, Croatia § SQL Server Consultancy (performance) § Microsoft Certified Master, MCT § Author: XDetails (SQL Management Studio) § Hobby: Photography vedran. kesegic@gmail. com

Tough situation? § Data is deleted or updated by mistake § DB is in SIMPLE recovery model § There are no backups! It is impossible to recover!. . . almost

3 Methods: 1. Good method 2. Bad method 3. Ugly method Not for faint-hearted!

A bit of theory. . . <60 sec

React FAST! § Clone the files (Why FULL backup is NOT enough? ) § Put db OFFLINE § Copy files (both data and log) § Put db ONLINE § Relax § you bought time to recover the data § Needed by all 3 recovery methods!

DEMO Cloning the database (What is different from FULL backup? )

Recovery methods 1. Process Kill (sqlservr. exe) § Works if executed fast (before CHECKPOINT) § NOT recommended (violent, too short time) 2. Read „empty” part of transaction log § § Undocumented commands and trace flags Good, but not easy (will show in DEMO) § § Easy Costs money (well worth) 3. 3 rd party tools (Apex. SQL log) ALL METHODS REQUIRE CLONING FIRST !!!

DEMO Extreme Recovery: Process Kill (Why it works? When it will not work? ) DON’t DO IT!

DEMO Recovery by reading „empty” space of transaction log (reverse UPDATE and DELETE)

DEMO Recovery with 3 rd party tool (Apex. SQL Log)

Online options? Copy data and log files ONLINE? § § SAN snapshots VM snapshots (if running SQL in VM) VSS (Volume Shadow Service) Tools that use VSS (e. g. Free. File. Sync)