October 11, 2013 at 6:00 am
I have one scenario based query based on this backup schedule:
Full Backup: Monday/Thursday @10:00 PM
Differential Backup: Tuesday/Wednesday/Friday/Saturday @10 PM
Transaction Backup: Every 2 hours.
Now say my Full backup on Thursday is unusable (due to corruption or any issue), if the db crashes at 11:00 am on Saturday, how can I restore the database back upto the maximum usable state ?
October 11, 2013 at 6:10 am
Restore your last good full backup and all transaction log backups after it.
John
October 11, 2013 at 6:14 am
Restore Monday full, Wednesday diff, all log backups from that backup up to the point that 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
October 11, 2013 at 6:23 am
So the full backup doesnot impact the chain of t-logs ?
October 11, 2013 at 6:25 am
No. Nor does the differential.
John
October 11, 2013 at 6:31 am
sqlnaive (10/11/2013)
So the full backup doesnot impact the chain of t-logs ?
No and never has.
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
October 11, 2013 at 6:33 am
Thanks John & Gail. I would be very thankful if you could provide any reference doc which can describe this part. Seems like I've got a thing to clear up on weekend. 🙂
October 11, 2013 at 6:40 am
Books Online?
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
October 11, 2013 at 6:52 am
In your case
you should follow the following steps.
1. restore database <dbname> from file = '<full backup file path>' with norecovery
2. restore database <dbname> from file = '<Diff backup file path>' with Recovery (if you not specify any thing By default it will take WITH RECOVERY option)
And Then Logs file(keep in mind the Sequence, if any file is skipped then u may lose ur TRN back up advantage) :
RESTORE LOG <dbname>
FROM DISK = ''<transnational log file 1>'
WITH NORECOVERY;
RESTORE LOG <dbname>
FROM DISK = ''<transnational log file 2>'
WITH NORECOVERY;
RESTORE LOG <dbname>
FROM DISK = ''<transnational log file 3>'
WITH NORECOVERY;
.
.
.
.
.
.
.
RESTORE LOG <dbname>
FROM DISK = '<transnational log file (The Last TRN Backup)>'
WITH RECOVERY;
October 11, 2013 at 7:26 am
GilaMonster (10/11/2013)
Books Online?
I tried checking info about transaction logs from following site but couldn't get any:
http://technet.microsoft.com/en-us/library/ms190925.aspx
Now I found this site:
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
It says that "A transaction log backup contains all the transaction log records generated since the last log backup (or full backup that starts a log backup chain)"
So in my mentioned scenario, new t-log chain should be started after Thurday night Full backup (?)
This link further states that "A log backup chain is an unbroken series of log backups that contain all the transaction log records necessary to recover a database to a point in time. A chain starts with a full database backup, and continues until something breaks the chain, thus preventing more log backups being taken until another full (or differential) backup is taken."
I am confused. :unsure:
October 11, 2013 at 7:35 am
October 11, 2013 at 7:50 am
It is awesum John. Very big thanks.
Also a masterpiece article for Gail. I'm going to share it with many of my DBA friends as even they were confused on the same scenario.
You both made by weekend. :-):-):-):-):-)
TGIF... Cheers.... 🙂
October 11, 2013 at 9:26 am
sqlnaive (10/11/2013)
It says that "A transaction log backup contains all the transaction log records generated since the last log backup (or full backup that starts a log backup chain)"
Correct
So in my mentioned scenario, new t-log chain should be started after Thurday night Full backup (?)
No, because a full backup does not ever break a log chain therefore does not always start one. The only time a full backup starts a log chain is when there was no log chain prior to that point.
This link further states that "A log backup chain is an unbroken series of log backups that contain all the transaction log records necessary to recover a database to a point in time. A chain starts with a full database backup, and continues until something breaks the chain, thus preventing more log backups being taken until another full (or differential) backup is taken."
Again perfectly correct. A log chain starts with that first full backup and then proceeds until something breaks the log chain. Since full backups don't break the log chain, they cannot be that 'something' that breaks the chain.
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
October 14, 2013 at 1:25 am
Thanks a lot Gail. This nature of t-logs chain was wonderful to know.
Also thank you John for clarifications.
October 14, 2013 at 2:25 am
I keep recommending this, but this free eBook may help you: http://www.sqlservercentral.com/articles/books/89519/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply