Backup & Restore · SQL Server Tips

SQL Server Recovery Models

I would like to write about Recovery Models in Microsoft SQL Server. You can find about this topic anywhere in the internet, but I still want to write it down here in my own blog because I can easily find it when I need this information in the future.

As you may already know, there are three different Recovery Models in SQL Server. You should select proper recovery models for your database(s) to prepare for recovery in case of disaster which is :

  • Simple
  • Full
  • Bulk-Logged

Simple Recovery Model

Simple recovery model is the most basic recovery model in SQL Server. Every transaction is still written in transaction log, but once transaction is complete and data has been written to data file, the space that was used for transaction log was cleared and can be used for new transaction.

The reason why I might choose simple recovery models for a database :

  • Database is not critical and can easily re-created
  • The data is only used for test or development purposes
  • Data is static and doesn’t change.
  • Losing all transaction since the last backup is not gonna be a problem

Backup type that we can run in simple recovery model :

  • Full Backup
  • Differential Backup
  • File or File Group backup
  • Partial backup
  • Copy only backup

Full Recovery Model

This is the most complete recovery model in SQL Server. It is allow to recover all of the data to any point of time as long as  all backup files are usable. With this recovery model, all operation are fully logged that cause you can restore a database into any point of time. It is recommended to regularly take transaction log backup when using this recovery model because if not, your transaction log file will growing forever.

Reason why choose full recovery model :

  • Data is critical and cannot be lost
  • Need the ability to point in time recovery
  • Using high availability option (Database mirroring, log shipping, always on, etc)

Backup type we can run on full recovery model :

  • Full backup
  • Differential backup
  • Transaction log backup
  • File or File group backup
  • Partial backup
  • Copy only backup

Bulk-Logged Recovery Model

This recovery model is almost similar to full recovery model but some of bulk operation will not fully logged in transaction log file. Bulk operation that not fully logged such as BULK INSERT, CREATE INDEX, SELECT INTO, etc. With this recovery model transaction log will not as large as if using full recovery model. and still allows to perform point in time recovery as long as transaction log backup does not include any bulk operations. This recovery model will still need backup transaction log or it will continue growing.

Reason why choose bulk-logged recovery model :

  • Data is critical but don’t want to log large bulk operation
  • Bulk operation is done at different time rather then normal operation
  • Still need the ability to perform point in time recovery.

Each SQL Server database can only have one recovery model, but each databases on one SQL Server instance can have different recovery model depend on backup and recovery process need. Database recovery model can be changed anytime as you need using T-SQL or using GUI method on SQL Server Management Studio.

T-SQL to change database recovery model : 

Change database recovery model to full recovery model

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL
GO

Change database recovery model to simple recovery model

ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE
GO

Change database recovery model to bulk-logged recovery model

ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED
GO

Change database recovery model using SSMS :

Right click on Database Name from Object Explorer, click  Properties. Click Options page and change database recovery model as you need and then click OK.

change-recovery-model

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s