SQL Server Full Recovery Model




By:
Overview

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. The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file.  This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason.  So, when the database is set to the "Full" recovery model, since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.

Explanation

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 and readable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.

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

  • Data is critical and you want to minimize data loss.
  • You need the ability to do a point-in-time recovery.
  • You are using Database Mirroring
  • You are using Always On Availability Groups

Types 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

Set SQL Server Full Recovery Model using T-SQL

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

Example: change AdventureWorks database to "Full" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

Set SQL Server Full Recovery Model using Management Studio

  • Right click on database name and select Properties
  • Go to the Options page
  • Under Recovery model select "Full"
  • Click "OK" to save
change database full recovery model

Last Update: 2/22/2019




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools