Did you know: Changing your database to Full Recovery Model will not effect until we take Full Database Backup

Hi All,

In my today’s post I will be explaining a very basic stuff which we are expected to know but sometimes we may miss out on it and can lead to adverse consequences. My Field experience tells me most customer tend to forget it and hence I thought I would pen it down in this post.

When you create a new database on Sql server, it will not be in FULL RECOVERY model by default. The recovery mode of the new database is decided by the recovery model of the model database. So if your model database is in simple recovery mode, your new database which you create will be in simple recovery mode. Similarly if your model database is in FULL Recovery model (which is the default case), the new created database will also be in FULL Recovery model.

The relevance of FULL Recovery model is that the Transaction log of the database will not get truncated until we backup the Transaction Log. This is done to allow us to perform point in time recovery with the help of T-log backups.

So the expected behavior in FULL Recovery model is that T-Log will continue to grow and will not be truncated until the Tlog backup is completed and once the T-log backup is taken, the T-log gets truncated and will not grow until we take the next T-log backup at regular interval.

However this behavior will not take effect until you take at-least one FULL Database backup. So we need to ensure that whenever a new database is created, a FULL BACKUP is taken first followed by periodic T-Log backups

Another practical scenario where you might see this situation. Starting SQL 2008, the following TSQL command is deprecated

BACKUP LOG  <database-name> WITH TRUNCATE_ONLY

So if your database is in FULL Recovery model, your TLOG is  grown very large (since you don’t take Tlog periodically or your Tlog backups are failing which you are no aware of) So the only way to truncate Tlog is by taking Tlog backup.

However lets say if you cannot afford to take Tlog backup because of space constraints or other reasons and somehow you want to truncate and shrink logfile. So the only option we have in sql 2008 is to change  the recovery model of the database to simple which will truncate the Tlog file, shrink the Tlog file and change the recovery model of the database back to the Full.

However changing the recovery model of the database to Full is not enough, since as I said earlier after changing the recovery model to FULL we need to take a Full Database backup to bring the database to FULL RECOVERY model.

Hope this post refreshes our concepts

Parikshit Savjani
Premier Field Engineer, Microsoft SQL Server