1

This question already has an answer here:

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!

marked as duplicate by RolandoMySQLDBA, Marian, Paul White, Martin Smith, Max Vernon Jan 28 '14 at 22:44

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

10

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.

Restore a SQL Server Database to a Point in Time

Not the answer you're looking for? Browse other questions tagged or ask your own question.