Top 15 pdf 70-462 for IT learners (61 to 75)

Question No. 61

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 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database. You need to ensure that the transactional database is restored. 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. Perform a point-in-time restore. 

D. Restore the latest full backup. 

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

F. Perform a page restore. 

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

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

Answer:


Question No. 62

You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders. 

Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD. 

You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation. 

Which Transact-SQL command should you use? 

A. ALTER INDEX IX_OrderDateON CustomerOrderDISABLE 

B. ALTER INDEX IX_OrderDateON CustomerOrderENABLE 

C. ALTER INDEX IX_OrderDateON CustomerOrderREORGANIZE 

D. ALTER INDEX IX OrderDateON CustomerOrderREBUILD 

Answer:


Question No. 63

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:


Question No. 64

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. 

The Sales database is configured as shown in the following table. 


You discover that all files except Sales_2.ndf are corrupt. 

You need to recover the corrupted data in the minimum amount of time. 

What should you do? 

A. Perform a restore from a full backup. 

B. Perform a transaction log restore. 

C. Perform a file restore. 

D. Perform a filegroup restore. 

Answer:


Question No. 65

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

You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server. 

What should you create? 

A. an Extended Event session 

B. a Policy 

C. a Database Audit Specification 

D. an Alert 

E. a Server Audit Specification 

F. a SQL Profiler Trace 

G. a Resource Pool 

Answer:


Question No. 66

You administer a Microsoft SQL Server 2012 instance. 

You need to stop a blocking process that has an SPID of 64 without stopping other processes 

What should you do? 

A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64 

B. Restart the SQL Server service. 

C. Execute the following Transact-SQL statement: KILL 64 

D. Execute the following Transact-SQL statement: ALTER SESSION KILL '64' 

Answer:


Question No. 67

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:


Question No. 68

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. 

You need to ensure that the following requirements are met: 

. Future modifications to the table definition will not affect the applications' ability to access data. 

. The new object can accommodate data retrieval and data modification. 

You need to achieve this goal by using the minimum amount of changes to the applications. 

What should you create for each application? 

A. Synonyms 

B. Common table expressions 

C. Views 

D. Temporary tables 

Answer:


Question No. 69

You administer a Microsoft SQL Server 2012 database. 

The database contains a Product table created by using the following definition: 


You need to ensure that the minimum amount of disk space is used to store the data in the Product table. 

What should you do? 

A. Convert all indexes to Column Store indexes. 

B. Implement Unicode Compression. 

C. Implement row-level compression. 

D. Implement page-level compression. 

Answer:


Question No. 70

You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. 

You grant User1 access to execute the stored procedure. 

You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. 

What should you do? (Each correct answer presents a complete solution. Choose all that apply.) 

A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role. 

B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure. 

C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement. 

D. Grant the db_owner role on the database to User1. 

E. Grant the sysadmin role on the database to User1. 

Answer: BC 


Question No. 71

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. SQL user without login 

B. User mapped to an asymmetric key 

C. Domain user 

D. login mapped to a virtual account 

Answer:


Question No. 72

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. 

You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: 

. Does not return an error 

. Closes all opened transactions 

Which Transact-SQL statement should you use? 

A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH 

B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW; END CATCH 

C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH 

D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH 

Answer:


Question No. 73

You maintain several databases on a 32-bit Microsoft SQL Server 2005 instance on a Windows Server 2008 R2 64-bit server. 

You need to migrate the databases to a 64-bit SQL Server 2012 instance on the same server. You also need to ensure that the new Transact-SQL functionality in SQL Server 2012 can be used in the database after the migration. 

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

A. Perform a side-by-side installation of a 32-bit SQL Server 2012 instance. 

B. Perform a side-by-side installation of a 64-bit SQL Server 2012 instance. 

C. Perform an in-place upgrade to 64-bit SQL Server 2012. 

D. Detach the database from the old instance and attach it to the new instance. 

E. Change the compatibility level of the database. 

Answer: BDE 


Question No. 74

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:


Question No. 75

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


Microsoft 70-462 Certification Sample Questions and Answers: http://www.braindumpsall.net/70-462-dumps/

P.S. New 70-462 dumps PDF: http://www.4easydumps.com/70-462-dumps-download.html