MSCE Interview Questions and Answers

QUESTION 51
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com maintains a database that is used by members of the manufacturing
and distribution departments. Certkiller .com has the two departments share three
tables in the database. Certkiller .com has data that pertains to the manufacturing
department maintained separately from the data pertaining to the distribution
department. The information contained in the database is dynamic.
Certkiller .com's current backup strategy consists of performing full database
backup on Sunday and a differential backup on each Monday, Wednesday and
Friday with the transaction log backup twice per day. Recently you discovered that
the database has grown in size making it impossible for you to complete the backups
in a timely manner. You are required to design a new backup strategy to
accommodate the large database which requires the minimum amount of
administrative effort. You r solution should additionally be able to restore the
database to a point in time while data loss is minimal.
What should you do?

A. All the system tables and shared tables should be placed in the Primary filegroup. All the tables pertaining to the manufacturing department should be placed into a new filegroup named CK_FGR02. All the tables that pertain to the distribution department should be placed into a new file group named CK_FGR03. A different filegroup should be back up each night in a round-robin fashion and finally backup the transaction log
several times a day.
B. All the system tables and shared tables should be placed in the Primary filegroup. All the tables pertaining to the manufacturing department should be placed into a new filegroup named CK_FGR02. All the indexes that pertain to those tables should be placed in to a new file group named CK_FGR03. All the tables that pertain to the distribution department should be placed into a new file group named CK_FGR04. All the indexes that pertain to those tables should be placed in to a new file group named CK_FGR05. The primary filegroup should be back up on Monday nights, CK_FGR02 and CK_FGR04 on Wednesday nights and CK_FGR03 and CK_FGR05 file groups should be back up on Friday nights.
C. A differential backup should be performed every Monday and Friday whilst you
perform transaction log back ups twice every day.
D.A full database back up should be performed on Saturday of the tables that pertain to the manufacturing department. A full database backup should be performed on Sunday of the tables that pertain to the distribution department whilst Monday, Wednesday and Friday are used for transaction log back ups.

Answer: A

Explanation: You should remember in the scenario that a filegroup is a collection of
related files which can be administered as a single unit. You would be capable of
organizing data into different filegroups which can be back up at different times.
The data in a filegroup does not require being split into independent subsets.

Incorrect Answers:
B: You should not consider this action in the scenario it could cause data inconsistencies in the future for Certkiller .com.
C: This option should not be considered for use in the scenario because it is only possible to backup filegroups databases and files.
D: In the scenario you should not use this option because full database back ups make a copy of an entire database you can not specify certain tables only.


QUESTION 52
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional Certkiller .com has recently implemented a SQL Server 2000 database that is used to store the business information. The database used by Certkiller .com uses many tables some of which are quite large. Certkiller .com stores data files on separate physical disks for performance reasons. Certkiller .com knows it would take to long for a full database backup so you want to backup only files. The backup schedule you use is shown below:



Certkiller .com has discovered at 12:30 P.M. that the disk hosting the primary data
file and other data files has failed. You are required to restore the database to the
point of failure.
What should you do?


A. The active transaction log should be backed up then you should restore the latest
backup of each damaged file. You should next restore all the transaction log backups
starting from the earliest restored file backups. Finally you should recover the database.
B. The data base can not be restored to the point of failure it is impossible because the primary data file has been lost.
C. The data base can not be restored to the point of failure it is impossible because the primary data file has been lost and no full database backup is available.
D. The active transaction log should be backed up then you should restore the latest
backup of each damaged file. You should next restore all the transaction log backups
starting from the most recent restored file backups. Finally you should recover the
database.

Answer: A

Explanation: In the scenario you should remember to recover a database to the
moment of failure you must have a backup of the active transaction log. For this
reason it is important to provide the utmost fault tolerance for the transaction log.

Incorrect Answers:
B, C: This option should not be considered in the scenario because this is possible and there is a full database backup that exists in the scenario.
D: This option should not be used in the scenario as you would only partly be adhering to the scenario objective at hand.

QUESTION 53
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com has recently implemented a SQL Server 2000 database with full-text
indexing enabled on the five largest tables in the database as well as full-text queries against those tables which function properly. You have performed regular backups of the database. The server hosting the database for Certkiller .com has failed after several months.

Certkiller .com management has decided to restore the database to another server in
the network. The database was successfully restored using the most recent backups.
When you test the database functionality you discover that all the full-text queries
fail. You are required to restore the database functionality.
What should you do?

A. A full population of all full-text catalogs should be performed.
B. The full-text catalog that existed before the disaster should first be rebuild and then restore them from the latest backup.
C. The full-text catalog that existed before the disaster should be rebuilt.
D. The full-text catalogs should be restored from the latest backups.

Answer: D

Explanation: In the scenario you should always remember that the Dull-text indexes
enable database users to perform sophisticated phrase searches using Microsoft
search. The indexes are stored in full-text catalogs that reside independently from
SQL Server databases.

Incorrect Answers:
A: This option should not be used in the scenario as you would be consuming to much
time by doing the task in this manner.
B: There is no need for this to be done in the scenario as a full backup of the existing catalog already exists in the scenario.
C: If you do not have a backup of the full-text catalog that would be the only time
appropriate for performing this action.

QUESTION 54
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You are responsible at Certkiller .com for performing regular backups of the SQL
server 2000 database. The schedule you use for the backup strategy is shown below:



Certkiller .com has discovered that Thursday night the disk on which the data file
resides failed at 3:32 P.M. You are required to restore the database as close to the
moment of failure as possible.
What should you do?

A. The active transaction log should first be backed up then restore the full back up that was made at 1:00 A.M. on Sunday. You should then restore the differential backup made at 6:00 P.M. on Wednesday and restore the transaction log backup that was made on Thursday at 10:00 A.M., 12:00 P.M. and 2:00 P.M. You should then finally restore the active transaction log.
B. You should first backup the active transaction log then restore the full backup made at 1:00 A.M. on Sunday. You should then restore the transaction log backups that were made on Thursday at 10:00 A.M., 12:00 P.M. and 2:00 P.M. You should finally restore the active transaction log.
C. You should first backup the active transaction log then restore the differential backup made at 6:00 P.M. on Wednesday. You should then restore the transaction log backups that were made on Thursday at 10:00 A.M., 12:00 P.M. and 2:00 P.M. You should finally restore the active transaction log.
D. You should first backup the active transaction log then restore the full backup made at 1:00 A.M. on Sunday. You should then restore the differential backup made at 6:00 P.M. on Wednesday. You should then restore the transaction log backups that were made on Thursday at 10:00 A.M., 12:00 P.M. and 2:00 P.M.

Answer: A

Explanation: In the scenario you are required to restore the database to the moment
of failure. Since the active part of the transaction log records all the transactions
you should immediately backup the active log to comply with this requirement.

Incorrect Answers:
B, C, D: In the scenario the other backup strategies that are shown should no be used in the scenario because this would not achieve the scenario objective of restoring the database to the moment of failure.


QUESTION 55
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server and all database servers run SQL
Server 2000. The Certkiller .com network contains a database server named
Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores
sales and product data.

Several data capturers enter data into the CK_Sales database everyday. The Full
Recovery Model is implemented for the CK_Sales database. A Full backup of the
CK_Sales database is performed every Saturday at 12:00 P.M., a differential
backup is performed every week night at 12:00 P.M., and a backup of the
transaction log is performed every two hours during the working day.
On Thursday morning you discover that Certkiller -DB01 suffered a hard disk
failure sometime before the previous day's differential backup was performed. You
replace the failed hard disk and restore the operating system and application on
Certkiller -DB01. You now need to restore the CK_Sales database as quickly as
possible.
What should you do?

A. Restore the last Full backup.
Restore the last differential backup.
Restore all the transaction logs in order.
B. Restore the last Full backup.
Restore all the transaction logs in order.
C. Restore the last Full backup.
Restore the differential backup since the last Full backup.
Restore all the transaction logs since the last differential backup.
D. Restore the last Full backup.
Restore the last differential backup.
Restore all the transaction logs since the last differential backup.

