Note: The answer is for reference only, you need to understand all question.
QUESTION 1
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, simply select the option or options in the answer area that you would configure.)


Hot Area: Answer:


QUESTION 2
You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL statement should you use?
A. USE Database2
B. EXECUTE AS OWNER
C. USE Database1

D. EXECUTE AS CALLER
Answer: B
QUESTION 3
You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node
B. A single instance of SQL Server is installed on the cluster. An additional node named Node C has been added to the existing cluster. You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?
A. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node
A.
B. Use Node A to install SQL Server on Node C.
C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
D. Use Cluster Administrator to add a new Resource Group to Node B.


Answer: C
QUESTION 4
You administer three Microsoft SQL Server 2012 servers named ServerA, ServerB, and ServerC. ServerA is the acting principal and ServerB is the mirror. You need to add ServerC as a witness to the existing mirroring session between ServerA and ServerB. You need to achieve this goal without delaying synchronization. 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.)
Build List and Reorder: Answer:

QUESTION 5
You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node
B. A single instance of SQL Server is installed on the cluster. An additional node named Node C has been added to the existing cluster. You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?
A. Run the New SQL Server stand-alone installation Wizard on Node C.
B. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
C. Use Node B to install SQL Server on Node C.

D. Use Node A to install SQL Server on Node C.

Answer: B
QUESTION 6
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.
A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
D. GRANT VIEW DEFINITION TO [SpecialDBARole];
E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];


Answer: BCF
QUESTION 7
You administer three Microsoft SQL Server 2008 R2 instances. Database mirroring is configured in High-Safety mode with Automatic Failover between the following three servers:

SQL1 is the Principal server.
SQL2 is the mirror server.
SQL3 is the witness server. You need to upgrade SQL1 and SQL2 to SQL Server 2012. You need to ensure that downtime is minimized during the upgrade. Which six 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.)
Build List and Reorder:


Answer:

QUESTION 8
You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment model. The project contains many packages. It is deployed on a server named Development1. The project will be deployed to several servers that run SQL Server 2012. The project accepts one required parameter. The data type of the parameter is a string. A SQL Agent job is created that will call the master.dtsx package in the project. A job step is created for the SSIS package. The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter. You need to configure SSIS on the Development1 server to pass the Environment Variable to the package. Which four actions should you perform in sequence by using SQL Server Management Studio? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder:


Answer:

QUESTION 9
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 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?
BULK_LOGGED
NO_CHECKSUM
FULL
RESTART
CHECKSUM
STANDBY
DBO.ONLY
NORECOVERY
SIMPLE
Transaction log
Differential
CONTINUE_AFTER_ERROR
COPY_ONLY
SKIP

Answer: J
QUESTION 10
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
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?
NORECOVERY FULL NO_CHECKSUM CHECKSUM Differential 8ULK_LOGGED STANDBY RESTART

SKIP
Transaction log
DBO ONLY
COPY_ONLY
SIMPLE
CONTINUE AFTER ERROR

Answer: L
QUESTION 11
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
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?
STANDBY
Differential
FULL
CHECKSUM
BULK_LOGGED
CONTINUE_AFTER_ERROR
SIMPLE
DBO_ONLY
COPY_ONLY
SKIP
RESTART
Transaction log
NO_CHECKSUM
NORECOVERY

Answer: F
QUESTION 12
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
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?
Differential
Transaction Log
FULL
SIMPLE
SKIP
RESTART
STANDBY
CHECKSUM
DBO_ONLY
COPY_ONLY
NORECOVERY
NO_CHECKSUM
CONTINUE_AFTER_ERROR
BULK_LOGGED

Answer: K
QUESTION 13
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
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 minimum amount of data is lost. Which recovery model should the database use?

FULL DBO_ONLY CONTINUE_AFTER_ERROR CHECKSUM NO_CHECKSUM SIMPLE Transaction log SKIP RESTART COPY_ONLY NORECOVERY BULK_LOGGED Differential STANDBY

