August 27, 2008 at 10:33 am
Hi everyone,
Can anyone explain simply the difference between LDF data and TRN data.
I am a bit confused.
I am backing up every night not more than total 2 Gb as a full-backup. It takes only few minutes. Do I really need to set a Transaction Log Backup Plan as well?
Thanks,
/Day
August 27, 2008 at 10:45 am
day (8/27/2008)
I am backing up every night not more than total 2 Gb as a full-backup. It takes only few minutes. Do I really need to set a Transaction Log Backup Plan as well?
Depends. If the drive crashes 10 min before the full backup would have run, can you afford to lose a full day's data? To answer the question of tran log backups, and how frequent they need to be, you need to know how much data loss is 'acceptable' in the case of a disaster.
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
August 28, 2008 at 1:06 am
Can anyone explain simply the difference between LDF data and TRN data
LDF file contains transaction log. Every database must have atleat one .LDF file.
TRN file contains transaction log backup. It will be created, only if you take log backup.
August 28, 2008 at 3:36 am
If you only have a full backup you can only restore to that full backup. So if your database dies towards the end of the day you can only go back to the last full backup and will lose all of the latest data.
If you have transaction log backups as well (frequency depends on factors such as recovery requirements) you will be able to restore to the latest log backup and therefore not lose much data. You can also do point-in-time recovery if you have transaction log backups. For most live systems a transaction log backup is required for these reasons.
August 28, 2008 at 11:30 am
Thanks everyone,
I have a better understanding now.
I would like to know when we restore a full back up, we restore full LDF if our recovery model is "full". What is it stored in LDF. Why we need full LDF file? We already restore the full backup. Even though LDF file is 0 byte we still have the full data if there is no change after the backup time.
I can understand TRN file and why we need them.
In which case, we would need full LDF data?
Thanks again.
August 28, 2008 at 11:39 am
Maybe these two blog posts will help?
http://www.sqlskills.com/blogs/paul/2007/10/25/DebunkingACoupleOfMythsAroundFullDatabaseBackups.aspx
http://www.sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply