Last week we decided on how often to backup our database, now we must decide where to backup our database. The first option available is to backup directly to tape from SQL Server. On the plus side a backup to tape allows for off-site storage of backups. On the down side tape backups are slow and therefore can impact the server for a longer amount of time than abackup to a file. The other common place to backup to is a file. File backups are much faster than a tape backup, for both backup and restore operations, however backups to files don’t allow for quick off-site storage (unless you happen to have a high speed remote link).
A third option is to use a combination of making backups to a file and then using another backup utility, such as NT Backup, to copy the file backups to tape. By making backups to files on another server nearby and then copying the files to tape, you can minimize the time a backup will impact your SQL Server while still allowing for off-site storage of tapes. Also, if you need to make multiple copies of tape backups, using another computer for copying backups to multiple tapes can help even more.
Another thing to think about when you are choosing where to make a backup is the time it takes to restore a backup. For example, a backup that is stored on another computer could be restored much faster over a high speed network than it could be from a tape drive. To take advantage of this faster recovery you may consider saving file backups for the week on another computer (in addition to your tape backups). In the event your SQL Server crashes you have a current backup on hand and available at a faster speed than tape.
File and tape backups do provide for lots of flexibility in designing your disaster recovery plan, but their are still many options available from third party venders. For example, you can find utilities that make the process of making backups of multiple SQL Servers very simple. You may also consider a Storage Area Network for large mission-critical systems. Although I would love to cover every option available, other backup utilities and hardware options are outside the scope of this series.