Answer: D

Explanation: The Full Recovery Model is implemented for the CK_Sales database
and you've performed a Full backup on Saturdays, daily differential backups and
transaction log backups every two hours. In the Full Recovery Model, transactions
in the transaction log are not cleared until they are backed up. Therefore you need
to restore the last Full backup, restore the last differential backup, and restore all the transaction logs since the last differential backup.

Incorrect Answers:
A: You do not need to restore all the transaction log backups, only the transaction log backups since the last differential backup.
B: You could restore all the transaction log backups since the last Full backup but it would be quicker to restore the last differential backup performed since the last Full backup and then only the transaction log backups since the last differential backup.
C: A differential backup will backup all data that has changed since the last Full backup. Therefore you only need to restore the last differential backup.

QUESTION 56
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com recently started using a SQL Server 2000 database to store business
information. The database on the server was created with the default options. You
are responsible for regularly loading large amounts of data to the database
regularly. As a result of these actions you discover that the transaction log file
residing on drive D: is increasing in size.

You have received instruction to start performing regular backups of the
transaction log and occasionally shrink the log over a period of time. You later
discover that the actions taken do not prevent the transaction log file from
increasing in size. You want to ensure that you prevent the transaction log from
consuming the entire disk space. You plan to replace the disk on which the log
resides with a larger one. The solution you are working on should not interfere with
your ability to restore tha database to a point in time.
What should you do?

A. You should switch to the Bulk-Logged Recovery model and perform a full or
differential database backup after each bulk-load operation whilst you continue to
perform frequent transaction log backups.
B. You should switch to the Simple Recovery model and perform a filegroup backup
after each bulk-load operation whilst you continue to perform frequent transaction log backups.
C. The Select into/bulkcopy database option should be disabled and continue to perform frequent log backups.
D. Drive D: should be compressed and continue to perform frequent transaction log
backups.

Answer: A

Explanation: You should remember in the scenario that the databases can be
configured with recovery models that establish the manner in which transactions
are logged and the transaction log is truncated. Employing this recovery model
ensures the database is fully recoverable as all database operations are logged and
the transaction log is never automatically truncated.

Incorrect Answers:
B: You should not consider using this option in the scenario as this would enable the
transaction log to become useless for point-in-time recovery as it would be truncated on each checkpoint.
C: You should not implement this option in the scenario as it basically is equivalent of implementing the Bulk-Logged Recovery model.
D: You should not consider making use of this option in the scenario because this action will not achieve the scenario objective.

QUESTION 57
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com makes use of a SQL Server 2000 database residing on a Windows
2000 Server computer. The server used for the SQL instance hosts the master copy
of the Certkiller .com database which is frequently updated and extensively queried.
Certkiller .com has members of the financial department who register transactions
continuously whilst members of the marketing department update the inventory
products as shipped or received.
Certkiller .com has given the responsibility of executing queries to determine future
sales prospects to the Sales department. Certkiller .com requires the database to be
operational at all times. You have recently discovered that the server is overloaded
and want to fix the situation.
What should you do?

A. The Simple Recovery model should be enabled.
B. The frequency of the Certkiller .com backups should be increased.
C. Log shipping should be disabled.
D. A standby server should be implemented.
Answer: D
Explanation: You should remember in the scenario that we are required to have the
database operational at all times. This objective can only be achieved when you
consider installing the standby server in the network as this enables the database to
be operational at all times.
Incorrect Answers:
A: This option should not be used in the scenario because this recovery model will only restore the database to the point of the last full or differential database backup and will not avoid the downtime.
B: This option should not be used in the scenario even though it is a good practice the downtime involved in restoring the server would not be avoided.
C: This option should not be used in the scenario because the implementation requires to much administrative effort.

