Setting the Recovery Mode

The recovery mode can be set using the SET clause of the ALTER DATABASE statement, the sp_dboption system stored procedure, or Enterprise Manager. To see which mode your database is in you can use Enterprise Manager (Database Properties, Options), or the DATABASEPROPERTYEX() property function:

 SELECT DATABASEPROPERTYEX('<databasename>', 'recovery') 

NOTE

The simple and bulk_logged recovery modes are intended to replace the SQL 7.0 trunc. log on chkpt. and select into/bulk copy options, respectively. However, select into/bulk copy and trunc. log on chkpt. can still be set using the sp_dboption stored procedure, but this will affect the recovery mode. For example, setting trunc. log on chkpt. to true would set the database into simple recovery mode. If both trunc. log on chkpt. and select into/bulk copy options are set, the lower recovery model, simple, takes precedence.