[Tested] 70-462 Microsoft test question 1-15 (Apr 2016)


♥♥ 2017 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-462 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-462 Exam Dumps (PDF & VCE):
Available on: http://www.certleader.com/70-462-dumps.html


70-462 Product Description:
Exam Number/Code: 70-462 vce
Exam name: Administering Microsoft SQL Server 2012 Databases
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Instant Access to Free VCE Files: Microsoft 70-462 Administering Microsoft SQL Server 2012 Databases

70-462 examcollection

Precise of 70-462 free question materials and free demo for Microsoft certification for IT learners, Real Success Guaranteed with Updated 70-462 pdf dumps vce Materials. 100% PASS Administering Microsoft SQL Server 2012 Databases exam Today!

2016 Apr 70-462 Study Guide Questions:

Q1. You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA. 

You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table. 

You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. 

Which Transact-SQL statement should you use? 

A. GRANT DELETE ON Purchases. Suppliers TC ContosoUser 

B. CREATE PROCEDURE Purchases.PurgelnactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser 

C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser 

D. CREATE PROCEDURE Purchases. PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases. PurgeInactiveSuppliers TO ContosoUser 

Answer: B 


Q2. You administer a Microsoft SQL Server 2012 environment. One of the SQL Server 2012 instances contains a database named Sales. 

You plan to migrate Sales to Windows Azure SQL Database. 

To do so, you need to implement a contained database. 

What should you do? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Enable server property Contained Database Authentication 

B. Set database containment to partial 

C. Set database containment to full 

D. Disable server property cross db ownership chaining 

E. Set database containment to AZURE 

F. Disable server property Contained Database Authentication 

Answer: ABE 


Q3. You administer a Microsoft SQL Server 2012 database. 

You need to ensure that the size of the transaction log file does not exceed 2 GB. 

What should you do? 

A. Execute sp_configure 'max log size', 2G. 

B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter. 

C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log. 

D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file. 

Answer: D 


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

A. Query the sys.dm_db_index_usage_stats DMV. 

B. Query the sys.dm_db_missing_index_details DMV. 

C. Use the Database Engine Tuning Advisor. 

D. Query the sys.dm_db_missing_index_columns DMV. 

Answer: C 


Q5. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) 


Which Transact-SQL query should you use? 

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW 

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS 

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO 

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO 

F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

Answer: G


70-462 exam engine

Up to the immediate present 70-462 practice exam:

Q6. You administer a Microsoft SQL Server 2012 database. 

Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error. 

You need to identify the root cause of the issue by retrieving the error message. 

What should you do? 

A. Create an Extended Events session by using the sqlserver.error_reported event. 

B. Create a SQL Profiler session to capture all ErrorLog and EventLog events. 

C. Flag all stored procedures for recompilation by using sp_recompile. 

D. Execute sp_who. 

Answer: A 


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


Q8. 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 minimum amount of data is lost. 

Which recovery model should the database use? 

A. FULL 

B. DBO_ONLY 

C. CONTINUE_AFTER_ERROR 

D. CHECKSUM 

E. NO_CHECKSUM 

F. SIMPLE 

G. Transaction log 

H. SKIP 

I. RESTART 

J. COPY_ONLY 

K. NORECOVERY 

L. BULK_LOGGED 

M. Differential 

N. STANDBY 

Answer: L 


Q9. You use a contained database named ContosoDb within a domain. 

You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. 

Which type of user should you create? 

A. User mapped to a certificate 

B. SQL user without login 

C. Domain user 

D. SQL user with login 

Answer: C 


Q10. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. 

You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01. 

What should you create? 

A. A Database Audit Specification 

B. A Policy 

C. An Alert 

D. A SQL Profiler Trace 

E. A Resource Pool 

F. An Extended Event session 

G. A Server Audit Specification 

Answer: G 


70-462 practice exam

Precise 70-462 bootcamp:

Q11. You are the administrator of a Microsoft SQL Server 2012 server. 

Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications 

You need to dynamically limit resource consumption. 

What should you do? 

A. Set up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources. 

B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor. 

C. Configure Extended Events to monitor and restrict resource limits allowed by each application type. 

D. Create a new Plan Guide with a Scope Type of sql and define the resource limits for each application. 

Answer: A 


Q12. You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. 

You attempt to add the [Corpnet\User1] login to the database. However, you receive the following error message: 

"User already exists in current database." 

You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the original permissions. You need to achieve this goal by using the minimum required permissions. 

Which Transact-SQL statement should you use? 

A. DROP USER [User1]; 

CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; 

ALTER ROLE [db_owner] ADD MEM3ER [Corpnet\User1]; 

B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1]; 

C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1]; 

D. ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1]; 

Answer: C 


Q13. You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance. 

The application is unable to connect to the database on the named instance. 

You need to ensure that the application can connect to the named instance. 

What should you do? 

A. Configure the application as data-tiered. 

B. Open port 1433 on the Windows firewall on the server. 

C. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group. 

D. Start the SQL Server Browser Service. 

Answer: D 


Q14. You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). 

Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. 

You need to reduce the occurrence of these timeout errors. 

What should you do? 

A. Set the Merge agent on the problem subscribers to use the slow link agent profile. 

B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication. 

C. Change the Merge agent on the problem subscribers to run continuously. 

D. Set the Remote Connection Timeout on the Publisher to 0. 

Answer: A 


Q15. 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 Transact-SQL statement should you use before attempting the restore? 

A. ALTER DATABASE Master SET ENCRYPTION OFF; 

B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' 

WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key', DECRYPTION BY PASSWORD = 

'MyPassword1!'); 

C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate' 

USE Orders; 

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_256 

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate; 

D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' 

Answer: B 



see more Administering Microsoft SQL Server 2012 Databases