Know How Log Backup Fail but Full Backup Succeed
In transactional log records, all the transactions and modifications in the database that is made by each transaction are present. It is an integral part of every SQL Server database. All the log records are produced at the time of logging process in a SQL Server database.
Table of Content
Operations Supported by the Transactional Log
- Individual Transaction Recovery
- All incomplete transactions can be recovered when the SQL Server is started
- Rolling a restored file, filegroup, database or page forward to the point of failure
- Support for transactional replication
- Provide support for disaster recovery
Most of the people have noticed that when they try to create log backup, it get failed. On the other hand, when the user tries to create the backup of the full database, the backup is completed successfully.
Therefore, in this blog, we are going to find out the reason why log backup fails but full backup succeed? Moreover, we are also going to discuss a reliable solution using which a user can eliminate this issue.
Error Message:
BACKUP LOG MYTECHMANTRA TO DISK = N'I:\BACKUPS\MYTECHMANTRA_Data..." failed with the following error: BACKUP detected corruption in the database log. Check the error log for more information. BACKUP LOG is terminating abnormally. However, at the same time, the full database backup will complete successfully without any issues and even DBCC CHECKDB will not discover any such errors.
Quick Glance on Transactional Log Backup & Full Database Backup
- Transactional Log Backup: A transactional log backup all the transactional log generated since the last time when the transactional log backup was completed successfully. Moreover, it also tries to back up the corrupt part of the TLOG. This result in the failure of transactional log backup.
- Full Database Backup: It will backup transaction log which will allow the database to be restored and recovered to a consistent point after the completion of the restore operation.
Causes Behind the Log File Corruption in SQL Server
- Viruses or Malicious Software: Due to virus attack, log files get infected & get damaged. Thus, the user is not able to access them.
- Terminating System Abnormally: When system or application closes abnormally, there are chances of getting log files corrupted.
- Input-Output Configuration: The I/O system is an important component where all the user and system databases are stored. Therefore, if any of the configurations get changed then it may lead to corruption in SQL log files.
- Storage Size Issue: It is one of the main reason behind the corruption of log files. When storage size exceeds the limit of a file then corruption occurs. Apart from this, SQL Server Clustering can also be another cause of corruption.
What to Do When Log Backup Fails?
There is one simple solution using which a user can eliminate this issue. To eliminate this corruption issue, user first has to switch the database recovery model from Bulk-Logged or Full to Simple Recovery Model.
Finally, the user needs to switch the recovery model of the database back to the Full. Then start the Full backup followed by the Transactional log backup to resolve the issue.
Conclusion
With the help of this blog, you will be able to understand why log backup fails but full backup succeeds. Some of the reasons behind the corruption of log files are also discussed.
It is important to eliminate all the situation that lead to corruption of log files. Moreover, an easy solution is also discussed in the above section so that a user can resolve the issue if it occurs.