TRANSACTION LOG BACKUP and RESTORE SEQUENCE: Myths & Truths

Hi Guys

In a recent issue with a customer, I came across a strange SQL belief (or say a DBA Myth) around SQL Server Transaction Logs Backup/Restore. Let me explain you what was the scenario and then let’s try attempting to break the myth with real testing.

SCENERIO:
-    Customer is taking FULL and TRANSACTION LOG (T-Log) backup of a critical database
-    Backup files are maintained on disk in sequential order
-    Due to a hardware failure (let’s assume), the database got crashed and customer decided to recover from the backups
-    Due to an another issue (assuming again!!!) the  most recent FULL backup file is LOST!. So customer is now only left with 1st FULL backup and successive T-Log backups
-    Customer is now confident that he will not be able to perform point-in-time recovery. “THIS is the MYTH”

Read the complete latest POST on SQLServerFAQ and demystify this one

About SQLServerFAQ

SQLServerFAQ blog contains a plethora of information spread across various aspects of troubleshooting commonly encountered issues with SQL server. The posts are contributed by SQL PSS (Product Support) Engineering community.

Comments

• Anonymous
  December 05, 2010
  IF all the Log backups after the 1st full backup are available, inatact and usable then one can use them to restore the DB and point in time is possible.

• Anonymous
  December 05, 2010
  Hi Monika, Yes, you are absolutely correct. So, over here everything depends upon BACKUP LOG CHAIN. Which means that even if any interim FULL and DIFF backups are missing, but T-LOG are intact, we can do point-in-time recovery. Cheers!! Varun