SELECT D.recovery_model_desc FROM sys.databases D WHERE name = 'MyDB'

要么

SELECT name,D.recovery_model_desc FROM sys.databases D

要么

SELECT DATABASEPROPERTYEX('MyDB','Recovery')