If you are managing a SQL Server instance with a bunch of databases, checking and changing their recovery models one by one is a tedious chore. It is also really easy to miss one. This guide provides a straightforward, automated T-SQL script that does the heavy lifting by scanning your server and instantly updating all user databases to the 'FULL' recovery model in a single run.

[notewrap]The best part? It is designed to be completely safe. It ignores your core system databases, like master and msdb, which you never want to touch, and updates only the user databases that actually need it. Running this script is the quickest way to wipe out annoying log backup errors (like Error 4208) and ensure your entire environment is fully prepped for proper point-in-time recoveries.[/notewrap]

[SQL Server - Full Recovery Model]

CFFCS | CarrzSynEdit: | SQL Script

SELECT 
    bs.database name AS [Database],
    CASE bs.type 
        SELECT 'D' THEN 'Full Database'

        SELECT 'L' THEN 'Transaction Log'

    END AS [Backup Type],
    bs.backup start date AS [Backup Time],
    bmf.physical device name AS [Actual File Path Location]
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media set id = bmf.media set id
WHERE bs.backup start date > DATEADD(MINUTE, -30, GETDATE()) -- Look at the last 30 minutes

ORDER BY bs.backup start date DESC;