How to select the Recovery Model in SQL Server?

Published by Jacob Martin on November 18, 2013

To recover the SQL server recovery software provides a suitable configuration to set up the backup plan.  This property controls the checking of currently available restoration operation, transaction logging and determines the transaction log for backup in the database recovery software are classified into-

1. Simple Recovery Model
2. Bulk-logged Recovery and
3. Full Recovery Model

Simple Recovery Model:

It provides a simple backup of useful database failures that occurs at time of restoring the database. This includes differential/complete/partial/ copy only/ file or file-group backups. This backup consists of data till the time the database is backed up last time. To select the Simple Recovery model is based on couple of condition which are-

• When the data does not have to be changed.
• Data is at critical stage and can be easily recreated.
• When the database is used for testing and development
• When recent changes has not backed up

Bulk-Logged Model :

This model is used for bulk operation such as BULK INSERT, CREATE INDEX etc. The advantage of these operations is to log fully in the transaction log and occupies a huge space.  It even allows you to perform on time recovery until the last bulk operation recovery.  The recovery model needs to issue transaction log backups so that it can prevent the transaction log from growing continuously. This model includes differential/partial /complete/copy-only/file or file-group /transaction log backup on the following conditions-

• If you want to recover to a point-in-time.
• If the data is critical and large bulk of operation is not to be logged.

 Full recovery Model:

As the name suggest the idea behind this type of recovery process to provide completed backup of the database. It even helps to recover all the data at any point-in-time till all the backups get used. This model fully logs all the operations although it also requires transaction log backups to prevent growing up of transaction log. Like Bulk_Logged this model of recovery software also includes differential/partial /complete/copy-only/file or file-group /transaction log backups. This includes following conditions on the basis of these you can select this model they are-

• Database mirroring is used in this model.
• It can perform point-in-time recovery whenever you need it.
• Data is critical and is free from any loss.

One of this Recovery Software is SQL Database (MDF) Repair Tool which suports all these types of recovery process.

Steps to recover SQL MDF files using SQL Database (MDF) Repair tool-

Step 1: Stop the running MS SQL server. Perform the repair task on the copy of the corrupt database, click on the ‘OK’ button to continue.

Step 2: Click on the ‘Select database’ button and select the path of corrupt MDF file. You can also search your corrupt database file by using ‘Look in’ and ‘File Type’ button. click on the ‘Scan file‘ button to start the repairing process.

Step 3: The recoverable objects of database are listed in a tree view on the left side of the window. You can see the preview by clicking on the object.

Step 4: You can also search for a particular object by using ‘Find item’ option. Write the object name or a part of the object name in the given text box, check on ‘Match case‘ or ‘Match whole word‘ and then click find next button.

Step 5: Click on the ‘Start Repair’ icon. A dialog box will appear fill the SQL server instance name. To save the repaired file in the desired location click on the brows button and give the path, else the repaired file will be saved in the ‘Default SQL Location’. Click on the ‘OK’ button.

114 Total Views 2 Views Today