Does the SIMPLE Recovery Model perform the same transaction logging as FULL Recovery Model? i.e. keep record of each insert/update/delete transaction?
If this is the case, then why should FULL Recovery model be used? In SIMPLE Recovery, Checkpoints run every minute on a busy database, so the data is written to disk.
Where does the data loss potentially occur on SIMPLE Recovery Model?
Thanks!
Does the SIMPLE Recovery Model perform the same transaction logging as FULL Recovery Model?
Technically, no. The simple recovery model minimally logs certain operations under certain conditions, just like the bulk-logged recovery model.
keep record of each insert/update/delete transaction?
Yes, your data modifications will be logged (with a few DML exceptions, like SELECT INTO and INSERT ... SELECT under certain conditions).
then why should FULL Recovery model be used?
For point-in-time recovery. With the simple recovery model, you can only restore full and differential backups. In other words, if you take a full backup at 2pm today, and you have a disaster at 5:05pm that demands a restore, then the most recent backup that you can restore will be your 2pm backup. 3+ hours of data loss.
But if you were in the full recovery model, and you had a full backup at 2pm today, followed by hourly transaction log backups and if the same disaster happened at 5:05pm, you could restore your 2pm full backup, and then restore your transaction log backups in between. Depending on if your database's transaction log was affected or not by the disaster (in other more technical words, if you can backup the tail of the log) then you could potentially have zero data loss, by recovering to the point of the disaster. Even if your transaction log is no longer available, and you can't take a tail log backup then you could still restore to your most recent transaction log (your SLAs will dictate your recovery point objective, and that will influence how frequently you take your transaction log backups).
Where does the data loss potentially occur on SIMPLE Recovery Model?
Provided that the database isn't salvageable at all, then it'll be from the last full/differential backup to the time of disaster.
