check database recovery mode is simple or full?

Question

I want to check the database is set to full or simple recovery mode using C# and SQl queries.
how do i check this?

Answer 1

There are two SQL queries that will return the recovery model of a given database: 

SELECT DATABASEPROPERTYEX('name_of_the_database', 'Recovery');

or

SELECT recovery_model_desc 
FROM sys.databases
WHERE name = 'name_of_the_database';

If you're using C#, you may want to use SQL Management Objects (SMO). The property that gets or set the recovery model of a database using SMO is described here:  http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.recoverymodel.aspx.  General information about SMO begins here: http://msdn.microsoft.com/en-us/library/ms162169.aspx

Keep in mind that there are 3 recovery models:  full, simple, and bulk_logged.

Regards,
Gail

---

Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights