🔥SQL Server Recovery Models Demystified | 🔍In-Depth Walkthrough

=================
*****Recovery Models*****:
=================
Recovery model is one the database property which controls the following :
What types of backups we can perform on DB side. - Whether we can perfom log backup are not?
How long we need to keep log records on ldf file.

Three types:

SIMPLE:
When checkpoint triggers, it will move the committed data to mdf file and truncate the inactive portion in log file.
EX: CHECKPOINT WITH TRUNCATE
We cant take log backup in simple recovery model.
We cant recover the data upto the point of failure and PIT restore is not possible.
Mostly we keep this in DEV environments.
No HA/DR supports in simple
Replication we can choose simple recovery model.

FULL:
When checkpoint triggers, it will move the committed data to mdf file and it wont truncate the inactive portion in log file.
EX: CHECKPOINT WITH NOTRUNCATE
We can take log backup , To truncate inactive portion we need configure log backups.
We can recover the data upto the point of failure and PIT restore is possible.
ALL PROD/TEST/ server should be FULL.may be DEV also we can keep in FULL.
ALL HA/DR supports in FULL
LOG Shipping
DB Mirroring
AlwaysON
Replication


BULK LOGGED:
When checkpoint triggers, it will move the committed data to mdf file and it wont truncate the inactive portion in log file.
EX: CHECKPOINT WITH NOTRUNCATE
We can take log backup , To truncate inactive portion we need configure log backups.
May or may not recover the data upto the point of failure and may or may not PIT restore is possible.
Only Log shipping supports bulk logged recovery model.

To improve bulk operation we will choose bulk logged recovery model.
In bulk logged log will minimaly logged.
SELECT INTO, INSERT WITH SELECT, BCP utility, SSIS packages
When we perform log backup will hold log records and data(BCM pages info)


Full to bulk-logged
==
Initiate a log backup
Perform bulk operations, immediately switch back the database to full recovery model
Initiate transaction log backup

Simple to FULL
==
Initiate a full (or differential, if full is already available) database backup
Schedule t-log backups

FULL or BULK_Logged to Simple
==
Disable the transaction log backup job
Ensure that there is a job to take full backup

USE master
GO
ALTER DATABASE MODEL SET RECOVERY SIMPLE ;  
SELECT name, recovery_model,recovery_model_desc FROM sys.databases  where name='model'

🔔 Subscribe for More SQL Server Tutorials

If you found this helpful, don’t forget to like, comment, and subscribe for more SQL Server guides, troubleshooting tips, database tutorials, and performance tuning videos.