September 23, 2008 at 12:30 am
Hi All,
Suppose I have a full backup, 3 differential backups and and a Transaction log backup. If the 3rd differential backup fails then can I restore the transaction log backup after the 2nd differential backup
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 23, 2008 at 1:29 am
Yes you can restore.
September 23, 2008 at 1:30 am
You should be able to. You'll have to have all the log backups made from the second diff backup, right up to where you want to restore to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 1:31 am
vyas (9/23/2008)
Yes you can restore.
what if the transaction log back up is taken after the 3rd differential bachup
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 23, 2008 at 1:44 am
Ahmad Osama (9/23/2008)
vyas (9/23/2008)
Yes you can restore.what if the transaction log back up is taken after the 3rd differential bachup
When was the previous log backup taken?
Or when was the log last truncated (either by an explicit backup log with truncate, or a switch to simple recovery and back)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 2:30 am
GilaMonster (9/23/2008)
Ahmad Osama (9/23/2008)
vyas (9/23/2008)
Yes you can restore.what if the transaction log back up is taken after the 3rd differential bachup
When was the previous log backup taken?
Or when was the log last truncated (either by an explicit backup log with truncate, or a switch to simple recovery and back)
consider the below given plan
Full backup
Differential backup 1
Differential backup 2
Tlog backup 1
Differential backup 3 (corrupt)
Tlog backup 2
now can v recover the tlog bachup 2
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 23, 2008 at 2:34 am
Given that, you would need to restore in the following order
Full backup
Diff 2
Tran log 1
Tran log 2
The transaction logs form a chain and you need all of the tran log backups, in order, from the last full/diff that you restore up until the point you're restoring to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 3:36 am
GilaMonster (9/23/2008)
Given that, you would need to restore in the following orderFull backup
Diff 2
Tran log 1
Tran log 2
The transaction logs form a chain and you need all of the tran log backups, in order, from the last full/diff that you restore up until the point you're restoring to.
what if I dont have the tran log 1 backup
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 23, 2008 at 3:47 am
As Gail said, you need the complete chain of Log backups from the last Full or Differential. Losing TLog1 would break the chain and you could only restore up to Diff2.
Cath
September 23, 2008 at 5:23 am
I am not tried this option
September 23, 2008 at 5:31 am
Ahmad Osama (9/23/2008)
what if I dont have the tran log 1 backup
You'll only be able to restore to Diff2. Trying to apply Tran log backup 2 will give an error saying something about the database not been rolled forward far enough
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply