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:
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:
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:
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:
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:
Note: Most of the times, databases adopt the respective recovery model from a default Model Database.