I just wanted to confirm on this ...
I wanted to knw tht if i take a full backup at say 8am and a t-log back up at 11am and there is a catastrophic failure at 1 pm ...
so when i restore my backups I will loose any transactions from 11am to 1pm
plus if i restore my database and backup the active tail of my t-log (say active log has uncommited trasac from 12 50pm to 1pm)
then i would loose transac from 11am to 12 50pm ???
I hope i was clear in asking my doubt
asked Feb 10, 2011 at 06:54 AM in Default
To answer the first question, if you restore your full backup from 8:00 AM and your tlog taken at 11:00 AM then yes your database will be as it was at 11:00 am for any transactions completed from 8:00 am - 11:00 am. I am not sure what you are asking about with the active tail.
answered Feb 10, 2011 at 07:52 AM
But, it does depend on the nature of the catastrophic failure. You might be able to do what's called a tail log backup, backing up the information in the transaction log that has been committed, and then include that in your restore.
You're referring to the recovery part of a restore when transactions are committed or rolled back depending on where they were when the backup completed. That's very different than what I described above. And yes, you can't get that information because it's not a part of any backup that you've taken (as you described it).
answered Feb 10, 2011 at 09:12 AM
Grant Fritchey ♦♦
As Trad said, any transactions that happen past the last available backup are lost, unless you have something other than a backup which may have that data of course (log shipping and replication servers may just have it, though they should absolutely not be relied in as something like a backup substitute).
Depending on the type of failure, it may or may not be possible to do a backup of the transaction log tail (MSDN has some info on how to do this at http://msdn.microsoft.com/en-us/library/dd297499.aspx). It would be unusual to be able to back up part, but not all of the tail. So the scenario where you can back up the tail to 12:50 but not all the way to 1:00 that you are describing is fairly unlikely. If you somehow got in that situation, then yes any transactions after the part of the transaction log that you would be able to backup would be lost.
answered Feb 10, 2011 at 09:18 AM