Using Recovery Models with SQL Server 2005 Database Engine

In this fifth part to a six-part series on administering SQL Server 2005 database engine, you will learn how to select and use an appropriate recovery model, and more. This article is excerpted from chapter one of the book SQL Server 2005 Management and Administration, written by Chris Amaris, Alec Minty and Ross Mistry (Sams Publishing, 2008; ISBN: 0672329565).

Understanding and Effectively Using Recovery Models

Each recovery model handles recovery differently. Specifically, each model differs in how it manages logging, which results in whether an organization’s database can be recovered to the point of failure. The three recovery models associated with a database in the Database Engine are

  • Full—This recovery model captures and logs all transactions, making it possible to restore a database to a determined point-in-time or up-to-the-minute. Based on this model, you must conduct maintenance on the transaction log to prevent logs from growing too large and disks becoming full. When you perform backups, space is made available once again and can be used until the next planned backup. Organizations may notice that maintaining a transaction log slightly degrades SQL Server performance because all transactions to the database are logged. Organizations that insist on preserving critical data often overlook this issue because they realize that this model offers them the highest level of recovery capabilities.
  • Simple—This model provides organizations with the least number of options for recovering data. The Simple recovery model truncates the transaction log after each backup. This means a database can be recovered only up until the last successful full or differential database backup. This recovery model also provides the least amount of administration because transaction log backups are not permitted. In addition, data entered into the database after a successful full or differential database backup is unrecoverable. Organizations that store data they do not deem as mission critical may choose to use this model.
  • Bulk-Logged—This recovery model maintains a transaction log and is similar to the Full recovery model. The main difference is that transaction logging is minimal during bulk operations to maximize database performance and reduce the log size when large amounts of data are inserted into the database. Bulk import operations such as BCP, BULK INSERT, SELECT INTO, CREATE INDEX, ALTER INDEX REBUILD, and DROP INDEX are minimally logged.

    Since the Bulk-Logged recovery model provides only minimal logging of bulk operations, you cannot restore the database to the point of failure if a disaster occurs during a bulk-logged operation. In most situations, an organization will have to restore the database, including the latest transaction log, and rerun the Bulk-Logged operation.

    This model is typically used if organizations need to run large bulk operations that degrade system performance and do not require point-in-time recovery.


When a new database is created, it inherits the recovery settings based on the Model database. The default recovery model is set to Full.

Next, you need to determine which model best suits your organization’s needs. The following section is designed to help you choose the appropriate model.

{mospagebreak title=Selecting the Appropriate Recovery Model}

It is important to select the appropriate recovery model because doing so affects an organization’s ability to recover, manage, and maintain data.

For enterprise production systems, the Full recovery model is the best model for preventing critical data loss and restoring data to a specific point in time. As long as the transaction log is available, it is possible to even get up-to-the-minute recovery and point-in-time restore if the end of the transaction log is backed up and restored. The trade-off for the Full recovery model is its impact on other operations.

Organizations leverage the Simple recovery model if the data backed up is not critical, data is static or does not change often, or if loss is not a concern for the organization. In this situation, the organization loses all transactions since the last full or last differential backup. This model is typical for test environments or production databases that are not mission critical.

Finally, organizations that typically select the Bulk-Logged recovery model have critical data, but logging large amounts of data degrades system performance, or these bulk operations are conducted after hours and do not interfere with normal transaction processing. In addition, there isn’t a need for point-in-time or up-to-the-minute restores.


It is possible to switch the recovery model of a production database and switch it back. This would not break the continuity of the log; however, there could be negative ramifications to the restore process. For example, a production database can use the Full recovery model and, immediately before a large data load, the recovery model can be changed to Bulk-Logged to minimize logging and increase performance. The only caveat is that the organization must understand it lost the potential for point-in-time and up-to-the-minute restores during the switch.

Switching the Database Recovery Model with SQL Server Management Studio

To set the recovery model on a SQL Server 2005 database using SSMS, perform the following steps:

  1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.
  2. In Object Explorer, first connect to the Database Engine, expand the desired server, and then expand the database folder. 
  3. Select the desired SQL Server database, right-click on the database, and select Properties. 
  4. In the Database Properties dialog box, select the Options page. 
  5. In Recovery Model, select either Full, Bulk-Logged, or Simple from the drop-down list and click OK.

Switching the Database Recovery Model with Transact-SQL

It is possible not only to change the recovery model of a database with SQL Server Management Studio, but also to make changes to the database recov ery model using Transact-SQL commands such as
ALTER DATABASE. You can use the following TSQL syntax to change the recovery model for the AdventureWorks Database from Simple to Full:

–Switching the Database Recovery model
Use Master

Compatibility Level

The Compatibility Level setting located on the Database Properties Options page is meant for interoperability and backward compatibility of previous versions of SQL Server. The options available are SQL Server 2005 (90), SQL Server 2000 (80), and SQL Server 7.0 (70).

Other Options (Automatic)

Also available on the Database Properties Options page are these options:

  1. Auto Close—When the last user exits the database, the database is shut down cleanly and resources are freed. The values to be entered are either True or False.
  2. Auto Create Statistics—This setting specifies whether the database will automatically update statistics to optimize a database. The default setting is True, and this value is recommended. 
  3. Auto Shrink—Similar to the shrink task, if this setting is set to True, SQL Server removes unused space from the database on a periodic basis. For production databases, this setting is not recommended. 
  4. Auto Update Statistics—Similar to the Auto Create Statistics settings, this setting automatically updates any out-of-date statistics for the database. The default setting is True, and this value is recommended. 
  5. Auto Update Statistics Asynchronously—If the statistics are out of date, this setting dictates whether a query should be updated first before being fired.

Other Options (Cursor)

The following options are also available on the Database Properties Options page:

  1. Close Cursor on Commit Enabled—This setting dictates whether cursors should be closed after a transaction is committed. If the value is True, cursors are closed when the transaction is committed, and if the value is False, cursors remain open. The default value is False.
  2. Default Cursor—The values available include Global and Local. The Global setting indicates that the cursor name is global to the connection based on the Declare statement. During the Declare Cursor statement, the Local setting specifies that the cursor name is Local to the stored procedure, trigger, or batch.

Other Options (Miscellaneous)

The following options are also available on the Database Properties Options page:

  1. ANSI Null Default—The value to be entered is either True or False. When set to False, the setting controls the behavior to supersede the default nullability of new columns.
  2. ANSI Null Enabled—This setting controls the behavior of the comparison operators when used with null values. The comparison operators consist of Equals (=) and Not Equal To (<>). 
  3. ANSI Padding Enabled—This setting controls whether padding should be enabled or disabled. Padding dictates how the column stores values shorter than the defined size of the column. 
  4. ANSI Warnings Enabled—If this option is set to True, a warning message is displayed if null values appear in aggregate functions. 
  5. Arithmetic Abort Enabled—If this option is set to True, an error is returned, and the transaction is rolled back if an overflow or divide-by-zero error occurs. If the value False is used, an error is displayed; however, the transaction is not rolled back. 
  6. Concatenate Null Yields Null—This setting specifies how null values are concatenated. True indicates that string + NULL returns NULL. When False, the result is string. 
  7. Cross-Database Ownership Chaining—Settings include either True or False. True represents that the database allows cross-database ownership chaining, whereas False indicates that this option is disabled.
  8. Date Correlation Optimization Enabled—If this option is set to True, SQL Server maintains correlation optimization statistics on the date columns of tables that are joined by a foreign key. 
    • Numeric Round-Abort—This setting indicates how the database will handle rounding errors. 
    • Parameterization—This setting controls whether queries are parameterized. The two options available are Simple and Forced. When you use Simple, queries are parameterized based on the default behavior of the database, whereas when you use Forced, all queries are parameterized. 
  9. Quoted Identifiers Enabled—This setting determines whether SQL Server keywords can be used as identifiers when enclosed in quotation marks. 
  10. Recursive Triggers Enabled—When this setting is enabled by setting the value to True, SQL Server allows recursive triggers to be fired.
  11. Trustworthy—This setting allows SQL Server to grant access to the database by the impersonation context. A value of True enables this setting. 
  12. VarDecimal Storage Format Enabled—When this option is set to True, the database is enabled for the VarDecimal storage format, which is a feature available only with Service Pack 2.

