Backup

"Save often" is one of the top rules in dealing with computers and data. The act of saving (aka backing up) and restoring is related to the acts of copying or moving. In databases, saving and restoring is done on different scales. A smaller scale may be as simple as copying a table before working on it. A larger scale may involve scheduling backups of a database and restoring it when needed.

While a lot of save & restore work can be done simply by using the Import or Export wizards in SQL Server, complications can arise quickly especially if there are identity columns or skipped ID numbers.

In larger scale back ups, there are two general recovery models:

• Simple recovery model. The data is restored to the last back up. Any recent changes since the last backup are lost.
• Full recovery model. The data is restored to the last back up, plus any transaction logs since the last backup.

In larger scale back ups, there are four kinds of back ups:

• Data backups.
  • Database backup. Aka full database backup. The entire database plus some of the transaction logs. Pseudo code:
backup database myDatabase to buDevice.
  • Partial backup. Like a database backup except that in only does some of the files. Pseudo code:
backup database myDatabase read_write_filegroups to buDevice.
  • File backup. Backs up specific files. Pseudo code:
backup database myDatabase fileA, filegroupB to buDevice.
• Differential backups. Backups in relation to a differential base, i.e. a differential backup are the parts that have changed since the applicable data backup was done.
  • Differential database backup. Pseudo code:
backup database myDatabase to buDevice with differential.
  • Differential partial backup. Pseudo code:
backup database myDatabase read_write_filegroups to buDevice with differential.
  • Differential file backup. Pseudo code:
backup database myDatabase fileA, filegroupB to buDevice with differential.
• Copy-only backups. New in SQL Server 2005, these are like data backups but cannot function as a differential base. Pseudo code:
backup database myDatabase with copy_only.
• Transaction log backups. These are only done for the full recovery model.

Page Modified: (Hand noted: 2007-10-13 15:42:38Z) (Auto noted: 2007-11-17 06:43:42Z)