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.)

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.)

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.)

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?

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 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?

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 following
requirements are met:
• The Sales role does not have the Select permission on the Customers schema.
• UserA has the Select permission on the Regions table.
Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a
storage area network (SAN). The financial database has the following characteristics:
• A data file of 2 terabytes is located on a dedicated LUN (drive D).
• A transaction log of 10 GB is located on a dedicated LUN (drive E).
• Drive D has 1 terabyte of free disk space.
• Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday
between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The
Finance department loads large CSV files into a number of tables each business day at 11:15 hours
and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to
the database. These data load operations must occur in the minimum amount of time. A full
database backup is performed every Sunday at 10:00 hours. Backup operations will be performed
every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that
your backup will continue if any invalid checksum is encountered. Which backup option should you
use?

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a
storage area network (SAN). The financial database has the following characteristics:
• A data file of 2 terabytes is located on a dedicated LUN (drive D).
• A transaction log of 10 GB is located on a dedicated LUN (drive E).
• Drive D has 1 terabyte of free disk space.
• Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday
between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The
Finance department loads large CSV files into a number of tables each business day at 11:15 hours
and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to
the database. These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be
performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
On Wednesday at 10:00 hours, the development team requests you to refresh the database on a
development server by using the most recent version. You need to perform a full database backup
that will be restored on the development server. Which backup option should you use?