Database Recovery Models

One of the great features added in SQL Server 2000 is the database recovery model. The database recovery model allows you to control the size and speed of your transaction log backups, and simplifies disaster recovery. Old options like select into/bulkcopy and trunc. log on checkpoint have been replaced with the Simple, Full, and Bulk-Logged recovery models. Each of the models has a varying degree of acceptable recovery time and speed.

Note 

The select into/bulkcopy and trunc. log on checkpoint options have been left in SQL Server 2000 for backward compatibility. They are only accessible through T-SQL commands such as the sp_dboption stored procedure. If you're using these two options in your code, consider removing them in favor of the new recovery models since they may be removed in a later version of SQL Server.

To set the database model in Enterprise Manager, go to the Options tab in the database Properties screen, as shown in Figure 8-4, and select the appropriate recovery model in the Model drop-down box.

click to expand
Figure 8-4: Database options in Enterprise Manager

You can also use T-SQL to specify a database's recovery model by using the ALTER DATABASE statement as shown here:

ALTER DATABASE <database name>       SET RECOVERY [SIMPLE | FULL | BULK_LOGGED]

For example, to set the Northwind database to the Simple recovery model, you can use this syntax:

ALTER DATABASE Northwind       SET RECOVERY SIMPLE

To determine which recovery model your database is in, use the sp_helpdb stored procedure as shown here:

sp_helpdb Northwind

The recovery model is displayed under the status column. The sp_helpdb stored procedure returns loads of results, most of which aren't needed. I prefer to use the databasepropertyex() function to determine which recovery model I'm in. You can do this by specifying the database and option of recovery as shown here:

SELECT databasepropertyex('Northwind', 'recovery')

Simple Recovery Model

The Simple recovery model is the easiest recovery model to implement. This model is essentially the same as leaving the trunc. log on checkpoint option checked in SQL Server 7.0. The Simple recovery model periodically truncates the transaction log, removing any transactions that have been committed. Because the transaction log is constantly being truncated, it cannot be backed up. This leaves you with only full and differential backups available for your backup strategy. If you try to perform a transaction log backup while your database is configured to use the Simple recovery model, you'll receive an error:

Server: Msg 4208, Level 16, State 1, Line 1 The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.

This model is perfect for those databases that are only being backed up on a nightly basis, or possibly those that are being backed up through a differential backup throughout the day. It's usually sufficient for most development databases. However, using this option means you cannot do exact point-in-time recovery, which may be required for your product. Since your transaction log is truncated and reused, you free up the space the transaction log would have used and the maintenance cost for administering those backups. This model is the default recovery model for SQL Server Personal Edition and SQL Server Desktop Engine.

Full Recovery Model

The Full recovery model provides the least chance of losing data, but you add administration and space costs. In this model, SQL Server logs all operations. This includes rows written through bulk operations like bcp or BULK INSERT. With the Full recovery model, you can recover to any point in time as long as you're performing regular transaction log backups. Keep in mind that if you choose this option, your transaction logs and backups of the logs will grow fast in a rapid OLTP environment. The Full recovery model is the default recovery model for SQL Server Standard and Enterprise Editions.

Note 

The Full recovery model also logs any CREATE INDEX commands. SQL Server 7.0 would only log the fact that the index was created, but not the actual index. In SQL Server 2000, however, the actual index is logged, which means you won't have to rebuild the index after restoration of your database from a transaction log backup.

Bulk-Logged Recovery Model

The Bulk-Logged recovery model was designed as a compromise to the Full recovery model. This model provides better performance and utilization of space compared to the Full recovery model. This is because when a bulk operation occurs under a database with this recovery model enabled, SQL Server only logs the fact that the bulk operation occurred and which extent it occurred on. Since the bulk operation is not fully logged, your transaction logs remain much smaller than in the Full recovery model.

Tip 

Microsoft did a scalability study on SQL Server recovery models where they stated that they
received a significant performance enhancement by using Full recovery model. This can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_asphosting.asp.

Since the extents where the bulk operation occurred are logged, you can recover to a given point in time if your transaction log backups are performed regularly. The trade-off is that when you back up the transaction log, the extents where the data changed must also be backed up in addition to the transaction log. This means your transaction log backups can be quite large and will take longer to finish.

Note 

Transaction log restorations in the Bulk-Logged recovery model are comparable to the Full model. The process of scanning the extents for changed data does not have to be redone when you restore the transaction log in this model.

Recovery Options

If you want to recover a database set to the Simple recovery model, you can only recover to the point of the last full backup. If you would like to recover a database set to the Full or Bulk-Logged model, you have to recover to the last full backup, then apply the last differential backup, and finally any transaction logs. With the final transaction log, you can also specify an exact point in time to recover to.

Tip 

I recommend that you use Full or Bulk-Logged recovery models for production databases if you care about getting a database up to a given point in time. In the rare instance where this is not the case, you can use full backups.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net