70-462 Exam Questions - Online Test

70-462 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Act now and download your Microsoft exam 70 462 test today! Do not waste time for the worthless Microsoft 70 462 dumps tutorials. Download Abreast of the times Microsoft Administering Microsoft SQL Server 2012 Databases exam with real questions and answers and begin to learn Microsoft exam 70 462 with a classic professional.

Q31. You administer a Microsoft SQL Server 2012 instance. 

You discover that the SQL Agent Error Log file is rapidly growing in size. 

You need to ensure that the SQL Agent Error Log file does not grow rapidly when SQL Server agent jobs execute. 

What should you do? 

A. Execute the sp_cycle_agent_errorlog stored procedure. 

B. Configure event forwarding. 

C. Enable the Auto Shrink option on the master database. 

D. Enable the Auto Shrink option on the msdb database. 

E. Disable the Include execution trace messages feature. 

Answer: E 

Q32. A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year. 

Which Transact-SQL query should you use? 

A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits 

B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits 

C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits 

D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits 

Answer: C

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

Q34. You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012 database solution. 

You are tasked with designing a scale-out and high-availability SQL Server 2012 Online 

Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances. 

Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be indexed. 

What should you include in your solution? 

A. You should include two servers configured in an Active-Active SQL Server 2012 Cluster 

B. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to a secondary database. 

C. You should include two servers configured in an Active-Passive SQL Server 2012 Cluster. 

D. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group. 

E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group 

Answer: B 

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

Q36. You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica. 

You have multiple queries that read data and produce reports from the database. 

You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. 

What should you do? 

A. Set the Availability Mode property of HA/Server02 to Asynchronous commit. 

B. Set the Readable Secondary property of HA/Server02 to Read-intent only. 

C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections. 

D. Set the Availability Mode property of HA/Server01 to Asynchronous commit. 

Answer: B 

Q37. 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: 

One of the hard disk drives that stores the reporting database fails at 16:40 hours. 

You need to ensure that the reporting database is restoreD. You also need to ensure that data loss is minimal. 

What should you do? 

A. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup. 

B. Perform a partial restore. 

C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. 

D. Perform a point-in-time restore. 

E. Restore the latest full backup. 

F. Perform a page restore. 

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

H. Restore the latest full backup. Then, restore the latest differential backup. 

Answer: E 

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

Q39. You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction. 

You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index. 

You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process. 

Which Transact-SQL batch should you use? 

A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REORGANIZE 

B. ALTER INDEX ALL ON AccountTransaction REBUILD 

C. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REBUILD 

D. CREATE INDEX IDXAccountTransactionAccountCode ON AccountTransaction.AccountCode WITH DROP EXISTING 

Answer: A 

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