Daily Archives: Tuesday, July 8, 2014

QUESTION 91
You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?
A.    SERIALIZABLE
B.    SNAPSHOT
C.    READ COMMITTED SNAPSHOT
D.    REPEATABLE READ

Answer: A

(more…)

QUESTION 81
Drag and Drop Question
You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012. The development team develops a new application that uses the SQL Server 2012 functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application. You need to migrate the database without affecting the production environment. Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Answer:

(more…)

QUESTION 71
You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and backup schedule are configured as shown in the following table:

At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database. You need to ensure that the databases are restored. You also need to ensure that data loss is minimal. What should you do?

A.    Perform a partial restore.
B.    Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
C.    Restore the latest full backup.
D.    Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
E.    Perform a page restore.
F.    Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
G.    Perform a point-in-time restore.
H.    Restore the latest full backup. Then, restore the latest differential backup.

Answer: H
Explanation:
File restore
Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored.
Page restore
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models
Piecemeal restore
Restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups. A piecemeal restore begins with a RESTORE DATABASE using the PARTIAL option and specifying one or more secondary filegroups to be restored

(more…)