Backup and Restore – MsSql

MSSQL backup Models

Recovery Models in MSSQL and how that impact backups and restore

Simple recovery model: In this, all the transaction logs are written to the log files but till the period till the checkpoint occurs and those are not committed. Once the transactions are completed those logs are removed from the transaction logs. So these are not supported for transaction log backups and also point-in-time recovery can not be performed using SRM. You can only perform Full or differential backup.

Full Recovery Model: In this even after the transaction gets completed the transaction logs stay there until a log backup is performed. When transaction logs are full, the database stops accepting transactions until a log backup is taken, and the log file is expanded or truncated. So in FRM, you need to ensure that TLB is taken frequently enough to remove the completed.

Bulk-logged Model: This model minimizes the TL space when doing bulk INSERTS, UPDATE, OR DELETE. This model is the same except for minimizing logs for bulk, it reduces the amount of space.

Differential backup(DBK) overview:
This is based on the most recent, previous full data backup. It captures only the data that has changed since that full backup.
The full backup upon which a differential backup is based is known as the base of Differential backup.
Before you apply a restore to differential backup you must apply full backup first. Therefore it takes more steps than applying a full backup.
Under the full recovery model, using differential backups can reduce the number of log backups that you have to restore.
A dbk captures the state of any extents that have changed b/w the last base backup and when the differential backup is created.
So the size of the given dbk depends on the amount of data that has changed since the base backup was created. Generally, the older the base, the larger the new dbk will be. It takes the backup of extents which are changed only.
Take a new full backup at a set of intervals to establish a new differential base backup.

Transaction log backup: These are incremental which means you need to restore all transaction log backups after the full backup to have point-in-time recovery. You must take one full backup before you create any log backup. TLbk is cumulative which means each TLBK is different from other but there is a link in between them.

Full backup(Weekly) – Differential backup(Daily) -Transaction logbackup(Hourly)

Thanks,
Please let me know if you like this video.