A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.
The three types of recovery models that you can choose from are:
- Full
- Simple
- Bulk-Logged
FULL - Recovery Model
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.
STEPs to Step-up via SSMS
STEP 1: Right
click on database name and select Properties
STEP 2: Go to the Options page
STEP 3: Under Recovery model select "Full"
STEP 4: Click "OK" to save
Why FULL 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
Step-up FULL Recovery Model via T-SQL
ALTER DATABASE <Database Name> SET RECOVERY FULL
GO
Simple - Recovery Model
The "Simple" recovery model is the most basic recovery model for SQL Server. Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed. Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the "Full" recovery model.
STEPs to Step-up via SSMS
STEP 1: Right click on database name and select Properties
STEP 2: Go to the Options page
STEP 3: Under Recovery model select "Simple"
STEP 4: Click "OK" to save
Why Simple Recovery Model?
- Your data is not critical and can easily be recreated.
- The database is only used for test or development.
- Data is static and does not change.
- Losing any or all transactions since the last backup is not a problem.
- Data is derived and can easily be recreated.
Types of backups you can run when the data is in the "Simple" recovery model:
- Complete backups
- Differential backups
- File and/or Filegroup backups
- Partial backups
- Copy-Only backups
Step-up Simple Recovery Model via T-SQL
ALTER DATABASE <Database Name> SET RECOVERY SIMPLE
GO
Bulk-Logged - Recovery Model
An adjunct of the full recovery model that permits high-performance bulk copy operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log. Can recover to the end of any backup. Point-in-time recovery is not supported. Log backups may be of a significant size because the minimally-logged operations are captured in the log backup. If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.
STEPs to Step-up via SSMS
STEP 1: Right click on database name and select Properties
STEP 2: Go to the Options page
STEP 3: Under Recovery model select "Bulk-logged"
STEP 4: Click "OK" to save
Why Bulk-Logged Recovery Model?
- Data is critical and you want to minimize data loss, but you do not want to log large bulk operations
- Bulk operations are done at different times versus normal processing.
Types of backups you can run when the data is in the "Bulk-Logged" recovery model:
- Complete backups
- Differential backups
- File and/or Filegroup backups
- Partial backups
- Copy-Only backups
- Transaction log backups
Step-up Bulk-Logged Recovery Model via T-SQL
ALTER DATABASE <Database Name> SET RECOVERY BULK_LOGGED
GO
