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.
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:
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