Skip Navigation Links
SQL Server 2012
SQL Server 2014
SQL Server 2016
Practice Test
Tip of the Day : Currency Symbols
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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.

Back to Tip of the Day List Next Tip