Other Options (Recovery)

Also available on the Database Properties Options page is

  • Page Verify—This option controls how SQL Server will handle incomplete transactions based on disk I/O errors. The available options include Checksum, Torn Page Detection, and None.

Other Options (State)

The following options are available on the Database Properties Options page:

  • Read Only—Setting the database value to True makes the database read-only.

    The default syntax for managing the read-only state of a database is

    ALTER DATABASE database_name <db_update_option> ::=
  • State—This field cannot be edited; it informs you of the state of the database. Possible states include Online, Offline, Restoring, Recovering, Recovery Pending, Suspect, and Emergency.

    To change the state of a database with TSQL, use the default syntax:

    ALTER DATABASE database_name <db_state_option> ::=
  • Restrict Access—This setting manages which users can connect to the database. Possible values include Multiple, Single, and Restricted. The Multiple setting is the default state, which allows all users and applications to connect to the database. Single user mode is meant for only one user to access the database. This is typically used for emergency administration. The final setting, Restricted, allows only members of the db_owner, dbcreator, or sysadmin accounts to access the database.

    The TSQL code for setting the Restrict Access value is as follows:

    ALTER DATABASE database_name <db_user_access_option> ::= 

{mospagebreak title=Administering the Database Properties Mirroring Page}

Most database administrators believe database mirroring is the paramount new feature included with the release of SQL Server 2005. Database mirroring is also a SQL Server high-availability alternative for increasing availability of a desired database. Database mirroring transmits transaction log records directly from one SQL Server instance to another SQL Server instance. In addition, if the primary SQL Server instance becomes unavailable, the services and clients automatically fail over to the mirrored server. Automatic failover is contingent on the settings and versions used.

The Database Properties Mirroring page is the primary tool for configuring, managing, and monitoring database mirroring for a database. The Mirroring page includes configuration settings for security; mirroring operating mode; and the principal, mirror, and witness server network addresses. For more information on configuring database mirroring, review Chapter 19, “Administering and Managing Database Mirroring” (online).

Administering the Database Properties Permissions Page

The Database Properties Permissions page is used to administer database authorization and role-based access and to control permissions on the database. Chapter 13 covers these topics in their entirety.

Administering the Database Properties Extended Permissions Page

The Database Properties Extended Permissions page is used for managing extended properties on database objects, such as descriptive text, input masks, and formatting rules. The extended properties can be applied to schema, schema view, or column view.

{mospagebreak title=Administering the Database Properties Transaction Log Shipping Page}

The final Database Properties page is Transaction Log Shipping. Transaction log shipping is one of four SQL Server 2005 high-availability alternatives similar to database mirroring. In log shipping, transactions are sent from a primary server to the standby secondary server on an incremental basis. However, unlike with database mirroring, automatic failover is not a supported feature.

The configuration settings located on the Transaction Log Shipping page in the Database Properties dialog box are the primary place for you to configure, manage, and monitor transaction log shipping.

For more information on administering transaction log shipping, including step-by-step installation instructions, review Chapter 20, “Administering and Managing Log Shipping” (online).

SQL Server Database Engine Management Tasks

The following sections cover additional tasks associated with managing the SQL Server Database Engine.

Changing SQL Server Configuration Settings

Presently, most of the configuration settings can be changed from within SQL Server Management Studio. These settings can also be changed using the SP_CONFIGURE TSQL command. The syntax to change configuration settings is

