Return
-
Title
Are SQL Server transaction log backups needed for successful disaster recovery if the full database backup is taken daily? -
Description
What happens when disaster occurs and we only have full database backup that are taken daily?
Are those enough for successful disaster recovery of all lost data? -
Resolution
This depends on how much data you can lose. If you can afford to lose up to 24 hours of data, then you don’t need transaction log backups and you should use the Simple recovery model
If the information you can lose is measured in minutes and hours, regular transaction log backups are necessary, as the maximum you will lose is the time between to transaction log backups
However, it is crucial we have full database backup chain and its not enough to have only full database backup to recover everything that is lost since full backup is not doing backup of all transactions from the online transaction logs.
Regularly created transaction log backups are mandatory here to ensure no data is lost.
Find more information on the SQL Server transaction log topic in the 10 most important SQL Server transaction log myths article.