Answers to Practice Questions: Backup Strategies

1. Suppose you have a very large database to back up and the overnight full backups to tape are threatening to exceed your backup window and spill over into working hours. Which of the following strategies could you use to speed up the backups? Select all correct answers.

The correct answers are A, C, and D. B is wrong because in SQL Server 7.0, you can't back up individual tables. However, you can achieve the same result by assigning a table or a group of tables to a filegroup and then backing up the filegroup. But you can't select from the GUI or from the command-line interfaces tables to back up.

2. Which of the following strategies can speed up a database backup? Select all correct answers.

The correct answers are A and C. Striping the backup helps significantly. Putting the database files on a RAID array usually means better throughput on reads, and the backup is reading the data from disk, not from memory. B is wrong; in fact the overhead for calculating parity and writing on a RAID 5 array could degrade backup performance. D isn't going to help because when SQL Server backs up the log, SQL Server reads the changes sequentially from start to end, so having the log on multiple files wouldn't improve performance.

3. Which of the following devices can you use for a SQL Server backup? Select all correct answers.

The correct answers are A, B, C, E, F, G, and H-everything except D. Remote tape drives aren't supported by the operating system, so you can't see them from within SQL Server. You can back up to a local file, then use the NT or other backup utility to move that file to a tape drive on another machine, but that's not what the question is asking. Devices E through H might require additional software, such as Adaptec's DirectCD, to let an application (instead of disk-mastering software) write to the drives, but you can back up to a file on either of these devices.

4. You try to back up the transaction log for a database, but the option to back up the log isn't available in Enterprise Manager. What could be the problem? Select all correct answers.

The correct answers are B, C, and E. B or E would prevent SQL Server from backing up the log until you run a full backup. C prevents SQL Server from backing up the log at all. A, D, and F are incorrect.