Question 3) Installing, Configuring, and Administering
Objective : Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases
SubObjective : Optimize database performance.
(Single Answer Multiple Choice)
You administer your company’s SQL Server 2000 computer named SQL2K. A database named Contacts resides on SQL2K. SQL2K contains one physical disk divided into two partitions. The primary and secondary filegroups reside on the first partition. The transaction log file resides on the second partition.
A full database backup completes at 1:00 p.m. The following sequence of events occurs:
1:12 p.m. – Transaction 145 starts.
1:15 p.m. – Differential backup begins.
1:16 p.m. – Transaction 146 starts.
1:17 p.m. – Transaction 147 starts.
1:18 p.m. – Transaction 145 commits.
1:25 p.m. – Differential backup completes.
1:27 p.m. – Transaction 147 commits.
1:30 p.m. – Transaction log backup starts.
1:35 p.m. – Transaction log backup completes.
1:37 p.m. – Transaction 146 commits.
At 1:40 p.m., a hard disk crash occurs. You replace the failed disk.
To which state can you recover the database?
- to a state in which only transaction 145 is committed
- to a state in which only transaction 147 is committed
- to a state in which only transaction 146 is committed
- to a state in which transactions 145 and 147 are committed
- to a state in which transactions 145 and 146 are committed
- to a state in which transactions 145, 146, and 147 are committed
D. to a state in which transactions 145 and 147 are committed
The database can be restored to a state in which transactions 145 and 147 are committed. To restore this database, you should restore the full database backup, the differential database backup, and all transaction log backups since the last differential database backup. The current transaction log cannot be backed up because the hard disk has failed. For this reason, transaction 146 cannot be committed during the restore process.
Because transaction 146 did not commit until after the transaction log backup, this transaction will not be committed when the database is restored. Transaction 145 will be committed when the differential database backup is restored. Transaction 147 will be committed when the transaction log backup is restored.
When a hard disk fails and all data resides on partitions of that disk, no data can be backed up. For transaction 146 to be committed after the restore, a transaction log backup must be made before starting the restore procedure. Because the transaction log and the data were on the same physical disk, it would not be possible to back up the transaction log once the database disk failed.
1. Administering a Microsoft SQL Server 2000 Database – Backing Up Databases
– Types of Backup Methods
2. SQL Server Books Online – SQL Server Architecture
– Administration Architecture – Backup/Restore Architecture
These questions are derived from the Self-Test Software Practice Test for Exam # 70-228 – Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition .