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

.

1. Perform a full backup on the weekend and differential backups during the week.
2. Perform a full backup on the weekend, and during the week, back up only the tables with many changes.
3. Perform a full backup on the weekend and filegroup backups during the week.
4. Back up to disk files instead of to tape, then use the Windows NT or other backup software to move the backup files to tape.

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.

1. Back up to multiple devices by using SQL Server's striping capability.
2. Back up to an NT striped disk set, built by using RAID 5 in your software.
3. Put the database data files on a RAID array.
4. Spread the transaction log across multiple files on separate disks.

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.

1. Local hard disk
2. Remote hard disk
3. Local tape drive
4. Remote tape drive
5. DVD-RAM drive
6. CD-RW (read-write erasable) drive
7. ZIP drive
8. JAZ drive

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.

1. The transaction log has no entries because no one is working on the database.
2. You haven't run a full backup yet.
3. The database has the truncate log on checkpoint option set.
4. No devices are defined for the log backup.
5. You've recently performed a nonlogged operation.
6. You've performed a differential backup since the last full backup.

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.