Answer: A
QUESTION 14
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?
A. DENY SELECT ON Object::Regions FROM Sales
B. DENY SELECT ON Schema::Customers FROM Sales
C. REVOKE SELECT ON Object::Regions FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM Sales
E. DENY SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM UserA

G. REVOKE SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Schema::Customers FOR UserA
I. EXEC sp_addrolemember 'Sales', 'UserA'
J. EXEC sp_droprolemember 'Sales', 'UserA'


Answer: D
QUESTION 15
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 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?

A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Schema::Customers FROM UserA
C. EXEC sp_addrolemember 'Sales', 'UserA'
D. REVOKE SELECT ON Object::Regions FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. EXEC sp_droproiemember 'Sales', 'UserA'

G. REVOKE SELECT ON Schema::Customers FROM UserA
H. DENY SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema::Customers FROM Sales

J. REVOKE SELECT ON Schema::Customers FROM Sales


Answer: D
QUESTION 16
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?
A. DENY SELECT ON Object::Regions FROM Sales
B. DENY SELECT ON Schema::Customers FROM Sales
C. REVOKE SELECT ON Object::Regions FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM Sales
E. DENY SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM UserA

G. REVOKE SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemember 'Sales', 'UserA'
J. EXEC sp_droprolemember 'Sales', 'UserA'


Answer: E
QUESTION 17
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?
A. REVOKE SELECT ON Schema::Customers FROM UserA
B. DENY SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember 'Sales', 'UserA'
D. DENY SELECT ON Object::Regions FROM Sales

E. REVOKE SELECT ON Object::Regions FROM UserA

F. DENY SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Schema::Customers FROM UserA
H. EXEC sp_droprolemember 'Sales', 'UserA'

I. REVOKE SELECT ON Object::Regions FROM Sales
J. REVOKE SELECT ON Schema::Customers FROM Sales


Answer: F
QUESTION 18
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 and 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 the Regions table. Which Transact-SQL statement should you use?
A. REVOKE SELECT ON Schema::Customers FROM UserA
B. REVOKE SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember 'Sales', 'UserA'
D. DENY SELECT ON Schema::Customers FROM Sales
E. EXEC sp_droprolemember 'Sales', 'UserA'

F. REVOKE SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Object::Regions FROM UserA

H. REVOKE SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema::Customers FROM UserA
J. DENY SELECT ON Object::Regions FROM Sales


Answer: J
QUESTION 19
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 any of the tables in the Customers schema. Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Object::Regions FROM Sales
C. REVOKE SELECT ON Schema::Customers FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. REVOKE SELECT ON Object::Regions FROM UserA
G. DENY SELECT ON Schema::Customers FROM Sales
H. DENY SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemember 'Sales', 'UserA'
J. EXEC sp droprolemember 'Sales', 'UserA'
Answer: H
QUESTION 20

You are a database administrator of a Microsoft SQL Server 2012 environment. The environment contains two servers named SQLServer01 and SQLServer02. The database Contoso exists on SQLServer01. You plan to mirror the Contoso database between SQLServer01 and SQLServer02 by using database mirroring. You need to prepare the Contoso database for database mirroring. 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.)
Build List and Reorder:

Answer:

QUESTION 21
You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a database named AdventureWorks. You need to prepare the AdventureWorks database for database mirroring.


ServerB will act as the mirror in a mirroring partnership along with ServerA. 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.)
Build List and Reorder: Answer:

QUESTION 22
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.)
Answer:

QUESTION 23
You administer a Microsoft SQL Server 2012 database. You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!'CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate';
BACKUP CERTIFICATE TDE_Certificate TO FILE ='d:\TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'd:\TDE_Certificate.key', ENCRYPTION BY PASSWORD ='MyPassword1!');
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate; ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location. A hardware failure occurs and so a new server must be installed and configured. After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database. You need to be able to restore the database. Which