Today we noticed our transaction log backups had started failing for our user databases.

From previous experience, I had a hunch that someone had created a new database that was in SIMPLE recovery model. Just an FYI… That will cause the log backup to fail.

Hum… This server has hundreds of databases… Now which is the culprit.

Here is a query you can use to find the recovery model of all you databases.

SELECT 'DbName' = name  , 'RecoveryModel' = DATABASEPROPERTYEX(name, 'Recovery') FROM master.dbo.sysdatabases ORDER BY name

Now… To find which one is SIMPLE?

SELECT 'DbName' = name  , 'RecoveryModel' = DATABASEPROPERTYEX(name, 'Recovery') FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX(name, 'Recovery') = 'SIMPLE' ORDER BY name

Sure enough… There was a database in SIMPLE recovery model.

Hope that helps someone…