In the full recovery model SQL Server does not truncate committed transactions until they have been backed up. It allows the creation of 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 make regular log backups to keep it small.
Here are some tips on 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 for point-in-time recovery
- There is a necessity for individual pages restored
Let’s consider the following scenario:
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 the Full recovery model:
ALTER DATABASE your_database SET RECOVERY FULL
