39
loading...
SQL Server Recovery Models: A Quick Guide
A recovery model in a Structured query language (SQL) Server manages the transaction logs and handles how they are logged, backed up, and restored. The SQL Server database contains a master database file (MDF) and a log database file (LDF). The MDF data file consists of all the database objects, such as tables, stored procedures, and information. The LDF log file consists of all the logs, such as transactions that occur in a database. There are three types of recovery models in SQL Server:
In this blog post, we will see how to find the existing model in our database, the pros, and cons of the three recovery models in SQL Server, and how to change the model.
You can check what the existing recovery model in your database is using either of the following ways.
Follow these simple steps to find out what the existing recovery model is using the SQL Server Management Studio:
You can also use the following SQL query to find the recovery model in a database.
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'My_Database_Name' ;The three recovery models (simple, full, and bulk-logged) determine the backup and restore options for a database. Based on the type of the recovery model, we can restore the data if there is a malfunction or crash in the database.
The basic recovery model in SQL Server is the simple recovery model. This model automatically removes the transaction log records on every completed transaction. So, it doesn’t support transaction log backups, only full or differential backup.
If there is a malfunction or crash in the database, then you can restore it with the recent full or differential backup. We cannot perform a point-in-time restore when using the simple recovery model. So, this may lead to a loss of the data that was modified between the time of recent full or differential backup and the time of failure.
The full recovery model in SQL Server maintains the transaction logs until it is backed up. Using this model, we can restore the database at any point in time and there will not be any data loss.
Like the simple recovery model, the full recovery model records the transaction logs. But, it will not remove them automatically on each completed transaction. Along with the insert and update transactions, the transaction logs also record the creating and altering indexes. This process makes the log file size huge, as each transaction is recorded. So, this process requires administration to closely monitor the growing log size.
If the transaction log becomes full, the database will not accept further transactions until the log file is either backed up or truncated.
The bulk-logged recovery model is similar to the full recovery model. The only difference is that the transaction log size is minimized when performing bulk-logged operations like bulk insert, select into and create index. So, the transaction log size in this model is small when comparing to the full recovery model.
Additionally, the bulk-logged recovery model increases the performance of large bulk operations, due to the minimal logging of the bulk transactions. In some cases, this model won’t support the point-in-time restore. The point-in-time restore can be done only if no bulk-logged operations are performed during the time of database malfunctioning or crashes.
If you perform a bulk operation, then we can restore the database only to the last transaction log before the bulk operation was recorded.
Before changing the recovery model of a database, we need to check the database activity. It is recommended that we change the recovery model during a time of lower database activity.
Before changing the recovery model, we need to make a full backup of the database. If anything goes wrong, we can immediately restore it to its original state.
You can change the recovery model of an SQL database using either of the following ways.
Follow these steps to change the recovery model using the SQL Server Management Studio:
You can also use the following SQL query to change the recovery model of a database.
USE [My_Database_Name] ;
ALTER DATABASE [My_Database_Name] SET RECOVERY FULL ;Thanks for reading! I hope you have a clear idea of the three different recovery models in SQL Server, how to check the existing one, and how to change it in your database. Try out the steps in this blog post and leave your feedback in the comments section!
For existing customers, the new version is available for download from the License and Downloads page. If you are not yet a Syncfusion customer, you can try our 30-day free trial to check out our available features.
You can also contact us through our support forum, Direct-Trac, or feedback portal. We are always happy to assist you!
If you like this blog post, we think you’ll also like the following useful articles:
39