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