MSCE Interview Questions and Answers

QUESTION 61
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 2000 Windows Server computer. Certkiller -DB01 hosts a database
named CK_Orders. Several data capturers enter data into the CK_Orders database
everyday.
Certkiller .com wants you to implement a disaster recovery plan for the CK_Orders
database. You need to ensure that data can be easily recovered. You plan to
perform a full backup every Saturday at 2:00 P.M.
What should you do?

A. Open the Windows Backup utility on Certkiller -DB01. Configure a Full backup to
run weekly on Saturdays at 2:00 P.M.
B. Run the BACKUP DATABASE Transact-SQL statement on the CK_Orders database.
C. Open the Database Backup dialog box for the CK_Orders database in the SQL
Enterprise Manager console. Configure a Full backup to run weekly on Saturdays at 2:00 P.M.
D. Open the Database Backup dialog box for the CK_Orders database in SQL Enterprise
Manager console. Configure a Differential backup to run weekly on Saturdays at 2:00
P.M.

Answer: C

Explanation: You configure a Full backup of a database in SQL Management
Studio. You can accomplish this by right-clicking the appropriate database and
selecting Tasks and then Back Up from the context menu.

Incorrect Answers:
A: The Windows Utility can be used to backup data on a computer. It cannot be used to
backup a database.
B: The BACKUP DATABASE statement can be used to backup a database but it cannot
be used to schedule a recurring backup job.
C: You plan on running a Full backup not a Differential backup.


QUESTION 62
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 Server 2003 computer. Certkiller -DB01 hosts a database
named CK_Orders. Several data capturers enter data into the CK_Orders database
everyday. The Simple Recovery Model is implemented for the CK_Orders database.
A Full backup of the CK_Orders database is performed every Saturday at 2:00
P.M.

You are concerned that data loss may occur should Certkiller -DB01 suffer a hard
disk failure. You decide to implement additional backups of the CK_Orders
database on a daily basis. You want to minimize the amount of time required for the
backups as well as the time required to restore the database.
What should you do?
A. Perform differential backups of the CK_Orders database on every week day.
B. Switch the CK_Orders database to the Full Recovery Model.
C. Perform a Full backup of the CK_Orders database on every week day.
D. Perform incremental backups of the CK_Orders database on every week day.

Answer: A

Explanation: To minimize the amount of time required to perform the backups, you
need to perform differential backups on the week days. Differential backup ups only
backs up the data that has changed since the last Full backup.

Incorrect Answers:
B: The recovery model will not affect the time required for the backup or the restore.
C: Restoring data from transaction log backups will not minimize restore times.
D: SQL Server 2000 does not support incremental backups.

QUESTION 63
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 named
CK_Products residing on a Windows 2000 Server computer. The server has recently
suffered a power failure in the middle of business hours. You bring the server back
online and the SQL Server initiates the recovery process and discovers torn pages.
You are required to repair these errors.
What should you do?

A. SQL Server 2000 should be run in single-user mode
B. The DBCC CHECKDB statement should be run with the REPAIR_FAST option
C. CK_Products should be attached to a different computer
D. CK_Products should be restored from the latest backups

Answer: D

Explanation: In the scenario you should remember to restore the database from the
latest backups and apply all transaction log backups that were made after the most
recent backup, this action ensures that you achieve the scenario objective.

Incorrect Answers:
A: In the scenario this option can not be used to repair the consistency errors of the database.
B: You should not consider this action as this will not help you achieve the scenario
objective.
C: In the scenario you should not use this option because this will not repair the torn pages in the database.

QUESTION 64
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. Certkiller .com has its headquarters in Chicago and branch office in
Miami. 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 for storing the business
information. SQL Server 2000 has been installed with the default
Latin1_General_CI_AS collation. You recently backed up the database and shipped
the tape to the branch office in Miami.
When the tape arrives at the Miami branch office it should be restored to another
SQL Server 2000 computer which has been installed with the Franch_CI_AS
collation. The restored databases will be used independently from the other
databases. The network users will not run distributed queries involving that
database. The tables of the database will not be joined with tables of other
databases. The task should be performed using the least amount of administrative
effort.
What should you do in Miami?

A. The master database should be rebuilt by the Miami administrator.
B. SQL 2000 should be installed on a different computer with the default collation by the Miami administrator.
C. The local settings in the registry should be changed by the Miami administrator.
D. The back up should be restored by the Miami administrator and nothing else should be done.

