Full Recovery Model

A full recovery model is database recovery model that completely logs all transactions and automatically stores the full set of log records until after they are backed up. Using a full recovery model means the database can be recovered all the way to the point of failure, assuming the tail of the log is backed up after the failure. Furthermore, all forms of recovery are supported under the full recovery model.

The entire database is offline for the duration of a full recovery model restore. The database cannot become operational until all data is recovered to a point where all parts of the database are at the same point in time and there are no uncommitted transactions.

With the full recovery model, following the restoration of your data backups, you must also restore all transaction log backups (tail-log backups) before recovering the database. It is also possible to restore a database to a specific recovery point in a log backup. You can choose a specific date and time, a marked transaction or a log sequence number for your recovery point.

According to Idera’s Robert L. Davis, there are a number of good reasons to use a full recovery model and regularly back up your transaction logs. “One reason you back up the log is to maintain its reusability and avoid growing the log file.”

Another important reason you back up log files is to avoid data loss. If you back up transaction logs, “you can replay those transactions if you have a disaster and need to recover the database.” With log file backups in the full recovery model, you can use log backups to bring the recovered database back up at your recovery point objective.

A Recovery Point Objective defines the amount of data you are willing to lose in the event of a disaster. If your RPO is 30 minutes, then your log backup frequency needs to be every 30 minutes or less.

Log backups also make point-in-time restores and page/file/filegroup level restores possible.