Microsoft SQL Server 2012 Bible

Backing Up the Database

The actual process of performing a backup presents as many options as the underlying concepts present.

Backup Destination

A backup may copy the data to one of two possible destinations:

• Disk subsystem: You can perform a backup either to a local disk (preferably not the same disk subsystem as the database files) or to another server's disk drive by using the Universal Naming Convention (UNC). The SQL Server service account must have write privileges to the remote drive/share to save the backup file.

Best Practice

You should back up the databases to a local disk (not the same disk where databases are stored) and then copy the backup files to tape or DVD (for small databases) using the organization's preferred IT backup method. This method is the fastest for SQL Server, and it enables the IT shop to continue using a familiar single-tape backup-software technique.

• Tape: SQL Server can back up directly to most tape-backup devices.

Note

Several companies offer a third-party backup for SQL Server. Although you may find third-party backup useful, it is a good idea to become familiar with SQL Server's built-in recovery methods before making the decision to use it.

A disk- or tape-backup file is not limited to a single backup event. The file may contain multiple backups and multiple types of backups.

Backup Rotation

If the backup file is copied to tape, then media retention or rotation, and the off-site media-storage location, become important.