In MS SQL Server, the Recovery Model decides how the transactions are maintained and backed up so that data can be recovered after disasters. Three recovery models are available from SQL Server 2000 onwards– simple, full, and bulk-logged. Each of these recovery models function differently from each other. And for choosing a model for your SQL Server, you should consider the criticality of the data, and your processing and backup needs.
Let us understand the usage and functionality of each model in detail:
Simple Recovery Model
As the name suggests, it is a basic recovery model which helps the user to restore full and differential backups. Though this model is easy to manage, it is reliable only when data is not critical. This model may not allow to restore database to a given point in time. So, the user may only restore it to the actual time when full or differential backup has occurred. You may lose any modification done in that duration.
So, this Simple Recovery Model is recommended in the following conditions.
- If the database is for testing or development need only.
- If DBA can afford the chance of losing modifications in transactions after the backup.
- If data is not crucial and can be recreated when needed
Set Simple Recovery Model for your SQL Server in an easy way using SQL Server Management Studio with these steps:
- Launch SQL Server Management Studio.
- Go the user database name and right-click on it. Then select the Properties option.
- In the Database Properties window, click Options on the left panel and then under Recovery model section, select Simple from the drop-down menu.
- Click OK to save the settings.
Full Recovery Model
Note: Similarly, DBA can switch to other Recovery Models by selecting the respective options from the drop-down menu under the Recovery model section mentioned above.
Full Recovery Model
It is a reliable model as compared to Simple Recovery Model as it helps to restore the lost data completely. This model allows point-in-time recovery of the database provided the user has all the valid database backups along with transactional log tail backup.
In full recovery model, you also need to back up transaction log files to avoid its growth.
Choose the Full Recovery Model in the following cases.
- If data is crucial and point-in-recovery is required.
- You are using Database Mirroring, Always On Availability Groups, etc
- If bearing data loss is not at all an option.
You can set the full recovery model as you have set the simple recovery model.
Bulk-logged Recovery Model
This model is almost similar to full recovery model except that it uses minimal logging (only information required to recover the transaction is logged). It supports point-in-time recovery but helps in reducing the processing time. However, this model is a little risky as the data may get lost if the logs since the most recent backup are damaged. In that case, changes since the last backup must be redone.
Select Bulk-logged Recovery Model for the following conditions.
- If you don’t want to perform bulk operations while using critical data that cannot be lost
- If minimal logging is required to reduce more log file growth.
How to Recover SQL Data Without Backups?
SQL database recovery tools are of great help when no backups are available to restore the lost SQL data. Tools like SQL Recovery helps you recover data from corrupt MDF and NDF files. Also, the KDR tool supports all MS SQL versions.
Conclusion
SQL Server supports different Recovery models to be secure data against disaster situations. Each model comes with different features for use in different situations, based on the criticality of data. In case, no valid backup is available for SQL database; a well-designed and efficient recovery tool should be considered for SQL data recovery without backup.