Monday, August 20, 2012 10:10 AM
What is the difference between Transaction Log Backups and Full Backups?
Why would you use Transaction Log Backups instead Full Backups?
- Moved by Samuel Lester - MSFTMicrosoft Employee, Moderator Monday, August 20, 2012 10:14 AM (From:SQL Server Express)
Monday, August 20, 2012 10:23 AM
Full backups - http://msdn.microsoft.com/en-us/library/ms186289.aspx
Transaction log backups - http://msdn.microsoft.com/en-us/library/ms191429.aspx
Differential backups - http://msdn.microsoft.com/en-us/library/ms175526.aspx
Monday, August 20, 2012 10:24 AM
Full Backup is the backup of entire database
and as the name suggest, Transaction log back is backup of only transaction log, and this is critical for database recovery incase of database crash. You can read more about a trans log backup here
The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.
Monday, August 20, 2012 10:26 AM
here is one more article
Monday, August 20, 2012 1:25 PM
If setting up a Backup strategy - make sure it reflects the Recovery Point Objective (RPO) . For intsnace, you might want to take a FULL BACKUP every night - and hourly log backups during the day. This would allow you to recover last nights Full BACKUP and roll forward the LOG BACKUPs.
There is much flexibility in how you set up the timings of the backups.
Normally , you'll use both for the backup strategy. You'll need a FULL BACKUP for the Transaction Logs to be useful .
Jack Vamvas sqlserver-dba.com
Tuesday, August 21, 2012 10:32 AM
In Simple words, as name suggests, full backup is backup of entire database. And transactional log backup is backup of the transactional logs (each log backup file after the full backup is required to restore the database).
Also, it is mandatory to have full backup before taking log backups, else transactional log backups are meaningless.
Before planning for the backup strategy you need to consider few points on importance of the databases such as how much loss you can afford.
For taking transactional log backup recovery model of the database should be either full/Bulk-logged.
With transactional log backups you can have point in time recovery and you will be required to have all the transactional log backups after the latest full backup in order to restore the database.
- Proposed As Answer by Mayur Yelpale Tuesday, August 21, 2012 10:32 AM