QUESTION 58
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
Certkiller .com recently started using a SQL Server 2000 database to store business
information which is used for online transaction processing. You have received
instruction periodically to load vast amounts of data into the database from test
files. The Certkiller .com network management do not want any data to be lost in the
event of a server failure.
You are busy designing the Certkiller .com network disaster recovery plan. You
should be able to restore the database up to the moment of failure and load the new
data as quickly as possible into the new database.
What should you do? (Choose all that apply.)

A. The Full Recovery model should be used.
B. The Bulk-Logged Recovery model should be used.
C. The Simple Recovery model should be used.
D. Full database backups should be performed.
E. Differential database backups should be performed.
F. Transaction log backups should be performed.

Answer: B, D, F

Explanation: In the scenario you should always remember that the Recovery models
provide a geographical method of setting database options which determine the
extent to which data loss would be incurred.

Incorrect Answers:
A: This option should not be considered in the scenario for use because this would
decrease performance of bulk-loads.
C: You should not consider using this option in the scenario as this would enable the
transaction log to become useless for point-in-time recovery as it would be truncated on each checkpoint.
E: This option should not be used in the scenario because you should first perform a Full backup before performing a differential backup.

QUESTION 59
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You are responsible for managing a SQL 2000 database used to store the business
information of Certkiller .com. You have recently received instruction from the CIO
to develop a new backup strategy. The backup strategy you are developing should
keep downtime to a minimum.

In the event of a database failure the database should be restored quickly as
possible. You received additional instruction that the database must be restored as
close as possible to the failure point. Certkiller .com has configured their databases to use the Full Recovery model.
What should you do?

A. A full database backup should be performed every Friday night whilst you perform
differential backups each Monday through Thursday night.
You should finally perform transaction log backups every hour of business hours.
B. A differential backup should be performed each Friday night whilst you perform
transaction log backups each Monday, Wednesday and Saturday night.
C. A full database backup should be performed every other Friday night whilst you
perform differential backups every Monday. You should finally perform a filegroup backup at the end of each business day.
D. A full database backup should be performed monthly whilst you perform transaction
log backup each Friday.

Answer: A

Explanation: In the scenario you should always remember that by implementing
disaster recovery strategies that uses full database backups with transaction log
backups significantly reduces the time involved for restoring the database to the
moment of failure.

Incorrect Answers:
B: In the scenario differential backups should be performed Monday through Thursday
night as differential backups backup the information that has changed since the last
differential backup.
C: You should avoid performing these actions as they will only be responsible forcreating additional administrative overhead.
D: In the scenario you should remember that full database backups should be run
regularly to ensure restoration to the moment of failure.


QUESTION 60
You work as the database administrator at Certkiller .com. The Certkiller .com
network contains a SQL Server 2000 database server named Certkiller -DB01 that
runs on a Windows 2000 Server computer. Certkiller -DB01 hosts a database
named CK_Projects. The Simple Recovery Model is implemented for the
CK_Projects database.
You need to implement a disaster recovery plan for the CK_Projects database. Your
disaster recovery plan must ensure that data can be baked up as quickly as possible
and that data can be recovered as quickly as possible following a disaster. You must
also ensure that no more than three hours data is at risk at any one time. You plan
to perform a full backup every Saturday at 12:00 P.M. You must now plan the
backup schedule that will run during the week.
What should you do?

A. Schedule a transaction log backup every three hours, Monday through Friday.
B. Schedule a differential backup at 12:00 P.M., Monday through Friday and schedule a
transaction log backup every three hours between differential backups.
C. Schedule a differential backup every three hours, Monday through Friday.
D. Schedule a full backup at 12:00 P.M., Monday through Friday and schedule a
differential backup every three hours between differential backups.

Answer: C

Explanation: To ensure backups can be performed quickly, you need to implement
differential backups every three hours. With differential backup ups, only the data
that changed since the last Full backup will be saved. This means that you only need
the last full backup and the last differential backup to restore the data.

Incorrect Answer:
A, B: The Simple Recovery Model does not transaction log the backups as it truncates
the transaction logs before the transaction logs are backed up. You must change the
Recovery Model to Full or Bulk-logged in order to backup the transaction logs.
D: Performing full backups every day would increase backup times.