June 5, 2019 at 3:10 pm
I am reading an article about transaction log backup, I am a bit confused about:'Transaction log backups are also not possible in the full or bulk-logged recovery models until a full database backup has been performed. The database engine automatically truncates the log in this scenario because there is no point in retaining transaction log records until the first full database backup.'
Can anyone explain this a little? why we need a full backup first?
June 5, 2019 at 3:14 pm
The point of a backup is to be able to restore the database.
If there is not an initial full backup (backup chain), there will be no way to restore the log backups. It creates the starting point for a restore.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 5, 2019 at 3:51 pm
I see, its for the purpose of restore. Thank you for your explaination
June 12, 2019 at 9:04 am
The transaction log records what happens in the database (the changes since a certain point - when the last full backup took place), whereas a full backup records the current state of the whole database.
The list of what happened is meaningless if you don't know what you started with.
Think of the transaction log like a lost of directions... "straight ahead 300m, turn 90 degrees left, continue for 50m then turn 45 degrees to the right. Carry on for 75m then stop."
None of that tells you anything useful about anything, until you lay it on top of the full backup, i.e. a map showing your current location and which way you are facing.
The transaction log is a list of directions about what happened in the database: "Add a row to table x containing values a,b and c; Update row 73 of table y changing column d to the value 17; drop table z; create table z with columns i,j,k; drop table z"
None of that has any meaning unless you start with the whole database in its original state (i.e. as in the full backup) then you can apply those same changes (from the transaction log backup) to get back to the state the database was in when you took the transaction log backup.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply