Backup and Restore plans with MSSQL

  • If you are a dba [database administrator] and you have a significant loss of data, how safe is your job?
  • As a dba, you need to have a backup and restore plan.
  • The backup plan must match your circumstances.

Data loss

  • How much data loss is acceptable?

One day of data, then nightly backups of the data with a simple recovery, and don't worry about the logs. The backups and restores will be short and sweet.

  1. ALTER DATABASE {databasename}
  2. SET recovery simple
  3. go
  4. BACKUP DATABASE {databasename} TO {device}

None, then you will have to backup both the database and the logs. The backups and restores will take more size and take much more time.

  1. ALTER DATABASE {databasename}
  2. SET recovery FULL
  3. go
  4. BACKUP DATABASE {databasename} TO {device}
  5. go
  6. BACKUP LOG {logname} TO {device}

Nature and frequency of the database data

  • Static data
  • Data warehouse
  • High volume data transactions
  • Low volume data transactions
  • Type of data such as manually entered, batches, graphics, videos...

This will dictate the frequency of the backups, when the data change, or when the data changes significantly.

Maintenance window

What's the maintenance window?

ClassAnnual downtime
99%3.7 days/year
99.9%8.8 hrs/year
99.99%53 mn/year
99.999%5.3 mn/year
  • Backups are done live/online, meaning backups are done while people are still using the system.
  • Restores are done as a single user mode, so people will be locked out:
  1. ALTER DATABASE {databasename}
  2. SET single_user
  3. go
  4. RESTORE DATABASE {databasename} FROM {device}
  5. go
  6. ALTER DATABASE {databasename}
  7. SET multi_user
  8. go
  • Tape is much slower than hard-drives.

Database size

  • The database size will dictate the media for backups.
  • Tape maximum database size is around a few terabytes.
  • SANs can deal with hundred of terabytes but can you take the SANs home?

Anybody doing:

BACKUP DATABASE {databasename} TO DISK = 'd:\backups\XYZ.bak'

with the {databasename} being to drive D is asking for a crash. If there is a crash, it's very likely that it will also affect the backup at the same time!