Some Quick Facts of Data Recovery

Its Friday -Fun of Data Recovery Day

Some Quick Facts

Fact 1) Who make sure that each committed transaction will be written to Transaction Log file before actual Data File in SQL Server?

 It’s a Write Ahead Logging (WAL) Mechanism, we call it a Buffer Manager too.It basically maintain ACID property in database.

Fact 2) What is Crash Recovery in SQL Server?

This is Database Recovery Process too which automatically initiate whenever SQL Server restarts, it always cross check as recovery process with the Transaction Log File for any reconcile between Transaction Log File and Data File. If there is any transaction that is been left to write in data file after writing it to transaction log file, it could be possible in case of certain database crash usually will be rolled forward to data file.

Fact 3) What is Redone and Undone in SQL Server?

Redone refers to Rolled Forward in Data Files and Undone is Rolled Back from Data Files.

In Case Transaction is been recorded in Log File but not in data file, it must have rolled forward to Data File and on the other side, if data is being recorded in Data File and not in Log File, it must perform a reverse physical operation on data file as “Undone”.

Fact 4) While performing Differential Database Backup how SQL Server knows which data is been changed after Full Backup?

A bitmap page is been referred in all data pages that contain a BIT for each extent in data files. Here 1 means, in one or more pages in an extent data is been modified since last full backup and 0 means no change. So, that’s how differential backups know which extent to be taken or which ones to leave.