A recovery model is a database property that controls how transactions are logged, whether the transaction log requires backing up, and what kinds of restore operations are available.
There are three main Recovery model in SQL server:
1.Simple Recovery Model: No Log backup is possible in SIMPLE recovery model. When a check point occurs, SQL server truncates the Transaction log and leaves No log entry for DR purpose.
Features which cannot be possible in SIMPLE recovery:
1. Point-in time restore
2. Log Shipping
3. Mirroring and Always-on
4. Media recovery without data loss
In case of any Disaster, any changes since the most recent Full/Differential backup will lost. Those changes must be redone.
2.FULL Recovery Model: SQL server preserve the transaction log until we back it up, i.e. we take the log backup. We can recover to a specific point in time. If the Tail of log is damaged we can lost the transactions occurred since most recent log backup.
3.Bulk-Logged Recovery Model: It is an adjunct of the full recovery model that permits high-performance bulk copy operations. It reduces log space usage by minimally logging the Bulk operation.we Can recover to the end of any backup. Point-in-time recovery is not possible.If the log is damaged or bulk logged operations
occurred since the most recent log backup, changes since that last backup will lost.
The model system database sets the default recovery model of new databases.
Switching the Recovery Model:
1. Before switching from the full recovery or bulk-logged recovery model,always back up the transaction log.
2. if we run transactions under the bulk-logged recovery model that might require a transaction log restore, these transactions can lead to data loss.
We switch to the bulk-logged recovery model only under the following conditions:
A. Users are currently not allowed in the database.
B. All modifications made during bulk Operations are recoverable without depending on taking a log backup.
If we are OK With these two conditions, we will not be exposed to any data loss while restoring a transaction log which was backed up under the bulk-logged recovery model.
NOTE:
1.Switching between Full and Bulk-logged recovery model: After Completing the Bulk operation, switch back to FULL Recovery model from Bulk-logged immediately.always backup the log after switching to FULL from Bulk-Logged recovery model.
2.Switching from the SIMPLE to FULL/Bulk-Logged recovery Model:Immediately take the FULL backup to initialize the log chain. Full/Bulk-Logged recovery model takes affect only after FULL backup. Always take the T-log backup frequently enough to prevent the Transaction log file grow.
We can change the recovery models of databases using T-SQL commands and GUI through SSMS.
1. Change the recovery Model using T-sql commands:
2. Change the recovery model using GUI
There are three main Recovery model in SQL server:
1.Simple Recovery Model: No Log backup is possible in SIMPLE recovery model. When a check point occurs, SQL server truncates the Transaction log and leaves No log entry for DR purpose.
Features which cannot be possible in SIMPLE recovery:
1. Point-in time restore
2. Log Shipping
3. Mirroring and Always-on
4. Media recovery without data loss
In case of any Disaster, any changes since the most recent Full/Differential backup will lost. Those changes must be redone.
2.FULL Recovery Model: SQL server preserve the transaction log until we back it up, i.e. we take the log backup. We can recover to a specific point in time. If the Tail of log is damaged we can lost the transactions occurred since most recent log backup.
3.Bulk-Logged Recovery Model: It is an adjunct of the full recovery model that permits high-performance bulk copy operations. It reduces log space usage by minimally logging the Bulk operation.we Can recover to the end of any backup. Point-in-time recovery is not possible.If the log is damaged or bulk logged operations
occurred since the most recent log backup, changes since that last backup will lost.
The model system database sets the default recovery model of new databases.
Switching the Recovery Model:
1. Before switching from the full recovery or bulk-logged recovery model,always back up the transaction log.
2. if we run transactions under the bulk-logged recovery model that might require a transaction log restore, these transactions can lead to data loss.
We switch to the bulk-logged recovery model only under the following conditions:
A. Users are currently not allowed in the database.
B. All modifications made during bulk Operations are recoverable without depending on taking a log backup.
If we are OK With these two conditions, we will not be exposed to any data loss while restoring a transaction log which was backed up under the bulk-logged recovery model.
NOTE:
1.Switching between Full and Bulk-logged recovery model: After Completing the Bulk operation, switch back to FULL Recovery model from Bulk-logged immediately.always backup the log after switching to FULL from Bulk-Logged recovery model.
2.Switching from the SIMPLE to FULL/Bulk-Logged recovery Model:Immediately take the FULL backup to initialize the log chain. Full/Bulk-Logged recovery model takes affect only after FULL backup. Always take the T-log backup frequently enough to prevent the Transaction log file grow.
We can change the recovery models of databases using T-SQL commands and GUI through SSMS.
1. Change the recovery Model using T-sql commands:
0 comments: