SQL Server 2008 Recovery Models

SQL Server:

SQL Server is a database management system that includes several data management and analysis technologies. It includes better compression features, which also helps in improving scalability and enhancing the indexing algorithms and introduce the notion of filtered indexes.

It also provides support for structured and semi-structured data. It includes capabilities for transparent encryption of data (TDE) as well as compression of backups through Recovery Models.



                                       Database Architecture

Note: Right Click on Database >> Go to Properties >> Go to Option. On the Right side you can find recovery model.







Note: This is a very easy method and it gives all the database information in one script.





RECOVERY MODELS:

Many of us have worked on systems where every minute of activity is important and needs to be backed up as soon as possible. Systems require different backup strategies, depending on the criticality of the information stored in them and before beginning to back up a SQL Server database, you need to know which recovery model the database is using, as by default, SQL Server databases are set to Full Recovery mode, which means they maintain sufficient logs to restore to any specific point in time. From SQL Server 2000 onwards microsoft supports three different recovery models: SIMPLE Recovery Model, FULL Recovery Model and BULK_LOGGED Recovery Model.



The following table summarizes the recovery models and backup types available with each recovery model.



SIMPLE Recovery Model:

The Simple Recovery Model as its name says is very "simple". In this model the SQL server maintains only a minimal amount of information in the transaction log. The databases using the Simple Recovery Model may restore full or differential backups only. When a database is set to Simple Recovery, it means log files are not kept permanently, so when a TSQL statement executes, changes are written to the data and log files, but they are not kept in the log file for long before being truncated. Ideally Simple Database Recovery Model is best suited for user databases which are in Development or Test environment, where there is a reduced need for frequent database backups. This recovery model is also suited for databases which mostly contain read only data or on those databases where changes happen infrequently.

T_SQL Sets to Simple Database Recovery Model:

ALTER DATABASE SemiProject
SET RECOVERY SIMPLE
GO

FULL Recovery Model:

The Full Recovery Model provides everything, as this allows the fullest set of backup options which in turn gives you the fullest set of recovery options. Every SQL Server database has a Transaction Log and every Insert, Update and Delete transaction that occurs is placed in the transaction log. Changing the Recovery Model to FULL tells SQL Server to keep all committed transactions in the Transaction Log until there is a backup. Once a backup has occurred, SQL Server will remove all committed transactions that have been added to the database from the log.

Benefits of FULL Recovery Model:

• Data is critical and data cannot be lost.
• You always need the ability to do a point-in-time recovery.
• You are using database mirroring

An added benefit is that it's possible to recover the database to a particular point in time. For example, if a user accidentally deletes all accounts in a database at 1 PM, it's possible to restore the database up to 12:59 PM, right before the deletion of the accounts occurred.

T_SQL Sets to Full Database Recovery Model:

ALTER DATABASE SemiProject
SET RECOVERY FULL
GO

BULK_LOGGED Recovery Model:

When the User database is in BULK_LOGGED Recovery Model the bulk operations such as CREATE INDEX, SELECT INTO, BULK INSERT, BCP etc are minimally logged within the transaction log thereby improving performance. The log backup and truncation methods that apply here are the same as in full Recovery mode so nothing really changes. We have to switch to Bulk_Logged mode when we need to do large bulk load operations. Recovery can only stop at the end of a transaction log. One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow.

This model is typically used if organizations need to run large bulk operations that degrade system performance and do not require point-in-time recovery.

T_SQL Sets to Bulk_Logged Database Recovery Model:

ALTER DATABASE SemiProject
SET RECOVERY BULK_LOGGED
GO