Case Scenario - 1
Database Recovery Model is Full and scheduling is as follows: Full Backup Daily night at 09:00 PM and every 3 hours log backup
Time Action Done
09:00 P.M. Back up full database
12:00 A.M. Back up transaction log
03:00 A.M. Back up transaction log
06:00 A.M. Back up transaction log
09:00 A.M. Back up transaction log
10:00 A.M. Back up full database (Forced by User)
12:00 P.M. Back up transaction log
02:00 P.M. Failure occurs
1) Restore the latest database backup which is of 10:00 A.M. With NoRecovery.
2) Restore the transaction log backup which is of 12:00 P.M. With Recovery as that is our last log backup.
3) But we would restored data only till 12:00 PM and what happens to the data added/modified between 12:00 PM to 02:00 PMIs it lost and cannot be recovered?
The answer is neither yes nor no.It depends on the situation and criticality of the failure.So now if we can recover what would be the steps for the same.
1)Try taking a log backup,it will allow log to be backed up provided log files(.ldf) are not corrupt and the failure is only to data files.If log backup succeeds then you have database entirely upto the point of failure.
Command: BACKUP LOG database_name TO
2) Restore the latest database backup which is of 10:00 A.M. With NoRecovery.
3) Restore the transaction log backup which is of 12:00 P.M. With NoRecovery.4) Restore the tail transaction log backup With Recovery.
Case Scenario - 2
Database Recovery Model is Full and scheduling is as follows:
Full Backup Every Week Sunday night at 09:00 PM and Differential Everyday night 11:00 PM and every 1 hours log backup
Time Action Done
Sun 09:00 P.M. Back up full database
Mon 11:00 P.M. Differential Back up
Tue 11:00 P.M. Differential Back up
Wed 11:00 P.M. Differential Back up
Thurs 11:00 P.M. Differential Back up
Fri 11:00 P.M. Differential Back up
Sat 11:00 P.M. Differential Back up
Sun 12:00 A.M. Back up transaction log
Sun 1:00 A.M. Back up transaction log
Sun 1:10 A.M. Failure Occurs
Answer is
1)Try taking a log backup,it will allow log to be backed up provided log files(.ldf) are not corrupt and the failure is only to data files.If log backup succeeds then you have database entirely upto the point of failure.
Command: BACKUP LOG database_name TO
2) Restore the latest database backup which is of Sun 09:00 P.M. With NoRecovery.
3) Restore the latest Differential backup which is of Sat 11:00 P.M. With NoRecovery.
4) Restore the transaction log backup which is of 12:00 A.M. With NoRecovery.
5) Restore the transaction log backup which is of 01:00 A.M. With NoRecovery.
6) Restore the tail transaction log backup With Recovery.
NOW the question is What if Sat & Friday Differential backups are also corrupt or deleted.Then database can be restored only till Thursday ?
Answer would be a definite no.Database can be recovered till the point of failure as we would have log backups happening every 1 hour hence we would restore the Thursday Differential backup and restore all the logs from thursday and receovery the database with the tail backup.
No comments:
Post a Comment