Free Microsoft 070-462 Practice Test & Real Exam Questions
You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses several custom applications that store data in databases on the Microsoft SQL Server 2012 servers.
A full backup of all databases is taken every night at midnight.
A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at
11pm.
A log backup is taken every 15 minutes for databases configured with the Full Recovery Model.
One application named ABCApp1 stores data in a database named ABCApp1DB.
ABCApp1DB is configured with the Simple Recovery Model. ABCApp1DB fails at 3:25am.
You discover that the last differential backup of ABCApp1DB failed.
You need to restore ABCApp1DB from backup as quickly as possible and minimize data loss.
Which of the following steps should you perform to restore ABCApp1DB? (Choose one or more answers).
A full backup of all databases is taken every night at midnight.
A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at
11pm.
A log backup is taken every 15 minutes for databases configured with the Full Recovery Model.
One application named ABCApp1 stores data in a database named ABCApp1DB.
ABCApp1DB is configured with the Simple Recovery Model. ABCApp1DB fails at 3:25am.
You discover that the last differential backup of ABCApp1DB failed.
You need to restore ABCApp1DB from backup as quickly as possible and minimize data loss.
Which of the following steps should you perform to restore ABCApp1DB? (Choose one or more answers).
Correct Answer: A
Vote an answer
You develop a Microsoft SQL Server database that contains a heap named OrdersHistorical.
You write the following Transact-SQL query:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement.
Which table hint should you use?
You write the following Transact-SQL query:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement.
Which table hint should you use?
Correct Answer: B
Vote an answer
You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Microsoft SQL Server 2012. All databases are hosted on a SAN (Storage Area Network).
You need to design a database solution for a new application.
You are tasked with designing a high-availability database solution.
The solution must include a single copy of the database to save disk space and the database must remain online in the event of a SQL Server failure. What should you include in your solution?
You need to design a database solution for a new application.
You are tasked with designing a high-availability database solution.
The solution must include a single copy of the database to save disk space and the database must remain online in the event of a SQL Server failure. What should you include in your solution?
Correct Answer: C
Vote an answer
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?
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?
Correct Answer: A
Vote an answer
You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load.
You need to limit the number of cores that handle I/O.
What should you configure?
You discover performance issues when large amounts of data are written to tables under heavy system load.
You need to limit the number of cores that handle I/O.
What should you configure?
Correct Answer: B
Vote an answer
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?
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?
Correct Answer: B
Vote an answer
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 Microsoft 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.)
You plan to migrate Sales to Microsoft 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.)
Correct Answer: D,F
Vote an answer
You administer a Microsoft SQL Server environment. The environment contains a SQL Server Reporting Services (SSRS) instance.
You need to ensure that users can create subscriptions.
What should you configure?
You need to ensure that users can create subscriptions.
What should you configure?
Correct Answer: D
Vote an answer
You administer a Microsoft SQL Server instance. You use a two-node SQL Server failover cluster. Node B is primary, and Node A is secondary.
You need to install a security patch on both nodes.
You need to ensure that the following requirements are met:
* Both nodes receive the update.
* Downtime is minimized.
* No data is lost.
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.)
You need to install a security patch on both nodes.
You need to ensure that the following requirements are met:
* Both nodes receive the update.
* Downtime is minimized.
* No data is lost.
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.)
Correct Answer:
Explanation
Starting with the update to Microsoft SQL Server 2012 Service Pack 1, customers with SQL Server 2012 failover cluster instances can greatly benefit from the fully automated update process provided by Cluster-Aware Updating (CAU).
CAU causes minimal down time for SQL Server 2012 client connectivity. While the primary node is being updated, the SQL Server clustered role (also called the resource group) goes through two failovers. The first failover happens before updating the primary node when CAU brings the original primary node into maintenance mode. During the update process on the original primary node, the SQL clustered role is temporarily brought online on a new primary node. The second failover happens once the update process is complete on the original primary node, and CAU fails back the SQL Server clustered role to the original primary node and brings it online.
References:
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?
Correct Answer: C
Vote an answer
You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to prevent users from disabling server audits in Server01.
What should you create?
You need to prevent users from disabling server audits in Server01.
What should you create?
Correct Answer: B
Vote an answer
You administer a Microsoft SQL Server database named Contoso on a server named Server01.
You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales.
What should you create?
You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales.
What should you create?
Correct Answer: F
Vote an answer
You administer a Microsoft SQL Server 2012 database instance. Other applications run on the server. Some of the applications are throwing errors because of insufficient memory.
You need to ensure that the other applications have sufficient memory.
Which setting should you configure?
You need to ensure that the other applications have sufficient memory.
Which setting should you configure?
Correct Answer:
Explanation
You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user.
A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job.
The SSIS package accesses a network share when exporting data from a SQL Server database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server.
You need to ensure that the SQL Server Agent job can execute the SSIS package.
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.)
A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job.
The SSIS package accesses a network share when exporting data from a SQL Server database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server.
You need to ensure that the SQL Server Agent job can execute the SSIS package.
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.)
Correct Answer:
Explanation
SQL Server Agent allows creating a proxy account which defines the security context for the job step.
We need to do is to create a credential to be used by the proxy account.
The created proxy need to have access to the SSIS subsystem
References:
https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-accou
