Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 271:

  You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured.

  When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required detail.

  The following error message is an example error message:

  "The job failed. The Job was invoked by User CONTOSO\ServiceAccount. The last step to run was step 1 (Subplan_1)."

  You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained.

  What should you do?

  A. Configure output files.
  B. Expand agent logging to include information from all events.
  C. Disable the Limit size of job history log feature.
  D. Configure event forwarding.
  B. Expand agent logging to include information from all events.

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ms175488.aspx

• Question 272:

  You administer a Microsoft SQL Server 2012 instance.

  The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week.

  You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:

  No more than 5 minutes worth of transactions are lost.

  Data can be recovered by using the minimum amount of administrative effort.

  What should you do? Choose three.

  A. Configure the database to use the SIMPLE recovery model.
  B. Create a DIFFERENTIAL database backup every 4 hours.
  C. Create a LOG backup every 5 minutes.
  D. Configure the database to use the FULL recovery model.
  E. Create a FULL database backup every 24 hours.
  F. Create a DIFFERENTIAL database backup every 24 hours.
  C. Create a LOG backup every 5 minutes.
  D. Configure the database to use the FULL recovery model.
  E. Create a FULL database backup every 24 hours.

  ---

  If there were four correct answers, then option B, Create a DIFFERENTIAL database backup every 4 hours, would be the fourth correct alternative.

• Question 273:

  You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

  

  You create a view named VwEmployee as shown in the following Transact-SQL statement.

  

  Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.

  Which Transact-SQL statement should you use?

  

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  B. OptionB

• Question 274:

  You have an index called Employee_IDX on the table Employees. Which of the following will rebuild this index online?

  A. ALTER INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);
  B. CREATE INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);
  C. REBUILD ONLINE INDEX Employee_IDX ON Employees;
  D. ALTER INDEX REBUILD ONLINE Employee_IDX;
  A. ALTER INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);

  ---

  References: https://msdn.microsoft.com/en-us/library/ms177442%28v=sql.110%29.aspx

• Question 275:

  DRAG DROP

  You administer a Microsoft Azure SQL Database database used for data warehouse operations.

  The database contains a table named OrdersHistory, defined as follows:

  

  A weekly ETL (extract-transform-load) runs a large INSERT statement to add data into the OrdersHistory table. The process is taking a long time to complete. You discover that the bulk of the process is performing non-clustered index updates on the OrdersHistory table.

  You need to improve the performance of the ETL process faster. You need to meet the following requirements: Use minimal administrative effort. Avoid losing existing permissions on existing objects.

  Which three actions should you perform? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

  ---

  Step 1: Run the following statement:

  ALTER INDEX ALL on OrdersHistory DISABLE.

  Step 2: Run the INSERT statement.

  Step 3: Run the following statement:

  ALTER INDEX ALL on OrdersHistory REBUILD

  The ALTER INDEX ALL REBUILD statement rebuilds and enables all disabled indexes on the table.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints

• Question 276:

  You administer a Microsoft SQL Server database. The database is currently configured to log ship to a secondary server.

  You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail-log backup.

  You need to leave the primary database in a read-only state that can also have further transaction logs applied.

  Which option of the BACKUP LOG command should you use?

  A. FORMAT
  B. NO_TRUNCATE
  C. STANDBY
  D. NORECOVERY
  C. STANDBY

  ---

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

• Question 277:

  You plan to install Microsoft SQL Server 2012 for a web hosting company.

  The company plans to host multiple web sites, each supported by a SQL Server database.

  You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.

  Which edition should you choose?

  A. Express Edition with Tools
  B. Standard Edition
  C. Web Edition
  D. Express Edition with Advanced Services
  B. Standard Edition

• Question 278:

  Which of the following is NOT a pre-defined server role in SQL Server 2012?

  A. securityadmin
  B. serveradmin
  C. sysdba
  D. dbcreator
  C. sysdba

  ---

  References: https://msdn.microsoft.com/en-us/library/ms188659%28v=sql.110%29.aspx

• Question 279:

  You work as the Senior Database Administrator (DBA) at ABC.com. The company has a main office and 10 branch offices. Each branch office contains a single database server running Microsoft SQL Server 2012. The main office has

  multiple clustered servers running Microsoft SQL Server 2012.

  Your role includes the management of the entire Microsoft SQL Server 2012 infrastructure.

  The company runs a custom application that stores data in a large Microsoft SQL Server 2012 database.

  The primary database is hosted in the main office. Each branch office SQL Server hosts a copy of the database.

  You need to configure a solution that will replicate the entire primary database from the main office SQL Server every weekend.

  What should you include in your solution?

  A. Transactional Replication
  B. Log Shipping
  C. Snapshot Replication
  D. SQL Server Availability Group
  C. Snapshot Replication

• Question 280:

  You are migrating a database named Orders to a new server that runs Microsoft SQL Server.

  You attempt to add a SQL login [User1], to the database. However, you receive the following error message:

  "User already exists in current database."

  You need to configure the [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 [User1] FOR LOGIN [User1]; ALTER ROLE [db_owner] ADD MEMBER [User1];
  B. ALTER SERVER ROLE [sysadmin] ADD MEMBER [User1];
  C. ALTER USER [User1] WITH LOGIN [User1];
  D. ALTER ROLE [db_owner] ADD MEMBER [User1];
  C. ALTER USER [User1] WITH LOGIN [User1];

  ---

  The WITH LOGIN clause enables the remapping of a user to a different login.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-2017