Recovery Model of SQL Database

Recovery Model of MS SQL Server database

Check the Recovery Model of MS SQL Server Databases:

SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases;

SELECT 'ADVENTUREWORKS' AS [Database Name],DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY') AS [Recovery Model];

Change the Recovery Mode with following command:

ALTER DATABASE dbName SET RECOVERY recoveryOption

Example:
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE

Option of RECOVER MODEL:
1. SIMPLE
2. BULK-LOGGED
3. FULL

SIMPLE Recovery Mode:
SQL Server maintains only a minimal amount of information in the transaction log. It can clean transaction log time to time. You can restore full or differential backups only. It is not possible to restore such a database to a given point in time. You can lost changes which you made after backup.

BULK-LOGGED Mode:
The bulk-logged model records these operations in the transaction log using a technique known as minimal logging.It will create minimal log of large transaction. It operate like full recovery mode. This saves significantly on processing time, but prevents you from using the point-in-time restore option.

Full Mode:
This Mode ensure the data is not lost that includes a combination of full and differential database backups in conjunction with transaction log backups.It allow you to restore point in time recovery with transaction log backup.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.