Disaster Recovery In SQL Server

Disaster recovery :  It is the process of recovering data when any disaster occurs. It is very important to plan disaster recovery of information as information is very important asset for any organization. Disaster Can be man-made or Natural disaster like Fire or it can also be technical disaster like Disk Failure or system failure.

How to Plan Disaster Recovery ?

Before Planning Disaster Recovery You Must Know How Much “Data Loss Is Accepted By An Organisation”.

Let’s Say Organization Says 15 Min Data Loss Is Accepted.

You Database Planning Can Be

• Full Back In the Morning Or At Less Production Hour (When Your Database Is Not Being Accessed By Many Users).
• Take Transaction Log Backup Every After 15 Min As Max Data Loss Is 15 Min.
• Take Differential Backup Every After 1 Hour.

So Since Above Involves Log Backup. We Must Have Database recovery model either “FULL” Or “BULK LOGGED”.

Full Recovery Model Is Preferred As Point In Time Recovery Is not Guaranteed In BULK LOGGED Recovery Model.

Differential Backup Contains All the Transaction Since Last Full Backup Was Taken.

Now According To Above Planning We Have Following Backup Log History and we Encounter A Database Crash At 5:38 AM.

 

Time	Backup Type
12:00AM	Full Database Backup
12:15AM	Transaction Log Backup
12:30AM	Transaction Log Backup
12:45AM	Transaction Log Backup
1:00AM	Transaction Log Backup , Differential Backup
1:15AM	Transaction Log Backup
1:30AM	Transaction Log Backup
1:45AM	Transaction Log Backup
2:00AM	Transaction Log Backup, Differential Backup
2:15AM	Transaction Log Backup
2:30AM	Transaction Log Backup
2:45AM	Transaction Log Backup
3:00AM	Transaction Log Backup, Differential Backup
3:15AM	Transaction Log Backup
3:30AM	Transaction Log Backup
3:45AM	Transaction Log Backup
4:00AM	Transaction Log Backup, Differential Backup
4:15AM	Transaction Log Backup
4:30AM	Transaction Log Backup
4:45AM	Transaction Log Backup
5:00AM	Transaction Log Backup, Differential Backup
5:15AM	Transaction Log Backup
5:30AM	Transaction Log Backup
5:38AM	Database Failure (Database Crash)

 

Now You Are Left With Above Backup Files and A Crashed Database.

Crash Can Be :

• Disk Failure (Database Files Cannot Be Recovered)
• Data File Has been Corrupted.
• Log File Has Been Corrupted.
• And Many More…

Let’s Say Disk Failure Occurred And Database Files Cannot Be Recovered.

Now With Above Database Backup Files We Have To Do Fastest Recovery

Steps :

1. Restore Full Database Backup Taken At 12:00 AM With No-Recovery(No Recovery Specifies That There Are More Transaction Log Backup To Be Restored).
2. Restore Last Differential Backup Taken At 5:00 AM With No-Recovery (Since Each Differential Backup Contains Transaction Since Last Full Backup).
3. Restore Transaction Log Backup Taken At 5:15 AM With No-Recovery
4. Restore Transaction Log Backup Taken At 5:30 AM With Recovery.

So In Above Scenario Total Database Loss Is 8 Minute.

This 8 Minute database loss can also be minimized. Support You Database has been crashed but Log file is available.

Then You Can Take “Tail Log Backup From Log File” Tail Log Back Is Same as Transaction Log Backup But The Different Is Your Tail Log Backup Will Not Have Meta Data Information as Data File Is not Available.

So, If You Have Tail Log Backup Then You Can Must Apply

Steps :

1. Restore Full Database Backup Taken At 12:00 AM With No-Recovery(No Recovery Specifies That There Are More Transaction Log Backup To Be Restored).
2. Restore Last Differential Backup Taken At 5:00 AM With No-Recovery (Since Each Differential Backup Contains Transaction Since Last Full Backup).
3. Restore Transaction Log Backup Taken At 5:15 AM With No-Recovery
4. Restore Transaction Log Backup Taken At 5:30 AM With No-Recovery.
5. Restore Tail Log Backup Taken After Database Crash With Recovery.
   

So In This Way Data Loss can be minimized By Taking Tail Log Backup.