Backup Question


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


more ▼

asked Feb 10, 2011 at 06:54 AM in Default

avatar image

388 27 33 37

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Feb 10, 2011 at 07:52 AM

avatar image

40.9k 39 95 168

(comments are locked)
10|1200 characters needed characters left

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).

more ▼

answered Feb 10, 2011 at 09:12 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

An awful lot of luck comes into play for Tail-log backups to succeed. Yup - the nature of the failure is huge, and hard for all to give clear guidance due to the variables.

Feb 10, 2011 at 10:00 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 10, 2011 at 09:18 AM

avatar image

15.6k 22 57 38

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 10, 2011 at 06:54 AM

Seen: 1001 times

Last Updated: Feb 10, 2011 at 06:54 AM

Copyright 2018 Redgate Software. Privacy Policy