Answer: D

Explanation: In the scenario you should always remember that SQL Server 2000
supports different collations at the levels of instance, table and column so simply
restoring the backup and doing nothing is the proper administrative step to take.

Incorrect Answers:
A: This option is not a required action in the scenario it was only a requirement when using the previous version of SQL, SQL Server 7.0.
B: There is no need for this administrative task in the scenario as SQL Server 2000
supports different collations at the levels of instance, table and column.
C: There is no need to make this change to the registry in the scenario because SQL
Server 2000 supports different collations at the levels of instance, table and column.

QUESTION 65
You work as the 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. The Certkiller .com
network contains a single database server named Certkiller -DB01, and a file
server named Certkiller -SR22. Certkiller -DB01 runs SQL Server 2000 and
hosts a database named CK_Sales that stores sales data for the company. The
CK_Sales database is configured to use the full recovery model.
You want to implement a backup strategy for the CK_Sales database that includes
log shipping. You successfully install SQL Server 2000 on a new database server
named Certkiller -DB02.
What should you do next?

A. Create a database instance on Certkiller -DB02.
B. Perform a full backup of the CK_Sales database on Certkiller -DB01 and restore it to Certkiller -DB02.
C. Configure Certkiller -DB01 and Certkiller -DB02 as active nodes.
D. Reproduce the user login from Certkiller -DB01 on Certkiller -DB02.

Answer: B

Explanation: Log shipping requires that the primary database be backed up and
restored to the secondary database server. This can be done automatically when you
configure the secondary server for log shipping, or it can be done manually.

Incorrect answers:
A: You have already installed SQL Server 2000 on Certkiller -DB02. You must create
the initial database instance when you install SQL Server 2000.
C: Failover clustering uses nodes, not log shipping.
D: Database mirroring with failover may require that you reproduce the user login from Certkiller -DB01 on Certkiller -DB02. Log shipping does not have this requirement as it is a passive recovery mechanism that does not support automatic failover.

QUESTION 66
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 currently makes use of a SQL Server 2000 database named ProInfo
to store its business transactions. The Certkiller .com management has decided that
the backup strategy consists of full database backups on Sunday evenings and
differential backups on Tuesday and Thursday evenings.
The following Thursday morning when you run the DBCC CHECKDB statement
you discover that several consistency error exist in a table named Production. You
additionally discover that no allocation errors were detected. You are required to
ensure that you make the database consistent with little or no data loss by
accomplishing this task before the Thursday differential database backup occurs.
What should you do?

A. The DBCC CHECKCATALOG (Products) statement should be executed.
B. The DBCC CHECKALLOC (Products, REPAIR_REBUILD) statement should be executed.
C. The database should be restored from the latest database backups.
D. The DBCC CHECKTABLE (Products, REPAIR_REBUILD) statement should be executed.

Answer: D

Explanation: In the scenario you are required to ensure that you make the database
consistent with little or no data loss. This objective is achieved by using this option because the statement will perform the most thorough repairs possible without
losing data.

Incorrect Answers:
A: This option should not be used in the scenario as it is meant for checking for errors in system tables not user tables.
B: This option should not be used in the scenario as you would only be checking the
database for allocation errors and that is not enough.
C: This option should not be used in the scenario because it does not contain the latest transactions that occurred after the completion of the most recent differential backup on Tuesday.

QUESTION 67
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 administering a SQL 2000 database for Certkiller .com who
sells genuine quality approved lumber products to corporate vendors
internationally. The Certkiller .com office is located in a region that suffers
frequently from power outages. You are required to provide a solution that avoids
database inconsistency problems as a result of the power outages. The solution you
are designing should not adversely affect the database performance.
What should you do?

A. A battery-backed disk cache should be used.
B. DBCC DBREINDEX should be executed.
C. DBCC DBREPAIR should be executed.
D. Frequent database backups should be performed.

Answer: A

Explanation: In the scenario you should consider making use of a battery-backed
disk cache which is capable of holding data in the write cache for several days. This
option best suite the scenario conditions.

Incorrect Answers:
B: You should not consider making use of this statement in the scenario because this
statement is superseded by a DROP DATABASE statement used to completely remove
the database.
C: This option should not be used in the scenario because you would adversely be
affecting the performance of the server.
D: This option should seriously be considered when databases are of concern but will not help database inconsistencies caused by power outages.