SP_CONFIGURE [‘configuration name’], [configuration setting

The configuration name  represents the name of the setting to be changed, and the
configuration setting value  is the new value to be changed. Before you can change settings, however, you must use the SP_CONFIGURE command. You must enable advanced settings by first executing the following script:

SP_CONFIGURE ‘show advanced options’, 1

For a full list of configuration options, see SQL Server 2005 Books Online.

Managing Database Engine Informational Reports

To succeed in today’s competitive IT industry, you must be armed with information pertaining to SQL Server 2005. SQL Server 2005 introduces a tremendous number of canned reports that can be opened directly from within SQL Server Management Studio. These reports provide information that allows you to maximize efficiency when conducting administration and management duties.

You can open these canned reports by right-clicking a SQL Server instance in Management Studio and selecting Reports and then Standard Reports. The standard reports include

  • Server Dashboard
  • Configuration Changes History 
  • Schema Changes History 
  • Scheduler Health  
  1. Memory Consumption 
  2. Activity – All Blocking Transactions
  3. Activity – All Cursors 
  4. Activity – Tip Sessions 
  5. Activity – Dormant Sessions 
  6. Activity – Top Connections 
  7. Top Transactions by Age 
  8. Top Transactions by Blocked Transactions Count 
  9. Top Transactions by Locks Count 
  10. Performance – Batch Execution Statistics 
  11. Performance – Object Execution Statistics 
  12. Performance – Top Queries by Average CPU Time
  13. Performance – Top Queries by Average IP 
  14. Performance – Top Queries by Total CPU Time 
  15. Performance – Top Queries by Total IP 
  16. Server Broker Statistics 
  17. Transaction Log Shipping Status

The standard report titled Server Dashboard, displayed in Figure 1.14, is a great overall report that provides an overview of a SQL Server instance, including activity and configuration settings.

{mospagebreak title=Detaching and Attaching Databases}

Another common task you must conduct is attaching and detaching databases.

Detaching a Database

When a database is detached, it is completely removed from a SQL Server instance; however, the files are still left intact and reside on the file system for later use. Before a database can be detached, all user connections must be terminated; otherwise, this process fails. The detach tool includes the options to automatically drop connections, update statistics, and keep full text catalogs.

Figure 1.14.   Viewing the standard Server Dashboard SQL Server canned report.

To drop the sample AdventureWorks database, follow these steps:

  1. In Object Explorer, first connect to the Database Engine, expand the desired server, and then expand the Database folder.
  2. Select the AdventureWorks database, right-click on the database, select Tasks, and then select Detach. 
  3. In the Detach Database dialog box, enable the following options, as displayed in Figure 1.15: Drop Connections, Update Statistics, and Keep Full Text Catalogs. Click OK.

Attaching a Database

Here’s a common usage scenario for attaching databases: Say you need to move the database from a source to a target SQL Server. When a database is attached, the state of the database is exactly the same as when it was detached.

Figure 1.15.   Specifying detach settings on the Detach Database dialog box.

The following steps illustrate how to attach a database with SQL Server Management Studio:

  1. In Object Explorer, first connect to the Database Engine, expand the desired server, and then select the Database folder. 
  2. Right-click the Database folder and select Attach. 
  3. In the Attach Databases dialog box, click the Add button to add the database to be attached. 
  4. In the Locate the Database Files dialog box, specify the path to the *.mdf file and click OK.
  5. Optionally, change the name or owner of the database. 
  6. Click OK to attach the database.

Alternatively, you can use the following TSQL syntax to attach the AdventureWorks database:

USE [master]

( FILENAME = N’D:AdventureWorks_Data.mdf’ ),
( FILENAME = N’D:AdventureWorks_Log.ldf’ )
if exists (select name from master.sys.databases

  1. sd where name = N’AdventureWorks’ and
  2. SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )  
  3. EXEC [AdventureWorks].dbo.sp_changedbowner @loginame= 
  4. N’COMPANYABCSQL.Service’, @map=false


Please check back tomorrow for the conclusion to this article.

[gp-comments width="770" linklove="off" ]