Transcript Recover_data_without_backupx

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
[email protected]
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...
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!
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. 3rd party tools (ApexSQL log)
ALL METHODS REQUIRE CLONING
FIRST !!!
Extreme Recovery: Process Kill
(Why it works? When it will not work?)
DON’t DO IT!
Recovery by reading „empty” space
of transaction log
(reverse UPDATE and DELETE)
Recovery with 3rd party tool
(ApexSQL 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. FreeFileSync)
Summary
 CLONE files ASAP!
 Relax 
 Use your favourite method!
More info:
blog.sqlxdetails.com
Send questions about SQL Server:
[email protected]
Thank you!
Please give feedback to us
 http://speakerscore.com/sqlsaturday419