Full Recovery Model

[Total: 9    Average: 4.4/5]

In the full recovery model SQL Server does not truncate committed transactions until they have been backed up. It allows creating full, differential and transaction log backups thus making point-in-time recovery possible.

You need to keep in mind that in the full recovery model the transaction log file would be much larger and you have to maker regular log backups to keep it small.

Here are some tips when the full recovery model is better to use:

  • There is a big necessity to recover all data
  • The database contains some filegroups and there is the need to restore of read/write secondary filegroups and, optionally, read-only filegroups one by one
  • There is a necessity of point-in-time recovery
  • There is a necessity of individual pages restore

Let’s consider the following scenario:

Full Recovery Model In the scenario above the failure occurs at 19:00, meaning that the changes made within the period from 18:59 to 21:00 will be lost. The best way out in these circumstances is to restore the full backup (10:00) then the differential backup (16:00) plus transaction log backups (18:00 and 20:00). The transaction log backup that was made at 20:00 allows to roll back to as far as 18:59.

Here is the sequence of commands for restoring this database to 18:59:59:

RESTORE DATABASE your_database FROM DISK = 'full_10_00.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE your_database FROM DISK = 'diff_16_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'log_18_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'log_20_00.bak' WITH STOPAT = '2015-11-19 18:59:59.000', RECOVERY

This is how you can set Full recovery model:

ALTER DATABASE your_database SET RECOVERY FULL