Transaction Log Backups and Full Backups

Question

What is the difference between Transaction Log Backups and Full Backups?

Why would you use Transaction Log Backups instead Full Backups?

---

CG

Answer 1

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

---

Thanks, Andrew

Answer 2

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

http://msdn.microsoft.com/en-us/library/aa173551(v=sql.80).aspx

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.

Regards
Satheesh

Answer 3

here is one more article 

http://blogs.technet.com/b/beatrice/archive/2008/07/24/full-backups-transaction-logs-backup.aspx

http://www.backup-sql-server.com/faq/sql-simple-recovery-vs-full-recovery-vs-bulk-logged-recovery.htm

Regards
Satheesh

Answer 4

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

Answer 5

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.

Thanks,
Mayur Yelpale