|
|
|
SQL Server Recovery Models
by
Nipun Tomar
on
Jan 12, 2012
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.
|
|
|
|
Comments: 0 Views: 353
|
|
|
|
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
|
|
|
|
|
|
|
|