SQL
Server provides an efficient way of backing up the databases by logging all the
transactions in log file but it becomes equally important to decide which data
has to be backed up and which not by limiting the logging process. In this
section we will discuss about the SQL Server
Recovery Models and its purpose.
What SQL Are Server
Recovery Models?
SQL
Server has a property which controls the way transactions must be logged and
its growth. This property comes under the SQL recovery models. This controls
the transaction log maintenance which can be different for different databases
as per its importance. It is important to choose right model as per the
requirements which can be also switched if required in future. To understand
these Recovery Models it is important to understand first what “Transaction Log
file” is.
Transaction
Log:
SQL Server has an internal mechanism which keeps a
detailed record of all the transactions made. This includes all type of
database modifications recorded as a string of log records that too in a
sequence of their creation. In case of any database malfunction or corruption,
these transaction logs can be utilized to bring back the database in consistent
state.
There
are three different types of recovery models;
- Simple
- Full
- Bulk-Logged
Simple
SQL Recovery Model:
As
name suggests, this model is the simplest one amongst all the models and lets
the transaction log file maintain minimal amount of information. SQL Server
itself truncates the log files removing the information about transactions
which have reached checkpoints (has been written to data file) to limit the
space. This space once freed can be used for other entries. Hence inn case of
this recovery model you should take precaution on keeping the backup interval
time less so that data loss is minimized but it can affect the production work.
Pros: This
model is extremely simple to be managed and requires and performs better as
minimum transaction log file space is used.
Cons:
Only recent full database or differential backups’ data is recoverable. This
Model has highest risks of data loss. It will not provide; Log Shipping,
Point-in-time restores, AlwaysOn or Database mirroring.
Usage: For
test environment SQL databases or some cases where data can be regenerated or
restored form the data source, this model can be utilized.
Full
SQL Recovery Model
Full
Recovery model is built to record and maintain each and every transaction made
until a full backup is taken which includes everything. This model can be used
as a disaster recovery strategy as it comprises full backup with along with
differential backup and transaction log backups. For controlling the increasing
size of transaction log, it should be backed up to make it truncated.
Pros:
This model provides complete protection and is foremost solution to keep data
available even after corruption in databases. One can recover data to any point
like right before the error occurred and hence is most effective strategy.
Cons: Maintenance
can be really tough as you will need to setup log backup ensuring the growth is
in control. Taking full backup can resolve the unnecessary growth of the
transaction log file.
Usage: Databases
which are extremely crucial and you don’t want to take any chances can be
formulated with Full Recovery model.
Bulk-Logged
SQL Recovery Model
Bulk-Logged
recovery model is same as Full recovery model with only difference that this
model handles the bulk data modification operations like (BCP, INSERT SELECT,
Index Creation, BULK INSERT, etc.) using minimal logging. Minimal logging
implies that only that much information will be logged which is required to
recover transaction without exhibiting any-time recovery. Processing time will
be saved using this model and it will also save log space usage. This in turn
restricts you from any-time-recovery option.
Pros: Transaction
log’s growth can be controlled as it will to minimal logging for bulk data.
Cons:
Any-time-recovery option will not be available like Full Recovery model.
Maintenance can be complicated.
Usage: You
can switch to bulk-logged model when bulk operations has to be done and as it
is done you can switch back to full recovery model.
How to Will You Change
Recovery Model
There
are two methods to View or Change Recovery Model;
- SQL Server Management Studio
- Using Transact – SQL
SQL
Server Management Studio:
Steps
to View or Change:
- Connect to apt instance of SQL Database Engine.
- Click Server Name in Object Explorer and expand the Server tree.
- Expand the Databases and right-click the database.
- Click Properties which in turn opens Database properties dialog box.
- Now in Select a Page pane click the Options.
- Here you will be able to see current Recovery Model in its list box.
- By clicking the drop-down menu you can change the Recovery Model.
- Once done click OK.
Using
Transact – SQL:
Steps to View Recovery
Model:
- Connect to Database Engine.
- Now, from Standard bar click New Query.
- Copy & Paste the below arguments to query window and click on Execute.
Steps to Change the
Recovery Model:
- Connect to Database Engine.
- Again, from Standard bar click New Query.
- Copy & Paste the below arguments to query window and click on Execute.
Conclusion:
You can choose Recovery Model for your SQL databases as per your requirements. Different Recovery Models can be chosen as per the importance of databases. For e.g. a very important database must have Full Recovery Model at the backend so that there are less chances of data loss. But it is extremely important to backup the transaction log file prior changing the Recovery Model of certain database’s transaction log file as any mistake can ultimately lose the data.
