Answers to Practice Questions--Restoring Databases

1. You have the truncate log on checkpoint option set for a small database, which you have to restore after a failure of the hard disk on which the data resides. Which of the following statements are true?

1. You can restore a full backup to this database.
2. You can restore a differential backup to this database.
3. You can restore the current transaction log (assuming that it was on a different disk) if you back up the log before beginning the restore.
4. You can restore to a point in time before the system crashed.

A and B are true. C and D are false. You can’t back up the log if you've set the truncate log on checkpoint option. And if you can’t back up the log, you can’t restore the database to a point in time before the failure.

2. You don't have the truncate log on checkpoint option set for a small database, which you have to restore after a failure of the hard disk on which the data resides. Which of the following statements are true?

1. You can restore a full backup to this database.
2. You can restore a differential backup to this database.
3. You can restore the current transaction log (assuming that it was on a different disk) if you back up the log before beginning the restore.
4. You can restore to a point in time before the system crashed.

All of these statements are true—assuming that you can back up the log for answer C and that you can ignore the bug in SQL Server 7.0 that prevents you from easily backing up the log.

You restore a database from a full backup by using the T-SQL restore command:

RESTORE DATABASE northwind FROM nwindback

You then run a restore operation to apply the transaction log. What happens after you apply the log, and what's the next step you would take?

1. The log restore runs correctly, then you run the recovery process.
2. The log restore fails because the full database restore ran recovery, which is the default. Because you messed up the restore sequence, the next step is to find someone else to blame.
3. The log restore fails because the full database restore ran recovery, which is the default. You have to start the restore sequence over with no recovery on the full database restore.
4. The log restore runs correctly, with recovery as the default, so you can go home.

C is the correct answer. You have to start over if you didn't specify no recovery on all the restores except the last one.