Full Recovery model.

  • With full recovery model, SQL Server preserves the transaction log until you back it up
  • The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable
  • The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated.
If a hardware failure occurs at 10:45 a.m, you would lose 15 minutes of work.  You can use the full database backup at 9 a.m, and apply the transaction logs to bring the database to its state at 10:30 a.m.  What if some critical data was deleted at 9:50 a.m?  Using the transaction log backup that was made at 10:00 a.m, you can restore the database to its state at 9:49 a.m.  You would still need to redo the work that was performed between the time of the deletion up to the time the error was discovered, as you cannot restore up to 9:49 a.m, skip the 9:50 a.m transactions, and restore the later transactions.

Here are some reasons why you may choose this recovery model:

  • Data is critical and data can not be lost.
  • You always need the ability to do a point-in-time recovery.
  • You are using database mirroring
Type of backups you can run when the data is in the "Full" recovery model:
  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups
  • Transaction log backups
How to set Simple recovery mode

ALTER DATABASE dbName SET RECOVERY Full
GO

Or 

Right click on DB --> Properties-->Options





No comments:

Post a Comment