How to Set SQL Server 2008 R2 Database Recovery Intervals?

For removal of drawbacks within SQL Server 2008 R2 SP1, SQL Server 2008 R2 introduced along with integration of multiple builds. The automatic recovery system of SQL Server 2008 R2 facilitated with an option named as Recovery Interval that reflects maximum time for SQL database repair within minutes.

For instant recovery of SQL databases, SQL Server 2008 R2 is added with this configuration option that further deals with checkpoint frequency. Basically recovery intervals are fascinating to deal with recovery time for online SQL Server transaction procedures.

Setting Recovery Intervals

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. All these are elaborated here:

sql database recovery

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 interval 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

Bulk-logged Recovery Model: As the name says this database repair procedure is applicable for bit to bit recovery within a big database production house.

When Bulk-logged Recovery Applicable?

Above listed all points should be true for applying Bulk-logged recovery:

  • Point-in-time or instant recovery is not a demand
  • Strictly applicable for large set-ups
  • Suggested for database recovery when bunch of database files over there
  • Specially for database log recovery within large set-ups

Note: Most of the times, databases adopt the respective recovery model from a default Model Database.