Data Recovery possibility on SQL databases

There can be a possibility that when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. During such scenarios, you will not be able to connect to the database. In this article we will go through the Tampa Data Recovery possibility on SQL databases.

When an incident occurs, it is important to take all precautions in order to protect the data and to increase chances of successful recovery. It is highly recommended to perform following steps immediately after an incident occurs:

  1. Take database offline – detach it from SQL server
  2. Create copies of the MDF and LDF files
  3. Reattach MDF and LDF files copied to SQL server
  4. Use appropriate recovery options

Recovery interval is the top time period for rolling back a recovery action executed by SQL Server. If the recovery action is not completed on time then, the transaction will roll forward. The status of Recovery Interval gets settled to 0 when it is automatically running. Steps for the recovery interval creation:

  • Within SQL Server, open Object Explorer
  • Make a right click on Server option
  • Make selection for Properties tab
  • Go with Database Setting tab
  • Find Recovery tab
  • Go with Recovery Interval box
  • Settle the value for database recovery within the range 0-32767 minutes
  • Recovery interval 0 shows that SQL Server 2008 R2 settled with automatic SQL recovery

Opt Recovery Model for SQL Database Recovery

After applying recovery interval properly, it is required to opt or use a database recovery model. There are different database recovery models for SQL Server 2008 R2.

Simple Recovery Model: This database recovery model is settled to remove database inaccessibility issues from development databases.

When Simple Database Recovery Applicable?

Above listed all points should be true for applying simple recovery:

 

  • You are not able to get update between the time intervals from last back to database failure.
  • Expecting the data loss risk from log files
  • Backing up database for restoration is not your preference
  • Relying on differential or full backup

Full Recovery Model: Database those are running in production phases should recover under this database recovery model.

When Full Database Recovery Applicable?

Above listed all points should be true for applying full recovery:

  • Ability to repair all data from damaged database
  • Should able to repair database failure point
  • Should able to repair database failure point
  • Possibility to recovery all damaged pages from databases
  • The cost for backups of transaction log is exceeding beyond expectations