🔥SQL Server Recovery Models Demystified | 🔍In-Depth Walkthrough #sqlserver #azuresql #database

================= *****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.