0

I'v read and experienced too, that in the SQL server's full recovery model, the restore to a point in time doesn't work correctly if I have only full database backups. It works correctly only if I backed up the transaction log also. But why do I need to backup the transaction log separately if the log file is stored also in the full database backup? Moreover, why do I need to backup the transaction log at all (anywhere, anyhow) if it's always present with all data necessary for the recovery?

mma
  • 381
  • 2
  • 15
  • I would think this was off-topic for SO as it's not about any programming side of database administration. But I might be wrong! – underscore_d Apr 05 '18 at 16:01
  • My question is about the mechanism of SQL server. OK, then where is the right place to ask such a question? – mma Apr 05 '18 at 16:03
  • If your database are GB/TB in size, you dont want to restore from a Fullback it will take a ages, you restore from a differential backup or transnational backup. – mvisser Apr 05 '18 at 16:03
  • 1
    Voted to move to DBA stack exchange - one of your assertions is incorrect, the only part of the log file stored in a full backup is the log entries from the beginning to end of the backup, to ensure a restore can be consistent. – Andrew Apr 05 '18 at 16:04
  • 3
    The log file is not stored in the database backup, can you point to where you've read that? Also, think about this logically. If you take a full backup at noon, and have a disaster at 4PM, then want to restore to 3:59PM, even if the log file *was* stored in the backup you took at noon, how would it contain any log data from after noon? There would be no log data in *that* full backup to let you get to the 3:59PM point in time. Even if it did work that way, which it doesn't. – Aaron Bertrand Apr 05 '18 at 16:07
  • @AaronBertrand I've experienced only that if I restore a database from a full backup then the log file is also restored with its original size. – mma Apr 05 '18 at 16:11
  • 1
    That's only because the backup knows how big the log file is. How big a file is can be completely unrelated to *what's in it.* – Aaron Bertrand Apr 05 '18 at 16:12
  • @AaronBertrand Interesting, and a bit strange, isn't it? – mma Apr 05 '18 at 16:14
  • A full backup of a database is just that, a full backup. It's point in time, regardless of what backup mode you're using. The **only** way to get point in time is using the Transaction Log backup, as you need to know details of the transactions up to a specific point to be able to do point in time. A Full Back up does not include that information. – Thom A Apr 05 '18 at 16:14
  • OK, I understand. But why isn't used the live log file? Why must it be backed up? – mma Apr 05 '18 at 16:15
  • Imagine if i gave you 2 pictures, one where a wall was White, and another where it was blue; the pictures were taken today and a year ago. If I asked you to paint the wall the colour it was 6 months ago, could you, without knowing what had happened between? What if someone had painted it twice inbetween then, without details of what happened each day you'd have no idea. in this analogy, the Full Database backup is the pictures. A transaction log would be a piece of paper to go with it that might say "in June, Jane painted the wall Green. Then in December she painted it Red." – Thom A Apr 05 '18 at 16:16
  • OK, I understand. But why isn't used the live log file? Why must it be backed up? – mma Apr 05 '18 at 16:18
  • Technically, you back up the *transactions in the log*, not *the log file*. This is why you end up with a smaller `something.trn` file, not a simple copy of the `.ldf` file. You back up the log in between backups so that if you need to restore to a point in time *after the full backup*, you can "replay" the things that happened between the full backup and the point in time. Those things aren't in the full backup, of course, because they hadn't happened yet when the full backup was taken. But they will be captured in the log backups you take in the meantime. – Aaron Bertrand Apr 05 '18 at 17:10

0 Answers0