Prep and try our 70-458 valid and latest training questions & answers

You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. The package consists of several data flow tasks.
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
What should you do?

• A. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.
• B. Deploy the project by using dtutil.exe with the /COPY SQL option.
• C. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
• D. Store the System::SourceID variable in the custom log table.
• E. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.*tart_execution stored procedures.
• F. View the All Messages subsection of the All Executions report for the package.
• G. Deploy the .ispac file by using the Integration Services Deployment Wizard.
• H. Store the System::ServerExecutionID variable in the custom log table.
• I. Use an event handler for OnError for each data flow task.
• J. Store the System::ExecutionInstanceGUID variable in the custom log table.
• K. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
• L. Use an event handler for OnTaskFailed for the package.
• M. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.
• N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
• O. Deploy the project by using dtutil.exe with the /COPY DTS option.
• P. View the job history for the SQL Server Agent job.
• Q. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
• R. Use an event handler for OnError for the package.

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL 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 remove the Select permission for userA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?

• A. REVOKE SELECT ON Schema: Customers FROM Soles
• B. DENY SELECT ON Object::Regions FROM UserA
• C. DENY SELECT ON Schema: Customers FROM UserA
• D. DENY SELECT ON Object::Regions FROM Sales
• E. REVOKE SELECT ON Object::Regions FROM Soles
• F. EXEC 3p_droprolemember 'Sales', 'UserA'
• G. DENY SELECT ON Schema:: Customers FROM Soles
• H. REVOKE SELECT ON Schema::Customers FROM UserA
• I. REVOKE SELECT ON Object::Regions FROM UserA
• J. EXEC sp_oddrolemember 'Sales', 'UserA'

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
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 chat stores the transactional database fails at 23:32 hours. Attempts to create a tail log backup are unsuccessful.

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 page restore.
• B. Restore the latest full backup. Then, restore the latest differential backup.
• C. Perform a point-in-time restore.
• D. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
• E. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• F. Perform a partial 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.

You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment.
All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server.
You need to create a server role named SpecialDBARole that can perform the following functions:
- View all databases.
- View the server state.
- Assign GRANT, DENY, and REVOKE permissions on logins.
You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of privileges necessary.
Which SQL statement or statements should you use? Choose all that apply.

• A. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
• B. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
• C. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
• D. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
• E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
• F. GRANT VIEW DEFINITION TO [SpecialDBARole];

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. Use the Database Engine Tuning Advisor.
• C. Query the sys.dm_db_missing_index_details DMV.
• D. Query the sys.dm_db_missing_index_columns DMV.