Microsoft 70-462 Free Practice Questions

Q1. You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. 

Some users must be prevented from deleting records in any of the tables in the Sales schema. 

You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. 

What should you do? 

A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role. 

B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role. 

C. Deny Delete permissions on each table in the Sales schema for each user. 

D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role. 

View Answer

Q2. 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. 

View Answer

Q3. 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') 

View Answer

Q4. You administer a Microsoft SQL Server 2012 database that includes a table named dbo.Log. This table contains millions of records about user activity in an application. 

Records in dbo.Log that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts. 

You need to be able to modify dbo.Log without requiring any changes to the applications that utilize dbo.Log. 

Which type of solution should you use? 

A. Extended events 

B. Columnstore index 

C. Partitioned tables 

D. Read committed snapshot 

View Answer

Q5. You administer a SQL Server 2012 database instance. 

You need to configure the SQL Server Database Engine service on a failover cluster. 

Which user account should you use? 

A. The BUILTIN\LocalService account 

B. A domain user 

C. A local administrative user 

D. The BUILTIN\NetworkService account 

View Answer

Q6. You develop a database for a travel application. 

You need to design tables and other database objects. 

You create the Airline_Schedules table. 

You need to store the departure and arrival dates and times of flights along with time zone information. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

View Answer

Q7. You administer a Microsoft SQL Server 2012 instance. 

You need to configure a new database to support FILETABLES. 

What should you do? Choose all that apply. 

A. Disable FILESTREAM on the Database. 

B. Enable FILESTREAM on the Server Instance. 

C. Configure the Database for Partial Containment. 

D. Create a non-empty FILESTREAM file group. 

E. Enable Contained Databases on the Server Instance. 

F. Set the FILESTREAM directory name on the Database. 

View Answer

Q8. You develop a database for a travel application. 

You need to design tables and other database objects. 

You create a stored procedure. 

You need to supply the stored procedure with multiple event names and their dates as parameters. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

View Answer

Q9. 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' 

View Answer

Q10. You administer a Microsoft SQL Server 2012 database. 

You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements: 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q11. You install Microsoft SQL Server 2012 on a new server. 

After setup is complete, you attempt to start the SQL Server service. After being in a starting state for a few moments, the service goes back to a stopped state. You need to determine the cause of the failure. 

Which file should you use? 

A. %programfiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\Errorlog 

B. %programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt 

C. %programfiles%\Microsoft SQL Server\110\Shared\ErrorDumps\SQLDmpr[XXXX].mdmp 

D. %programfiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf 

View Answer

Q12. You administer a Microsoft SQL Server 2012 database. 

You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements: 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!' 

CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate' 

BACKUP CERTIFICATE TDE_Certificate TO FILE = ''d:\TDE_Certificate.cer' 

WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key', ENCRYPTION BY PASSWORD = 

'MyPassword1!'); 

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_256 

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate; 

ALTER DATABASE Orders SET ENCRYPTION ON; 

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. ALTER DATABASE Master SET ENCRYPTION OFF; 

B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' 

WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key', DECRYPTION BY PASSWORD = 

'MyPassword1!'); 

C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate' 

USE Orders; 

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_256 

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate; 

D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' 

View Answer

Q13. 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? 

A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account. 

B. Disable the guest user in the msdb database. 

C. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile. 

D. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile. 

View Answer

Q14. You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. 

You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. 

You need to configure the SQL Server environment to support these packages. 

What should you do? 

A. Create SSIS configuration files for the packages. 

B. Create an Integration Services catalog. 

C. Install Data Quality Services. 

D. Install Master Data services. 

View Answer

Q15. 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. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin; 

B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs]; 

C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin; 

D. GRANT VIEW DEFINITION TO [SpecialDBARole]; 

E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin; 

F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole]; 

View Answer