-
Titel
Does a full or differential database backup clear the online transaction log? -
Beschreibung
A question that is frequently asked when pursuing the best practice for MS SQL Server database backups is how to ensure no data is lost in cases of a production database disaster -
Lösung
The inactive parts of the online SQL Server transaction log are marked for clearing only when a transaction log backup is created
Full and differential database backups don’t contain much SQL Server transaction log information, only the transactions necessary to recover the database into a consistent state.
These transactions are not a backup of the online transaction log, therefore these transactions are not marked for overwriting in the online transaction log.
Having said that 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.