You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment. All
DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs
access to the SQL Server. You need to create a server role named SpecialDBARole that can perform
the following functions:
• View all databases.
• View the server state.
• Assign GRANT, DENY, and REVOKE permissions on logins.
You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of
privileges necessary. Which SQL statement or statements should you use? Choose all that apply.

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an
application. You need to evaluate existing and potential clustered and non-clustered indexes that
will improve performance. What should you do?

DRAG DROP
You administer a Microsoft SQL Server 2012 instance that contains a database of confidential data.
You need to encrypt the database files at the page level. You also need to encrypt the transaction log
files. Which four 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.)

DRAG DROP
You administer a Microsoft SQL Server 2012 database. The database uses SQL Server Agent jobs to
perform regular FULL and LOG backups. The database uses the FULL recovery model. You plan to
perform a bulk import of a very large text file. You need to ensure that the following requirements
are met during the bulk operation:
• The database transaction log is minimally affected.
• The database is online and all user transactions are recoverable.
• All transactions are fully recoverable prior to the import.
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.)

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size.
The Sales database is configured as shown in the following table.

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum
amount of time. What should you do?

HOTSPOT
You administer a Microsoft SQL Server 2012 database. The database contains a table that has the
following definition:

You want to export data from the table to a flat file by using the SQL Server Import and Export
Wizard. You need to ensure that the following requirements are met:
• The first row of the file contains the first row of data.
• Each record is of the same length.
• The date follows the U.S. date format.
• The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog box in
the answer area.)

DRAG DROP
You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the
following definition:

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to
include only rows that contain a value in the CarrierTrackingNumber column. Which four TransactSQL statements should you use? (To answer, move the appropriate statements from the list of
statements to the answer area and arrange them in the correct order.)

You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains
a schema named Customers that has a table named Regions. A user named UserA is a member of a
role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is
granted the Select permission on the Customers schema. You need to ensure that the Sales role,
including UserA, is disallowed to select from any of the tables in the Customers schema. Which
Transact-SQL statement should you use?

You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains
a schema named Customers that has a table named Regions. A user named UserA is a member of a
role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is
granted the Select permission on the Customers schema. You need to ensure that UserA is
disallowed to select from the Regions table. Which Transact-SQL statement should you use?

You administer a SQL 2012 server that contains a database named SalesDb. SalesDb contains a
schema named Customers that has a table named Regions. A user named UserA is a member of a
role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is
granted the Select permission on the Customers schema. You need to remove the Select permission
for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in
the Customers schema, including the Regions table, through the Sales role permissions. Which
Transact-SQL statement should you use?