[2021-New] Microsoft 70-764 Dumps With Update Exam Questions (11-20)

70-764 Dumps

70-764 Exam Questions - Online Test

70-764 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table.

You need to monitor WingDB and gather information for troubleshooting issues. What should you use?

A. sp_updatestats

B. sp_lock

C. sys.dm_os_waiting_tasks

D. sys.dm_tran_active_snapshot_database_transactions

Answer: B

Explanation:

The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are happening on your system. This procedure returns much of its

information from the syslock info in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests.

Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.

sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

References:https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql

Q2. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.

You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01. What should you create?

A. a Database Audit Specification

B. a Policy

C. an Alert

D. a SQL Profiler Trace

E. a Resource Pool

F. an Extended Event session

G. a Server Audit Specification

Answer: G

Q3. DRAG DROP

You are the database administrator for a Microsoft SQL Server instance. You develop an Extended Events package to look for events related to application performance.

You need to change the event session to include SQL Server errors that are greater than error severity 15.

Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Answer:

Explanation:

Step 1: ALTER EVENT SESSION Contoso1 ON SERVER

Step 2: ADD EVENT …

Step 3: (ACTION ... Step 4: WHERE...

Step 5: ) GO

Example: To start an Extended Events sessions in order to trap SQL Server errors with severity greater than 10,just run the following script:

CREATE EVENT SESSION [error_trap] ON SERVER

ADD EVENT sqlserver.error_reported (

ACTION

(package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.c lient_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username, sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlser ver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)

WHERE ([severity]>10)

)

ADD TARGET package0.event_file (

SET filename=N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap.xel'

) WITH (

STARTUP_STATE=OFF

) GO

Q4. You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).

The financial database has the following characteristics:

A data file of 2 terabytes is located on a dedicated LUN (drive D). A transaction log of 10 GB is located on a dedicated LUN (drive E). Drive D has 1 terabyte of free disk space.

Drive E has 5 GB of free disk space.

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT

These data load operations must occur in the minimum amount of time.

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

You need to ensure that your backup will continue if any invalid checksum is encountered. Which backup option should you use?

A. STANDBY

B. Differential

C. FULL

D. CHECKSUM

E. BULK_LOGGED

F. CONTINUE_AFTER_ERROR

G. SIMPLE

H. DBO_ONLY

I. COPY_ONLY

J. SKIP

K. RESTART

L. Transaction log

M. NO_CHECKSUM

N. NORECOVERY

Answer: F

Explanation:

Verified answer as correct.

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

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backuprestorebase.continueaftererror.aspx

Q5. Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.

Clients connect to databases by using line-of-business applications. Developers connect by using SQL Server Management Studio (SSMS).

You need to provide permissions to a service account that will be used to provision a new database for a client.

Which permission should you grant?

A. DDLAdmin

B. db_datawriter

C. dbcreator

D. dbo

E. View Database State

F. View Server State

G. View Definition

H. sysadmin

Answer: C

Explanation:

Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

References:https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles

Q6. 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 the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema. Which Transact-SQL statement should you use?

A. REVOKE SELECT ON Schema::Customers FROM UserA

B. DENY SELECT ON Object::Regions FROM UserA

C. EXEC sp_addrolemember 'Sales', 'UserA'

D. DENY SELECT ON Object::Regions FROM Sales

E. REVOKE SELECT ON Object::Regions FROM UserA

F. DENY SELECT ON Schema::Customers FROM Sales

G. DENY SELECT ON Schema::Customers FROM UserA

H. EXEC sp_droprolemember 'Sales', 'UserA'

I. REVOKE SELECT ON Object::Regions FROM Sales

J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: F

Explanation:

References:

http://msdn.microsoft.com/en-us/library/ms188369.aspx http://msdn.microsoft.com/en-us/library/ms187750.aspx http://msdn.microsoft.com/en-us/library/ff848791.aspx

Q7. You manage a Microsoft SQL Server environment. You plan to encrypt data when you create backups.

You need to configure the encryption options for backups.

What should you configure?

A. a certificate

B. an MD5 hash

C. a DES key

D. an AES 256-bit key

Answer: D

Explanation:

To encrypt during backup, you must specify an encryption algorithm, and an encryptor to secure the encryption key. The following are the supported encryption options: Encryption Algorithm: The supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES

Encryptor: A certificate or asymmetric Key

References:https://docs.microsoft.com/en-us/sql/relational-databases/backup- restore/backup-encryption

Q8. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

A company has a server that runs Microsoft SQL Server 2021 Web edition. The server has a default instance that hosts a database named DB1.

You need to ensure that you can perform auditing at the database level for DB1.

Solution: You migrate DB1 to a named instance on a server that runs Microsoft SQL Server 2021 Enterprise edition.

Does the solution meet the goal?

A. Yes

B. No

Answer: A

Explanation:

All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2021 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

References:https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

Q9. You administer a Microsoft SQL Server 2012 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.

The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours.

Users modify more than 30 percent of the data in the database during the week. Backups are performed as shown in the following schedule:

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.

The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.

When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.

You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time. What should you do on Saturday?

A. Perform a differential backup at 08:59 hours.

B. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.

C. Create a database snapshot at 08:59 hours.

D. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.

E. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.

F. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.

Answer: C

New questions:

61. HOTSPOT

You manage a Microsoft-SQL Server database named sales Orders.

You need to verify the integrity of the database and attempt to repair any errors that are found. Repair must not cause any data to be lost in the database.

How should you complete the DBCC command? To answer, select the appropriate options in the answer area.

Q10. You administer a Microsoft SQL Server 2012 instance.

After a routine shutdown, the drive that contains tempdb fails.

You need to be able to start the SQL Server. What should you do?

A. Modify tempdb location in startup parameters.

B. Start SQL Server in minimal configuration mode.

C. Start SQL Server in single-user mode.

D. Configure SQL Server to bypass Windows application logging.

Answer: B

Explanation:

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

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