UUS Practice Tests⌕ Search
IT Certifications · 70-764

Exam 70-764: Administering a SQL Database Infrastructure Practice Test

Exam 70-764 covers administration of SQL Server database infrastructure, including security, configuration, data movement, recovery, and availability. The practice material reflects operational decisions such as separating object-definition permissions from row access, reviewing instance settings, exporting data, restoring backups, and supporting read-only access on a secondary replica.

Exam overview

Exam providerMicrosoft
Exam code70-764
CategoryIT Certifications

About the Exam 70-764: Administering a SQL Database Infrastructure Exam

Database administration requires balancing access, recoverability, and service availability. Permission design distinguishes server logins from database users and separates rights to change schema from rights to read or modify table data. Instance configuration review involves inspecting settings and understanding their effect on database operations. Data transfer work includes choosing bulk import or export utilities for schema and data movement. Recovery decisions depend on backup type and restore order: a full backup establishes the base, while applicable differential backups restore later changes. Point-in-time access can support recovery of deleted rows without replacing the active database. High-availability planning includes selecting configurations that allow readable secondary replicas, while routine monitoring uses file-level usage information to track allocated space and growth limits.

Sample Practice Questions

Review a selection from this exam's published US Practice Tests practice bank.

Question 1

A database administrator wants to reduce the risk that a user can alter a table's definition while still allowing that user to read and modify rows in it. Which permission separation is most appropriate?

  1. AGrant SELECT and INSERT, UPDATE, and DELETE on the table, but do not grant ALTER
  2. BGrant CONTROL on the database
  3. CAdd the user to the db_owner role
  4. DGrant ALTER on the schema
Show answerCorrect answer: A

Data manipulation permissions such as SELECT, INSERT, UPDATE, and DELETE can be granted without granting ALTER, which permits changes to object definitions. Broad permissions such as CONTROL or membership in db_owner would grant substantially more authority than required.

Question 2

A database administrator wants to identify which SQL Server configuration values differ from their defaults and review the current settings. Which system stored procedure is intended to report configuration options and their values?

  1. Asp_configure
  2. Bsp_who
  3. Csp_helpdb
  4. Dsp_spaceused
Show answerCorrect answer: A

sp_configure displays SQL Server configuration options and their current values, and can be used to change supported settings. The other procedures provide session, database, or space information rather than general instance configuration values.

Question 3

A SQL Server administrator wants to export a table's data and schema in a portable text format for transfer to another system. Which utility is designed for bulk data import and export?

  1. Abcp
  2. Bsqlcmd
  3. CProfiler
  4. DSQL Server Configuration Manager
Show answerCorrect answer: A

The bcp utility performs bulk copying of data between a SQL Server table or view and a data file. It supports export and import, including character-format data suitable for transfer.

Question 4

A database must be available for read-only queries on a secondary replica while changes are synchronized from the primary. Which SQL Server high-availability technology supports this design?

  1. AAlways On availability groups
  2. BDatabase mirroring in witness-only mode
  3. CLog shipping with standby disabled
  4. DReplication snapshot agent alone
Show answerCorrect answer: A

Availability groups can provide secondary replicas that support read-only connections, subject to configuration and edition/version capabilities. The other choices do not provide this same readable-secondary availability-group design.

Question 5

A SQL Server database is accidentally dropped, and the administrator needs to recover it using a full backup followed by differential backups. What is the correct restore sequence?

  1. ARestore the most recent differential backup first, then restore the full backup
  2. BRestore the full backup, then the most recent applicable differential backup
  3. CRestore only the most recent differential backup
  4. DRestore the full backup with recovery, then apply the differential backup
Show answerCorrect answer: B

A differential backup contains changes made since the most recent full backup. Restore the full backup first, keeping the database in a restoring state, then apply the selected differential backup. The final restore can bring the database online.

Question 6

An administrator must allow a SQL Server login to connect to an instance but prevent it from accessing a particular database. Which database-level action directly prevents access to that database for the mapped user?

  1. AAdd the user to the db_datareader role
  2. BGrant the user CONNECT permission
  3. CDeny CONNECT permission to the user
  4. DGrant the user VIEW DEFINITION permission
Show answerCorrect answer: C

Denying CONNECT at the database scope prevents the user from establishing a connection to that database. Database access is distinct from the login's ability to connect to the SQL Server instance.

Question 7

A database must allow administrators to recover accidentally deleted rows from a previous point without restoring the entire database over the current one. Which feature creates a transactionally consistent, read-only view of a database at a point in time?

  1. ADatabase snapshot
  2. BFull-text index
  3. CPartition function
  4. DService Broker queue
Show answerCorrect answer: A

A database snapshot provides a read-only, point-in-time view of its source database, which can be used to inspect earlier data while the source remains online. It is not a substitute for backups and depends on the source database and snapshot files remaining available.

How to use this practice test

Access is free. A practice session presents up to 20 random questions from the 70-764 bank and displays results when the session ends. Use missed answers to identify whether you need to revisit permissions, backup and restore sequencing, availability design, configuration inspection, or file-space monitoring.

Preparation tips

  • Practice distinguishing server-level login access from database-level user access, and compare table data permissions with permissions that alter object definitions.
  • Write out restore sequences for a full backup followed by differential backups; identify the restore state needed before applying each subsequent backup.
  • Compare readable secondary replica scenarios with other availability approaches, focusing on where read workloads run while changes are synchronized.
  • Use SQL Server system procedures and views to inspect configuration values and database-file allocation or usage, then interpret what the output reveals.
  • Work through a bulk data-transfer scenario and decide when a SQL Server import/export utility is appropriate for moving table schema and data.
Official sourceOfficial exam page ↗