To determine the recovery model for a database, run the following statement:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Recovery')
Values returned by this statement will be FULL for full recovery model, BULK_LOGGED for bulk logged model and SIMPLE for simple recovery model. A returned value of NULL means the database name is not a valid database.
In SQL Server, there are 3 recovery models available, namely simple, full and bulk-logged. Simple recovery model minimally logs most transactions, logging only the information required to ensure database consistency after a system crash or after restoring a data backup. Full recover model fully logs all transactions and retains all the transaction log records until after they are backed up. Bulk-logged recovery model minimally logs most bulk operations, such as index creation and bulk loads, while fully logging other transactions.