How the Recovery Models in Microsoft SQL apply to Backup Exec

Article:TECH11060  |  Created: 2001-01-13  |  Updated: 2011-07-28  |  Article URL http://www.symantec.com/docs/TECH11060
Article Type
Technical Solution

Product(s)

Environment

Issue



How the Recovery Models in Microsoft SQL apply to Backup Exec


Solution



Using Symantec   Backup Exec (tm), there are several backup methods available for SQL backups (Figure 1).

Figure 1

 


1. DATABASE - Back Up Entire database

Allows the backup of the entire database including all system tables, user-defined tables (SQL 6.5 only), and user-defined filegroups (SQL 7.0 or later).

If only Database backup is run, the option Truncate Log on Checkpoint (SQL 7.0) or the Simple recovery mode (SQL 2000 or later) should be selected, therefore preventing transaction logs from becoming full since the logs are not cleared after a database backup.

This kind of backup can be performed with the following (SQL 2000 or later) recovery options: Simple, Full, and Bulk-Logged


2. LOG - Back Up Transaction Log

Allows the backup of only the data contained in the transaction logs. After the transaction log is backed up, committed transactions are removed (truncated).

A Database backup must be run before a Log backup, which alone is not sufficient to restore a complete database.

Do not run a Log backup if:

 
a. Truncate log on check point (SQL 7.0) or Simple recovery mode (SQL 2000 or later) is enabled
 

 
b. Select into/bulkcopy (SQL 7.0) or Bulk-Logged mode (SQL 2000 or later) is enabled
 

 
c. Files are added or deleted. In this case, a Database (full) backup should be run
 

 
This type of backup is allowed with the following SQL recovery options: Full and Bulk-Logged


3. LOG NO TRUNCATE - Back Up Transaction Log - No Truncate

This method should be used when a database is corrupt or missing. The Log No Truncate backup does not remove committed transactions after the log is backed up.

A Database backup is needed to completely restore a database.

This kind of backup can be performed in association with the following SQL recovery options: Full and Bulk-Logged


4. DATABASE - Only Back Up Database Changes (Differential, v 7.0 or later)

It allows backup of only the changes made to the database since the last Database backup. Therefore, Log backups must also be performed to have a point-in-time recovery of the database.

This kind of backup allows the following SQL recovery options: Full, Simple, and Bulk-Logged


Backup Methods performed with Backup Exec that can be performed in association with the SQL 2000 or later recovery model

Table 1 lists backup methods that may be performed with the associated recovery options.

Table 1
 

The Simple, Bulk-Logged, and Full recovery models can be set from the SQL Server Enterprise Manager by right-clicking on the database to be backed up, selecting the Options tab, and then selecting the appropriate option from the Model drop-down menu under Recovery (Figure 2).

Figure 2
 


For a better understanding of how transaction logs work in association with the SQL recovery option available, please read the information given below.

A major architectural change in SQL Server 2000 is the feature of database recovery models. There are three main database recovery models supported in SQL Server 2000. Table 2 lists the various recovery models that are available and describes their effects on recovery operations.

Table 2
 

These three values replace the database options select into/bulkcopy and trunc. log on chkpt.

1. Full recovery model

The full recovery model guarantees the least risk of losing work if a data file is damaged. For a database with this model, SQL Server fully logs all operations, so every row inserted through a bulk copy program (bcp) or BULK INSERT operation is written in its entirety to the transaction log.

It uses database backups and transaction log backups to provide complete protection against media failure. If one or more data files are damaged, media recovery can restore all committed transactions. In-process transactions are rolled back.

Full recovery provides the ability to recover the database to the point of failure or to a specific point in time. To guarantee this degree of recoverability, all operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.


2. Bulk-Logged recovery model

The Bulk-Logged recovery model allows recovery in case of media failure and gives the best performance using the least log space for certain bulk operations, including BULK INSERT, bcp, CREATE INDEX, WRITETEXT, and UPDATETEXT. In the Bulk-Logged recovery model, SQL Server minimally logs these operations. When a bulk operation is executed, SQL Server logs only the fact that the operation occurred. However, the operation is fully recoverable because SQL Server keeps track of which extents the bulk operation modified.

In a Bulk-Logged recovery model, the data loss exposure for these bulk copy operations is greater than in the Full recovery model. While the bulk copy operations are fully logged under the Full recovery model, they are minimally logged and cannot be controlled on an operation-by-operation basis under the Bulk-Logged recovery model. Under the Bulk-Logged recovery model, a damaged data file can result in having to redo work manually.

In addition, the Bulk-Logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes. Point-in-time recovery is not supported. In SQL Server 2000, it is not necessary to perform a full database backup after bulk copy operations complete under the Bulk-Logged recovery model. Transaction log backups under this model capture both the log and the results of any bulk operations performed since the last backup.


3. Simple recovery model

The Simple recovery model brings forth the fastest bulk operations and the simplest backup-and-restore strategy. Under this model, SQL Server truncates the transaction log at regular intervals, removing committed transactions. Thus, only full database backups and differential backups are allowed; an error will be returned if a backup of the log is attempted while using the Simple recovery model. Because SQL Server doesn't need the log for backup, it reuses sections as soon as all the transactions in them are committed or rolled back and no longer needed for recovery from server or transaction failure.


How Automatic Recovery feature of SQL 2000 Server works

The Automatic Recovery feature of SQL 2000 server rolls forward (therefore writes to the database) all the committed (complete) transactions that have been written to the log file before the checkpoint, while it rolls back (deletes) all the uncommitted (therefore incomplete) transactions that were still occurring at the time of the failure.  

Below is a graphic explanation as to why the Simple and Full recovery mode should/should not be used to backup SQL 2000 with Backup Exec.

Figure 3 shows that if the recovery mode in SQL 2000 is set to Full, transaction 1 is written to the database (DB) since it was already committed at the time of the first checkpoint. Transaction 2 and 3 will be rolled forward and therefore written to the DB whenever the second check point will occur. Transaction 4 and 5 will be rolled back since they are not committed and are deleted.

Note: In the event of a database failure, a Log - No Truncate backup should be the first thing to do to back up the logs written between the last checkpoint and the time of the failure.

Figure 3
 
Legend:

B/T = Beginning transaction

C/T = Committed transaction. This is when the transaction has terminated but it has not yet been written to the DB

C/P = Checkpoint. This is when SQL 2000 transfers (writes) all the transactions stored on the log file to the database

Figure 4 shows that if the recovery mode is set to Simple, one more transaction will be lost since the log file will be truncated on the checkpoint and, therefore, more data loss will occur.

Figure 4
 
Legend:

B/T = Beginning transaction

C/T = Committed transaction. This is when the transaction has terminated but it has not yet been written to the DB

C/P = Checkpoint. This is when SQL 2000 transfers (writes) all the transactions stored on the log file to the database

 



Legacy ID



236600


Article URL http://www.symantec.com/docs/TECH11060


Terms of use for this information are found in Legal Notices