Today we will discuss about various recovery models of SQL Server. A recovery plan is as important as water for a person who is searching for land in a desert. A recovery plan help to restore database whenever there is a database failure.
Each database on database server can be setup differently and have the ability to change the recovery model as needed.
Let's discuss the three plans in detail:
Simple
The simple recovery model gives a simple backup that can be used to replace our entire database in the event of a failure or if we have a need to restore your database to another server. With this recovery model we have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup). With this recovery model we are exposed to any failures since the last backup completed.
Why we may choose this recovery model:
Type of backups:
Bulk_Logged
With Bulk_Logged recovery model, bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log, are logged. The advantage of using this recovery model is that our transaction logs will not get that large if you are doing bulk operations and we have the ability to do point in time recovery as long as our last transaction log backup does not include a bulk operation as mentioned above. If no bulk operations are run this recovery model works the same as the Full recovery model. One thing to note is that if we use this recovery model we also need to issue transaction log backups otherwise our database transaction log will continue to grow.
Here are some reasons why we may choose this recovery model:
Type of backups you can run:
Full
The full recovery model is the most complete recovery model and allows to recover all data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that can recover our database to any point. In addition, if the database is set to the full recovery model we need to also issue transaction log backups otherwise our database transaction log will continue to grow forever.
Here are some reasons why you may choose this recovery model:
Type of backups you can run:
How to update / select Recovery Models
The recovery model can be changed as needed, so if your database is in the Full recovery model and you want to issue some bulk operations that you want to minimally log you can change the recovery model to Bulk_Logged complete your operations and then change your database model again. One thing to note is that since there will be a bulk operation in your transaction log, in backup you can not do a point in time recovery using this transaction log backup file that contains this bulk operation, but any subsequent transaction log backup can be used to do a point in time recovery.
Also, if your database is in the Simple recovery model and you change to the Full recovery model you will want to issue a full backup immediately, so you can then begin to also do transaction log backups. Until you issue a full backup you will not be able to take transaction log backups.
To change the recovery model you can use either SQL Server Management Studio or T-SQL as follows:
Management Studio
Right click on the database name, select Properties, select the Options tab and select recovery model from the drop-down list and select OK to save.
T-SQL
ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
-- set to Bulk Logged recovery
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
GO
-- set to Simple recovery
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
GO
1 comment:
Thanks for the update Alex. Great...
Post a Comment