Close this search box.

BACKUP LOG is not allowed while the recovery model is SIMPLE

Last week when I was working on weekend for a customer I have experienced an odd behavior. I was working on migration, moving databases from one server to another. The source server is SQL Server 2012 and the destination server is on SQL Server 2014.  While I wanted to apply the log backup following the full backup it throws an error BACKUP LOG is not allowed while the recovery model is SIMPLE.

I asked myself, was I doing something wrong? No!! The database(s) are in FULL recovery mode, I did the full backup and then run log backup, why on the earth it should fail? Or why it would say BACKUP LOG is not allowed while the recovery model is SIMPLE? when database is in FULL recovery model?

log backup is not allowed


I recall a situation where I have experienced something like this, but it’s other way round. The databases are in SIMPLE recovery model and still it shows log_resuse_wait_desc as LOG_BACKUP, this behavior was mentioned here and it also has the connect item. The case that happened last Sunday was opposite, and I think it is a bug. There were few databases that I have changed the recovery model to FULL, confirmed the same and then ran the log backup. Ideally this should give me the log backup file, instead it throws me an error saying BACKUP LOG is not allowed while the recovery model is SIMPLE. Did change back the database recovery mode to SIMPLE, run a full Backup and then change recovery model to full again and ran the command. This has saved me a time and I was able to complete the migration successfully, but the question is, why this odd behavior? Does anyone have experienced similar situation in SQL Server family? May a good idea to file a connect item?

photo credit: Au Kirk What Now via photopin (license)

Leave a Reply

Your email address will not be published. Required fields are marked *

Picture of Hemantgiri Goswami

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.