SQL Server Recovery Models Overview – Simple, Full, Bulk-logged
Recovery model plays very important role when it comes to data recovery or to minimise data loss. You need excellent understanding of your business requirements and importance of data. Depending on scenarios you have to decide whether you have to select simple, full or bulk-logged recovery model. Recovery model plays important role to decide recovery strategy. For example, in banking operations you can’t take risk to lose any transaction and each record is very important.
In SQL Server, there are three types of recovery models.
1. Simple Recovery Model
2. Full Recovery Model
3. Bulk-logged Recovery Model
How to check which recovery model is set for your database.
SELECT name "Database Name", recovery_model_desc FROM sys.databases GO
1. Simple Recovery Model
In simple recovery model, you will not be able to do log backups as it automatically truncates log. So it minimizes admin works and saves log space. But there are some risks also, if database is damaged or data has lost then you won’t be able to recover it if you don’t have backups. You can recover only the point where you have already taken backup. Simple recovery model truncates log after checkpoint, when checkpoint occurs SQL Server truncates log before the VLF which contains the Min. LSN value.
You will not be able to perform following operations in simple recovery model.
– Log Shipping
– Database Mirroring
– AlwaysOn feature
– Point-in-Time restores or data recovery etc.
You can use simple recovery model when:
– Data is not so important and you do not need point in time recovery.
– You can take risk to lose some data because your business requirement is different.
– You can manage if database is corrupted or damaged and you are comfortable losing data during the point between last backup and current failure point.
– You don’t want to backup and restore transaction log and you can manage with full backup and differential backup.
How to set Simple Recovery Model
USE master GO ALTER DATABASE Inventory SET RECOVERY SIMPLE GO
2. Full Recovery Model
Full recovery model is by default set when you install SQL Server. If you will create new database then recovery model will be set according to model database. In full recovery model all transaction logs are retained till the time of backup. In this recovery model you can recover the database till the point of failure if tail of the log is backed up.
You can use full recovery model when:
– You need point-in-time data recovery.
– Data is very important and you cannot lose any data.
– You want to use advanced features like log shipping, mirroring, AlwaysOn etc.
How to set Full Recovery Model
USE master GO ALTER DATABASE Inventory SET RECOVERY FULL GO
3. Bulk-logged Recovery Model
You can use bulk-logged recovery model when you are running very large operations or bulk operations. This recovery model reduces the transaction logging requirements for bulk operations. You can switch temporarily a database to bulk-logged recovery model when you are running bulk import or you are performing indexing operations on very large tables. It will increase performance using this recovery model rather than using full recovery model where each transactions are logged. This recovery model also retains transaction log records until they are backed up. Point-in-time recovery is not supported in bulk-logged.
How to set Bulk-logged Recovery Model
USE master GO ALTER DATABASE Inventory SET RECOVERY BULK-LOGGED GO
You need ALTER permission on database to view or change recovery models.
–> Different Ways to Find Recovery Models in SQL Server.
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
You may also like
