Skip to content

5. Data Backup and Recovery

  • Backing up files can protect against:
    • accidental loss of user data.
    • database corruption
    • hardware failures
    • natural disasters.
  • It’s the job of the DBA to ensure that backups are performed and that backup tapes are stored in a secure location.

Planning a Backup Strategy

  • Answer the following questions:
    • How important is the data?
    • What type of information does the data contain?
    • How often does the data change?
    • How quickly does the recovery process need to be?
    • What backup equipments are available?
    • Who is responsible for the backup process?
    • What is the best time for scheduled backups?
    • Do you need to store the backup data off-site?

The Basic Types of Backup

  • Full Backup:
    • A full backup is a complete copy of the database. It is the most common type of backup.
  • Copy Backup:
    • A full backup of partial sections of the database.
  • Differential Backup:
    • A backup of the changes that have occurred since the last full backup.
  • Incremental Backup:
    • A backup of the changes that have occurred since the last backup (full or differential).
  • Daily Backup:
    • A backup of all changes that happened today.

Examples of backup strategies

  • Full backup every week, and daily backups every day.
  • Use 10 tapes, divided into 2 sets of 5 tapes each. Use each set for a week (so each set is used bi-weekly); then use a specific tape of the set for each day of the week. AS:
    • First week use set 1: tape 1 for Monday (full backup), tape 2 for Tuesday, tape 3 for Wednesday, tape 4 for Thursday, and tape 5 for Friday.
    • Second-week use set 2: tape 1 for Monday (full backup), tape 2 for Tuesday, tape 3 for Wednesday, tape 4 for Thursday, and tape 5 for Friday.
    • Rotate the sets every week.
    • This way each tape is used once every 2 weeks which makes their life expectancy longer.

Backup Media

  • Selecting a backup media device depends on the following:
    • Capacity. How much data can be stored on the device?
    • Reliability. How often does the device fail?
    • Extensibility (scalability). Can the device be expanded to store more data?
    • Speed. How long does it take to write data to the device?
    • Cost. How much does the device cost?
  • Most common backup media:
    • Tape drives:
      • Most common. Use magnetic cartridges.
      • Cheapest. Unreliable. Slow.
      • Can lose data with time, or may be damaged by heat, humidity, and dust.
      • Capacity: 100MB to 2GB.
    • Digital audio tape (DAT) drives:
      • Faster, more reliable, more expensive, and have more capacity than tape drives.
      • Use 4mm or 8mm tapes.
      • Capacity: 16GB t0 36GB.
      • Speed: 30MB/min (for 4mm tapes) and 10MB/min (for 8mm tapes).
    • Auto-loader tape systems:
      • High cost.
      • Uses multiple DAT drives organized in a magazine.
      • Supports high capacity and high speed.
    • Magnetic optical drives:
      • Capacity: 1GB to 4GB.
      • Combines magnetic tape and optical lasers in 3.5-inch and 5.25-inch disks.
    • Tape jukeboxes:
      • Similar to auto-loader tape systems; but, uses Magnetic optical drives instead of DAT.
      • These systems load and unload disks stored internally for backup and recovery operations.
      • Their key drawback is the high cost.
    • Removable disks:
      • Full external hard drives.
    • Disk drives:
      • The fastest solution to back up and restore data.

SQL Server Backup and Recovery

  • The Tables-only backups are not supported in SQL Server. Every backup must include (to make the recovery possible):
    • Data (tables): files or file groups.
    • Transaction log: the log file.
  • Recovery model:
    • A database property that controls the transaction log maintenance.
    • Three types: Simple, Full, and Bulk-logged.
    • Determines the backup and restore requirements.
  • Restore: A multi-phase process that includes:
    • Copying all the data and log pages from a backup to the database.
    • Applying forward all transactions that are logged in the backup.

Types of Backups

  • Copy-only Backup. Independent of the regular sequence of SQL Server backups.
  • Data backup. A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).
  • Database backup. Full database backups represent the whole database at the time the backup has finished.
  • Differential backup. A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.
  • Full backup. Data backup, and also enough logs to allow for recovering that data.
  • Log backup. A backup of transaction logs that includes all log records that were not backed up in a previous log backup.
  • File backup. A backup of one or more database files or filegroups.
  • Partial backup. Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup; and any optionally-specified read-only files.

References