Types of recovery models in sql server:
3 types of recovery models in sql server those are:
1.Full recovery model
2.Bulk-logged recovery model
3.Simple recovery model.
1.Simple recovery model:
In simple recovery model no log backups and automatically reclaims log space to keep space requirements small essentially eliminating the need to manage log space.
changes since the most recent backups are unprotected. In the event of a disaster,those changes must be redone. Can recover only to the end of a backup.
2.Bulk- logged recovery model:
Requires log backups An adjust of the full recover model that permits high-performance bulk copy operations.Reduces log space usage by bulk logging most bulk operations.
If the log is damaged or bulk -logged operations occurred since the most recent log backup,changes since that last backup must be redone.Other wise,no work is lost.
Can recover to the end of any backup.point-in-time recovery is not possible.
3.Full recovery model:
Requires log backups.No work is lost due to a lost or damaged data file can recover to an arbitrary point in time.
If the tail of the log is damaged,changes since most recent log backup must be redone.
Can recovery to a specific point in time assuming that your backups are complete up to that point.
How to find the recovery model in sql server :
select databasepropertyex('dbname','recovery')
or
select name,recovery_model_desc,recovery_model from sys.databases
Backup under the simple recovery Model:
The simple recovery model provides the simplest form of the backups and restore.Backup is easy to manage because transaction log in never backed up.However,if the there are no log backups, a database can be restored only to the end of the most recent backups of the data.If a failure were to occur,updates that are made after the most recent backups of the data are lost.
For example:
Five full database backups exit,but only the most recent backup(t1,t2,t3,t4),taken at the time t5 has to be restored.restoring this backup returns the database to the t5 point in time.All later updates respresented by the t6 box are lost.we can restored database up to t5.
Backup under the Full recovery model:
The full recovery model uses log backups to prevent data loss in the broadest range of failures scenarios,and backing and restoring the transaction log(Log backups) is required.
By using log backups we can recover the database up to the point -in-time recovery.Assuming we can backup the active log after a disaster occurs,you can restore the database up to the point of failure without data loss.
For ex:
A database backup,Db_1 and two routine log backups,log_1 and log_2 have been taken.some time after the log_2 log backup,data loss occurs in the database.Before three backups are restored ,the admin must back up the active log(the tail of the log).The database admin then restores db_1,log_1,and log_2 without recovering the database.then the database admin restores and recovers the tail-log backups(Tail).This recovers the databases to the point of failure,recovering all the data.
Backup under the bulk-Logged recovery Model:
The bulk logged recovery model is special-purpose recovery model that should be used only intermittently to improve the performance of certain large scale bulk operations,such as bulk imports of large amount of data.Much of the description of backup under the full recovery model also applies to the bulk-logged recovery model.
The best practice is to switch to the bulk -logged recovery model right before a set of bulk operations,perform the operations, and then immediately switch back to the full recovery model.
the bulk -logged recovery model minimally logs bulk operations,although fully logging other transactions.The bulk-logged recover model protects against media failure and, fo bulk operations,provide the best performance and least log space usage.
However,the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations,because bulk logging operations prevents recapturing changes on a transaction -by-transaction basis.If a log backup contains any bulk-logged operations,you cannot restore to a point-in-time within that log backup;you can restore only the whole log backup.
under the bulk-logged recover model,if a log backup covers any bulk operations,the log backup contains both log records and the data pages that were changed by bulk operations.This is necessary to capture the results of the bulk-logged operations.The incorporated data extents can make a log backup very large.Additionally,backing up the log requires access to the data files contain the bulk-logged transactions.If any affected databases file is in accessible,the transaction log cannot be backed up and all operations committed in that log are lost.
To track the data pages, A log backups operations relies on bulk-changes bitmap pages that contains a bit for every extent.For extent updated by bulk-logged operations,since the log backup,the bit is set to 1 in the bitmap.The data extent are copied into the log followed by the log data.
How to change the recovery model in sql server :
Alter database <database name> Set recovery Full/Bulk-logged/Simple.
3 types of recovery models in sql server those are:
1.Full recovery model
2.Bulk-logged recovery model
3.Simple recovery model.
1.Simple recovery model:
In simple recovery model no log backups and automatically reclaims log space to keep space requirements small essentially eliminating the need to manage log space.
changes since the most recent backups are unprotected. In the event of a disaster,those changes must be redone. Can recover only to the end of a backup.
2.Bulk- logged recovery model:
Requires log backups An adjust of the full recover model that permits high-performance bulk copy operations.Reduces log space usage by bulk logging most bulk operations.
If the log is damaged or bulk -logged operations occurred since the most recent log backup,changes since that last backup must be redone.Other wise,no work is lost.
Can recover to the end of any backup.point-in-time recovery is not possible.
3.Full recovery model:
Requires log backups.No work is lost due to a lost or damaged data file can recover to an arbitrary point in time.
If the tail of the log is damaged,changes since most recent log backup must be redone.
Can recovery to a specific point in time assuming that your backups are complete up to that point.
How to find the recovery model in sql server :
select databasepropertyex('dbname','recovery')
or
select name,recovery_model_desc,recovery_model from sys.databases
Backup under the simple recovery Model:
The simple recovery model provides the simplest form of the backups and restore.Backup is easy to manage because transaction log in never backed up.However,if the there are no log backups, a database can be restored only to the end of the most recent backups of the data.If a failure were to occur,updates that are made after the most recent backups of the data are lost.
For example:
Five full database backups exit,but only the most recent backup(t1,t2,t3,t4),taken at the time t5 has to be restored.restoring this backup returns the database to the t5 point in time.All later updates respresented by the t6 box are lost.we can restored database up to t5.
Backup under the Full recovery model:
The full recovery model uses log backups to prevent data loss in the broadest range of failures scenarios,and backing and restoring the transaction log(Log backups) is required.
By using log backups we can recover the database up to the point -in-time recovery.Assuming we can backup the active log after a disaster occurs,you can restore the database up to the point of failure without data loss.
For ex:
A database backup,Db_1 and two routine log backups,log_1 and log_2 have been taken.some time after the log_2 log backup,data loss occurs in the database.Before three backups are restored ,the admin must back up the active log(the tail of the log).The database admin then restores db_1,log_1,and log_2 without recovering the database.then the database admin restores and recovers the tail-log backups(Tail).This recovers the databases to the point of failure,recovering all the data.
Backup under the bulk-Logged recovery Model:
The bulk logged recovery model is special-purpose recovery model that should be used only intermittently to improve the performance of certain large scale bulk operations,such as bulk imports of large amount of data.Much of the description of backup under the full recovery model also applies to the bulk-logged recovery model.
The best practice is to switch to the bulk -logged recovery model right before a set of bulk operations,perform the operations, and then immediately switch back to the full recovery model.
the bulk -logged recovery model minimally logs bulk operations,although fully logging other transactions.The bulk-logged recover model protects against media failure and, fo bulk operations,provide the best performance and least log space usage.
However,the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations,because bulk logging operations prevents recapturing changes on a transaction -by-transaction basis.If a log backup contains any bulk-logged operations,you cannot restore to a point-in-time within that log backup;you can restore only the whole log backup.
under the bulk-logged recover model,if a log backup covers any bulk operations,the log backup contains both log records and the data pages that were changed by bulk operations.This is necessary to capture the results of the bulk-logged operations.The incorporated data extents can make a log backup very large.Additionally,backing up the log requires access to the data files contain the bulk-logged transactions.If any affected databases file is in accessible,the transaction log cannot be backed up and all operations committed in that log are lost.
To track the data pages, A log backups operations relies on bulk-changes bitmap pages that contains a bit for every extent.For extent updated by bulk-logged operations,since the log backup,the bit is set to 1 in the bitmap.The data extent are copied into the log followed by the log data.
How to change the recovery model in sql server :
Alter database <database name> Set recovery Full/Bulk-logged/Simple.
No comments:
Post a Comment