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?
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?
