02 - Recovery Approaches

Hi,

 

Backup & Recovery is the core of a DBA's job. The top priority of a database administrator is to have a database recovery plan for all of your SQL Server installations; not only should you have a plan, you should

be able to carry it out. This means knowing the recovery process inside and out, and testing the plan.

 

This post will not be covering Backup in detail, only appraches will be discussed.

 

 

Database Recovery Methods

 

You have three recovery methods available with SQL Server 2000.

 

-          Full

-          Bulk-Logged

-          Simple

 

 

Full Recovery

 

-                      This model allows full, differential, and transaction log backups.

-                      All operations are fully logged; this means that your transaction log can quickly grow large during bulk loading, SELECT INTO, and CREATE INDEX operations.

-                      The trade-off is that you can recover from a transaction log to a specific point in time or transaction mark (more on this later).

 

 

 

Bulk-Logged Recovery

 

-                      This model allows full, differential, and transaction log backups.

-                      The transaction log backups, however, will include minimal logging for:

 

o         Bulk processes

o         SELECT INTO

o         CREATE INDEX

o         Text/image operations

 

-                      This model is similar to SQL Server 7.0's select into/bulkcopy database option. This minimal logging will help keep your transaction log at a reasonable size, but the trade-off is inability to restore a transaction log using point-in-time recovery. Also, if the data file is damaged, minimally logged operations will need to be redone; this is because in bulk-logged mode, a transaction log backup requires access to the database data files. If SQL Server cannot reach the corrupted files, the database cannot be backed up.

 

 

Simple Recovery

 

-                      This recovery model allows for full and differential backups.

-                      It does not allow transaction log backups, unless you are issuing a TRUNCATE_ONLY backup. The SIMPLE model is equivalent to the trunc log on checkpoint database option in SQL Server 7.0 and prior versions. This model does not log:

 

o         Bulk load operations

o         SELECT INTO operation

o         CREATE INDEX

o         Text/image operations

 

-                      The advantage is that log space used is kept to a minimum. The disadvantage is you can only recover from a full or differential backup.

 

 

Understanding the Transaction Log – Recovery Process

 

-                      Before reviewing how to back up databases, let's take a quick detour and review the architecture of the transaction log.

-                      A SQL Server database requires at least one data file and one transaction log file. Even if you are using the SIMPLE recovery model, you need a transaction log, which is used to recover the database. SQL Server writes the following activity to the database transaction log:

-                       

o         The start and end of each transaction in the database. These are written sequentially.

o         Images of data before and after a modification.

o         Every extent (eight 8KB pages) allocation or de-allocation.

o         CREATE TABLE, CREATE INDEX (if full or bulk-logged recovery model), DROP TABLE, and DROP INDEX.

o         If full recovery model, SELECT INTO, bcp operations, text or image modifications.

o         If bulk-logged recovery model, limited logging of SELECT INTO, bcp operations, text or image modifications.

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead