Full Recovery or Stealth Simple

This query lists all databases on an instance that are using the FULL recovery model but do not have a full or differential backup to anchor the log chain – that way you don’t have to inspect each database on the server to find the offender.

SELECT

db.name,

recovery_model_desc,

last_log_backup_lsn

FROM

sys.databases AS db

INNER JOIN sys.database_recovery_status AS dbrs

ON db.database_id = dbrs.database_id

WHERE

db.recovery_model = 1

AND dbrs.last_log_backup_lsn IS NULL

You can test it out by placing a fully backed database into SIMPLE recovery then placing it back into FULL recovery. The query will now return the modified database. Backup the database and the query will now return an empty set.

This query is based on the concepts in an article by Paul Randal, in case you want to do some more digging: http://www.sqlskills.com/BLOGS/PAUL/post/New-script-is-that-database-REALLY-in-the-FULL-recovery-mode.aspx