Identifying your Recovery Model

Identifying your recovery model is relatively simple and can be conducted in several ways. In this post I will illustrate the process of identifying your recovery model via the UI and with TSQL. Understanding the differences between the three model is important so here are some informative MSDN links that get pretty detailed in explaining each model.

Recovery Models: Simple | Full | Bulk-Logged.

Using the UI

Step 1:  Right click on the target database and select “Properties”

Step 2: Select “Options”

Step 3: Select the appropriate recovery model

As you can see it is very easy, but in order for you to determine the model for each database you would have to repeat these steps for each database. Not the most efficient way to spend your day. Now let’s get them all in one shot.

With TSQL

Step 1: From within the SQL Server Management Studio start a new query

Step 2: Run the following statement

SELECT name
       ,recovery_model_desc
FROM sys.databases
ORDER by name

Here are the results

Change Recovery Model via TSQL

Step 1: Run the following statement

ALTER DATABASE AdventureWorks
SET RECOVERY MODEL FULL -- FULL, SIMPLE, BULK_LOGGED

Here are the results after re-running the select statement