Exam 70-765: Provisioning SQL Databases Practice Test
Exam 70-765 focuses on provisioning SQL Server databases and configuring the database objects, files, access controls, and operational settings they need. Practice material addresses decisions such as choosing recovery behavior, setting file locations and growth options, creating schemas and stored procedures, and granting permissions. These concepts are useful when turning application requirements into a database configuration that administrators can deploy and maintain.
Exam overview
About the Exam 70-765: Provisioning SQL Databases Exam
Database provisioning requires more than creating a database: administrators must align storage, recovery, access, and concurrency settings with how an application will use the data. The practice bank includes scenarios involving SIMPLE recovery and log backups, file placement on a specified volume, automatic growth settings, and connection limits. It also tests object-level choices, including creating a schema for a dedicated namespace, using stored procedures for reusable server-side operations, and granting execute permission without exposing underlying tables. Other scenarios concern read access through built-in database roles, point-in-time read-only testing with a database snapshot, and running DBCC CHECKDB to assess structural consistency. Work through each case by identifying the requirement first, then distinguishing the SQL Server feature or permission that directly satisfies it from broader alternatives.
Sample Practice Questions
Review a selection from this exam's published US Practice Tests practice bank.
A database deployment must allow administrators to create objects in a dedicated namespace rather than placing them in dbo. What should the deployment create and use?
Show answer
Correct answer: AA schema is a database-level namespace that can own objects such as tables, views, and procedures. Creating and explicitly using a schema helps organize objects and manage permissions.
A database is configured to use the SIMPLE recovery model. Which statement best describes its log-backup and recovery implications?
Show answer
Correct answer: AUnder the SIMPLE recovery model, transaction log backups are not available, and log space is typically reused after checkpoints. This model generally does not provide point-in-time restore capability.
An application needs a reusable, server-side routine that accepts parameters and performs several SQL statements in the database. Which database object is best suited to this purpose?
Show answer
Correct answer: AA stored procedure is a named database routine that can accept parameters and execute one or more statements. It can encapsulate operations for reuse and can be granted permissions as a database object.
An administrator needs to give a database user read access to all current and future user tables and views in one database. Which built-in database role is the most direct choice?
Show answer
Correct answer: AThe db_datareader fixed database role grants read access to user tables and views in the database. It does not grant write access or database-wide schema modification rights.
A database must provide consistent, read-only access to a point-in-time view of its data for testing, without creating a full independent copy of all database pages immediately. Which feature is designed for this purpose?
Show answer
Correct answer: AA database snapshot presents a read-only, transactionally consistent view of the source database at the time the snapshot was created. It initially relies on sparse files and stores changed pages as the source database changes.
A provisioning process needs to create a database user that can execute a stored procedure but cannot directly read the underlying tables. Which permission is the narrowest direct grant for this need?
Show answer
Correct answer: AGranting EXECUTE on the specific procedure provides the required operation without granting broad table-reading or database-administration permissions. The procedure's execution context and ownership chaining should also be designed appropriately.
Before a planned SQL Server upgrade, an administrator wants to check a database for structural consistency problems. Which operation is designed for this purpose?
Show answer
Correct answer: ADBCC CHECKDB checks the logical and physical integrity of database objects and allocation structures. It is a consistency-checking operation, unlike configuration changes, backup-header inspection, or ownership changes.
How to use this practice test
The free US Practice Tests practice bank contains 50 questions for this exam. Each session presents up to 20 questions selected at random and displays results after you finish. Use the scenarios to check whether you can distinguish database configuration choices, permission scope, and SQL Server features; revisit any missed concept in a test environment or your technical notes.
Preparation tips
- Practice writing CREATE DATABASE statements that specify logical file names, physical paths, initial sizes, and growth increments; verify the resulting file configuration in SQL Server.
- Compare SIMPLE, FULL, and BULK_LOGGED recovery behavior, especially which backup types support log truncation and point-in-time recovery.
- Use a test database to create schemas, users, stored procedures, and narrowly scoped EXECUTE grants; confirm the user can run the procedure without selecting its base tables.
- Review database snapshots, DBCC CHECKDB, and database connection limits, and identify the distinct operational problem each feature addresses.
- For automatic file growth, calculate likely growth events from workload size and compare fixed-size increments with percentage growth to avoid excessive small expansions.