How to Know the Recovery Model of Your Database

Recovery model plays an important role in SQL Server because they decide the nature of your transaction log file and the recovery strategy. SQL Server supports three types of recovery models: Simple, Bulk-Logged, and Full.These SQL Server recovery models have their own properties. User can also change the recovery model after creating the database.Let’s see brief information about them:

Simple Recovery model: If your database is in simple recovery model then following operations cannot be performed on database-

• Database Mirroring
• Log Shipping
• AlwaysOn feature
• Point-in-Time recovery

You can use simple recovery model when:

• There is no need for point-in-time recovery
• Data can be derived and easily recreated
• Data is static

Full Recovery Model: A user can choose the full recovery model:

• For point-in-time data recovery
• When data became very important and user cannot lose the data
• For the use of advanced features like log shipping, mirroring, AlwaysOn etc.

Bulk-Logged Model: A bulk-logged recoverymodel is used to preserve the transaction log records after full backup of the database.

• Data is critical, but user doesn’t want to log large bulk operations
• Bulk operations complete at different times versus normal processing

Different type of backups that you can run on the bulk-logged model:

• Copy-Only Backup
• Differential Backup
• Complete Backup
• Partial Backup
• File/Filegroup backup
• Transactional log backup

After the small overview on recovery model, here we will know about multiple ways for checking the recovery models of SQL Server database:

Step 1: Using SSMS (SQL Server Management Studio)

Go to Object Explorer and Right Click on your Database-> Go to Properties ->Options ->Recovery Model

Step2: Using catalog View

User can also check the recovery model type by the following query:

Select name, recovery_model_desc FROM sys.databases

GO

The result has two columns, first is name of the database and second is recovery model type as shown in the figure:

Step3: Using Metadata Function:

DATABASEPROPERTYEX() is a metadata function to find the recovery model of the database. This function returns only one database at a time. Here is the query:

SELECT DATABASEPROPERTYEX (‘DatabaseName’,’RECOVERY’) “Recovery Mode1”

GO

The output will be:

Step 4: Using sp_helpdb

The user can also use the stored procedure sp_helpdb to check the recovery model of the databases.

EXEC sp_helpdb

GO

The output contains several fields and in the Statussection, a user can easily find the recovery model type.

Step 5: Using Object Explorer

In the object explorer, click on the Databasesand press F7. The output will be like this:

So these are the basic options to find the recovery model type of the database. 

Final Words: We have seen the different reasons to choose the recovery models and all the steps to find the recovery model of the database. When a user switches one recovery model to another recovery model then, he/she should be aware of its important points. Every recovery model has its own features and all are essential so choose your recovery model very carefully to save your database. 

»»  Read More...

Small Overview of SQL Server Recovery Models

SQL Server supports three recovery models: simple, full, and bulk-logged. Through these recovery models, you can manage log files as well as prepare your database for best possible recovery. Typically, a database uses the full recovery model or simple recovery model. You can also switch to another recovery model at any time.

Simple Recovery Model

This approach allows the database to be recovered to the most recent backup. If you used databases with simple recovery model that means you don’t want to take log backups, you may restore full or differential backups only. In this recovery model, database restoration to a given point in time is not possible, you may only restore it to the exact time when a full or differential backup occurred. If you are using this recovery model, you will automatically lose any data modifications made between the time of the most recent full/differential backup and the time of the failure.

Full Recovery Model

This approach allows the database to be recovered to the point in time of failure. In this model, SQL Server save the transaction log until you back it up. This model includes a combination of full and differential database backups in conjunction with transaction log backups. In case of database failure, you have the most flexibility restoring databases using the full recovery model. In addition to save data modifications stored in the transaction log, this model allows to restore a database to a specific point in time.

Bulk-logged Recovery Model

The bulk-logged recovery model works same like the full recovery model, the only difference is the handling way to bulk data modification operations. The bulk-logged model records these modification operations in the transaction log using a technical process known as minimal logging. Microsoft recommends that this model only be used for short time.

Conclusion

In this article I just covered the basics of sql database recovery models. After reading this article, You can move to the right direction- which recovery model to be used to prevent data loss. I strongly recommended Full recovery model  to avoid data loss and to achieve Point in Time Recovery.

»»  Read More...