QUESTION 68
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 discovered that periodically data has been added to,
deleted from and modified in the Products table of the SQL 2000 database. The
table has a unique clustered index which enforces the primary key on the ProductID
column. Certkiller .com concern is that overtime the performance of queries against
the Products table has deteriorated. You decide to run the DBCC SHOWCONTIG
statement against the table and receive the output below:
DBCC SHOWCONTIG scanning 'Products' table ...
Table: 'Products' (123456789); index ID: 1, database ID: 8

TABLE level scan performed.
- Pages Scanned............................: 825
- Extents Scanned..........................: 104
- Extent Switches..........................: 822
- Avg. Pages per Extent....................: 7.9
- Scan Density [Best Count:Actual Count]...: 12.44% [102:820]
- Logical Scan Fragmentation...............: 98.67%
- Extent Scan Fragmentation................: 1.64%
- Avg. Bytes Free per Page.................: 2185.8
- Avg. Page Density (full).................: 77.28%
DBCC execution completed If DBCC printed error messages, contact your
system administrator.
You are required to improve the performance of the queries against the Products
table.
What should you do?

A. The DBCC DBREINDEX (Products) statement should be executed.
B. The DBCC SHOWCONTIG statement should be executed and specify the REBUILD option.
C. The clustered index should first be dropped on the Products table and recreate it.
D. The DBCC SHOWCONTIG statement should be executed and specify the REINDEX option.

Answer: A

Explanation: In the scenario the performance shows the table has become
fragmented over time; to achieve the scenario objective the DBCC DBREINDEX
statement will rebuild the clustered index without dropping the table's primary key.

Incorrect Answers:
B, D: In the scenario you should not consider using this option since the DBCC
SHOWCONTIG statement has no options it is only used to show the extent of data
fragmentation in a table or an index.
C: There is no need for you to perform this action in the scenario because you should
simply use the DBCC DBREINDEX statement which will ensure that the table's primary
key is not dropped.


QUESTION 69
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 discovered during routine maintenance that the physical
size of the transaction log is increasing. Certkiller .com frequently adds a vast
amount of data to the database and the log then expands to accommodate them.
During the course of the day you decide to perform a backup of the transaction log
with the default options and the log file appears to have free space. You still notice that the physical size continues to increase as a result of the bulk-load operations. You are required to reduce the size of the transaction log file.
What should you do?

A. A BACKUP statement should be executed with the UNLOAD option.
B. A BACKUP statement should be executed with the NOUNLOAD option.
C. A DBCC SHRINKFILE statement should be executed.
D. A DBCC DBREINDEX statement should be executed.

Answer: C

Explanation: In the scenario you should always remember that executing the DBCC
SHRINKFILE statement you are able to reduce the physical size of the transaction
log as required in the scenario.

Incorrect Answers:
A: This option should not be used in the scenario because this option would
automatically rewind and release the tape drive after backup and will not decrease the size of the transaction log.
B: This option should not be used in the scenario because this option would not
automatically rewind and release the tape drive after backup and will not decrease the size of the transaction log.
D: This option can not be used to help in the scenario because this DBCC DBREINDEX
statement is used to rebuild all indexes in a particular table.


QUESTION 70
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 periodically running scheduled jobs within
a database maintenance plan. During your maintenance routine you discover that CK_Sales database. You are required to repair those errors as quickly as possible.
What should you do?

A. You should execute the DBCC CHECKALLOC (Inventory, REPAIR_FAST)
statement.
B. You should execute the DBCC CHECKTABLE (Inventory,
REPAIR_ALLOW_DATA_LOSS) statement.
C. You should execute the DBCC CHECKCATALOG (CK_Sales) statement.
D. You should execute the DBCC CHECKDB (CK_Sales,
REPAIR_ALLOW_DATA_LOSS) statement.

Answer: D

Explanation: In the scenario you should remember that in some situations you are
not able to bring the database completely up to data. To correct those errors you are
required to execute the proper DBCC statement in the scenario.

Incorrect Answers:
A: This option should not be used in the scenario because this statement only checks for and corrects allocation errors in the scenario.
B: This option should not be considered for usage in the scenario as this statement would only correct consistency errors and not the allocation errors.
C: This option should not be used in the scenario because the statement is used to check system tables for errors and apply only to system tables this option does not repair any errors. several allocation and consistency errors are in a table named Inventory in the