SQL Server Backup and Recovery Mode Information

The bellow queries help you to get information about backup and recovery mode in your server.
--When the last time  my databases are backed up

SELECT d.name, MAX(b.backup_finish_date) AS Last_Backup_Finish_Date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b
ON d.name = b.database_name AND b.type = 'D'
WHERE d.database_id NOT IN(2,3)
GROUP BY d.name
ORDER BY 2

--The physical place for backed up database
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily


--Database Recovery Mode
SELECT d.name, d.recovery_model, d.recovery_model_desc
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'L'
WHERE d.recovery_model IN (1, 2) AND b.type IS NULL AND d.database_id NOT IN (2, 3)




written by: Elmozamil Elamir