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?

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 implement
log shipping of the financial database to another SQL Server 2012 instance. You decide to failover to
this secondary database. You need to ensure that all transactions will be replicated to the secondary
database. 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. You need to
ensure that the backup size is as small as possible. Which backup should you perform every two
hours?

DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data
warehouse. You are developing the part of the SSIS package that populates the ProjectDates
dimension table. The business key of the ProjectDates table is the ProjectName column. The
business user has given you the dimensional attribute behavior for each of the four columns in the
ProjectDates table:
• ExpectedStartDate – New values should be tracked over time.
• ActualStartDate – New values should not be accepted.
• ExpectedEndDate – New values should replace existing values.
• ActualEndDate – New values should be tracked over time.
You use the SSIS Slowly Changing Dimension Transformation. You must configure the Change Type
value for each source column. Which settings should you select? (To answer, select the appropriate
setting or settings in the answer area.)

You are developing a data flow transformation to merge two data sources. One source contains
product data and the other source contains data about the country in which the product was
manufactured. Both data sources contain a two-character CountryCode column and both use SQL
Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column
in ascending order. You use a Merge Join transformation to join the data.

You need to ensure that the Merge Join transformation works correctly without additional
transformations. What should you do? (Each answer presents a part of the solution. Choose all that
apply.)

You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two
different sources. One source is hosted in a SQL Azure database. Each source contains products for
different distributors. Products for each distributor source must be combined for insertion into a
single product table destination. You need to select the appropriate data flow transformation to
meet this requirement. Which transformation types should you use? (Each correct answer presents
a complete solution. Choose all that apply.)

You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup
transformation. The reference data to be used in the transformation does not change. You need to
reuse the Fuzzy Lookup match index to increase performance and reduce maintenance. What should
you do?

You are developing a SQL Server Integration Services (SSIS) package. You need to design a package to
change a variable value during package execution by using the least amount of development effort.
What should you use?