May 2021 updated: microsoft 70-457 exam
Exam Code: 70-457 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-457 Exam.
2021 May 70-457 Study Guide Questions:
Q71. 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: The differential backup of the reporting database fails. Then, the reporting database fails at
14:00 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, and restore the latest differential backup. Then, restore the latest log backup.
B. Perform a point-in-time restore.
C. Restore the latest full backup.
D. 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.
E. Restore the latest full backup. Then, restore the latest differential backup.
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 page restore.
H. Perform a partial restore.
Answer: C
Q72. CORRECT TEXT
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.
You need to create a stored procedure named usp_Customers for App1. Usp_Customers must meet the following requirements:
*NOT use object delimiters.
*Minimize sorting and counting.
*Return only the last name of each customer in alphabetical order.
*Return only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN and END statements.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer: CREATE PROCEDURE usp_Customers @Count int AS SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName
Q73. 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 OK 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
Far out 70-457 training kit:
Q74. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail. You also need to minimize the amount of storage used by the database.
Which configuration should you use?
A. . Two servers configured in different data centers
SQL Server Availability Group configured in Synchronous-Commit Availability Mode
One server configured as an Active Secondary
B. . SQL Server that includes an application database configured to perform transactional replication
C. . Two servers configured in the same data center .
SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
. One server configured as an Active Secondary
D. . Two servers configured in different data centers
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. . Two servers configured in the same data center
. A primary server configured to perform log-shipping every 10 minutes
. A backup server configured as a warm standby
F. . Two servers configured on the same subnet
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
G. . SQL Server that includes an application database configured to perform snapshot replication
H. . Two servers configured in a Windows Failover Cluster in the same data center
. SQL Server configured as a clustered instance
Answer: H
Q75. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?
A. SET XACT_ABORT ON
B. SET ARITHABORT ON
C. TRY
D. BEGIN
E. SET ARITHABORT OFF
F. SET XACT_ABORT OFF
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms190306.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms188792.aspx
Q76. 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
Approved 70-457 practice test:
Q77. You generate a daily report according to the following query:
You need to improve the performance of the query. What should you do?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q78. You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
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. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Reference: http://msdn.microsoft.com/en-us/library/ff773063.aspx
Q79. 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
Q80. You administer all the deployments of Microsoft SQL Server 2012 in your company.
A database contains a large product catalog that is updated periodically.
You need to be able to send the entire product catalog to all branch offices on a monthly basis.
Which configuration should you use?
A. . Two servers configured in the same data center
A primary server configured to perform log-shipping every 10 minutes
A backup server configured as a warm standby B . SQL Server that includes an application database configured to perform transactional replication
B. . Two servers configured in the same data center
SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
One server configured as an Active Secondary D . Two servers configured in a Windows Failover Cluster in the same data center
SQL Server configured as a clustered instance
C. . SQL Server that includes an application database configured to perform snapshot replication
D. . Two servers configured in different data centers
SQL Server Availability Group configured in Synchronous-Commit Availability Mode
One server configured as an Active Secondary
E. . Two servers configured on the same subnet
SQL Server Availability Group configured in Synchronous-Commit Availability Mode
F. . Two servers configured in different data centers
SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
Answer: E
Reference:
http://msdn.microsoft.com/en-us/library/ms151832.aspx
see more http://www.2passeasy.com/exam/70-457/