01 - Backup 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 Backup Methods

 

You have five backup methods available with SQL Server 2000.

 

-          Full Backups

-          Transaction Log Backups

-          Differential Backups

-          Offline Backups

-          Snapshot Backups

 

 

Full Backups

 

-                      A full backup copies your entire database.

-                      Full backup allows you to restore your entire database from the latest full backup.

-                      This is the most time-consuming backup option. Since the database is online when it is backed up, the backup includes changes and log file entries from the point the backup began to the point that it is completed.

 

 

Transaction Log Backups

 

-                      Transaction log backups back up the transaction log from the previous full backup or transaction log backup.

-                      When the backup completes, SQL Server truncates the inactive portion of the log.

-                      Transaction log backups have low resource overhead and can be run frequently.

-                      Transaction logs are cumulative, meaning each backup is part of a sequential line of transaction log backups.

 

-                      To recover the database using transaction logs, you must first restore from the Full Database Backup and all Transaction Log Backups that have occurred from that point.

 

 

Differential Log Backups

 

-                      Differential backups copy all changed pages since the last full backup. They are:

 

o         Smaller than full database backups

o         Faster in backup time then full backups

o         Slower in total restore time (full plus differential restore)

 

-                      Differential backups work side-by-side with transaction log backups.

-                      Since the database is online when it is being backed up, the backup includes changes and log file entries from the point the backup began to its completion.

 

-                      Differential backups, unlike transaction log backups, are self-contained and only require the latest full backup from which to restore.

-                      For example, if you run a full backup at 8am, a differential backup at 10am, and an additional differential backup at 1pm, this 1pm differential backup will include all changes since 8am.

 

 

Offline Backups

 

 

-                      Offline backups take your database offline, either by shutting down the SQL Server service or detaching the database.

-                      Once the database files are no longer in use, you may:

 

o         Copy them to a remote server

o         Use a third-party backup utility

o         Back up using Window 2000's backup tool

 

-                      This should be least considered for most databases, as they do not make the database highly available.

-                      Caching for stored procedures and prepared statements also suffers, as the database has to begin from scratch when it is reattached or SQL Server is restarted.

 

 

Snapshot Backups – Or Split Mirroring

 

-                      Third-party hardware and software must be purchased; but for very large databases, this may be your best backup solution.

-                      At best, backups and restores of a very large database can be performed in seconds; this is achieved by splitting a mirrored set of disks, or producing a copy of a disk block as it is written while keeping the original.

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead