70-764 Free Practice Test

- (Exam Topic 4)
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.
Start of repeated scenario.
You are a database administrator for a company that has on-premises Microsoft SQL Server environment. There are two domains in separate forests. There are no trust relationships between the domains. The environment hosts several customer databases, and each customer uses a dedicated instance running SQL Server 2016 Standard edition. The customer environments are shown in the following table.
< ><>>>>< >
Solution:
Scenario:
<>>>>>>>>>>-- >>'>-- >>>-- >-- >>>>>>

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 2)
You administer a Microsoft SQL Server 2016 database named Contoso that contains a single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers. Which Transact-SQL statement should you use?

1. A. EXEC sp_addrolemember\'db_procexecutor\', \'BillingUsers\'
2. B. CREATE ROLE proc_caller GRANT EXECUTE ON ALL PROCEDURES TO proc_caller ALTER MEMBER BillingUsers ADD TO ROLE proc_caller
3. C. GRANT EXECUTE ON Schema::dbo TO BillingUsers
4. D. GRANT EXECUTE ON Contoso::dbo TOBillingUsers

Correct Answer: D

- (Exam Topic 1)
You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: TableA and TableB.
TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millions of rows in each partition. Most changes to the data in TableA affect recently added data. The UPDATE STATISTICS for TableA takes longer to complete than the allotted maintenance window.
Thousands of operations are performed against TableB each minute. You observe a large number of Auto Update Statistics events for TableB.
You need to address the performance issues with each table.
In the table below, identify the action that will resolve the issues for each table. NOTE: Make only one selection in each column.

Solution:
Table A: Auto_update statistics off
Table A does not change much. There is no need to update the statistics on this table. Table B: SET AUTO_UPDATE_STATISTICS_ASYNC ON
You can set the database to update statistics asynchronously: ALTER DATABASE YourDBName
SET AUTO_UPDATE_STATISTICS_ASYNC ON
If you enable this option then the Query Optimizer will run the query first and update the outdated statistics afterwards. When you set this option to OFF, the Query Optimizer will update the outdated statistics before compiling the query. This option can be useful in OLTP environments
References:
https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 4)
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.
You have a Microsoft Azure SQL database that has Blob Auditing configured. You need to review the audit logs.
Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.
<>

1. A. Yes
2. B. No

Correct Answer: B
The fn_get_audit_file, not dm_db_audit_file, the returns information from an audit file created by a server audit in SQL Server.
This example reads from a file that is named ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-14/10_45_22_173_1.xel:
SELECT * FROM sys.fn_get_audit_file (\'https://mystorage.blob.core.windows.net/sqldbauditlogs/ShiraServer/MayaDB/SqlDbAuditing_Audit/2017-07-
Note: Blob auditing logs are saved as a collection of blob files within a container named sqldbauditlogs. References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

- (Exam Topic 4)
You have a database named DB1. You complete a full backup on January1, 2018 to a backup set named DB1_Backup. You create a differential backup January 2, 2018 to the same backup set. You perform transaction log backups each day at 1:00 PM.
DB1 experiences a catastrophic failure.
You need to restore the database to January 3, 2018 at 11:00 AM.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segment from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.

Solution:
This example restores a database, differential database, and transaction log backup of the MyAdvWorks
database. Step 1:
-- Assume the database is lost at this point. Now restore the full
-- database. Specify the original full database backup and NORECOVERY.
-- NORECOVERY allows subsequent restore operations to proceed. RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1 WITH NORECOVERY; GO
Step 2:
-- Now restore the differential database backup, the second backup on
-- the MyAdvWorks_1 backup device. RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1 WITH FILE = 2, NORECOVERY;
Step 3:
-- Now restore each transaction log backup created after
-- the differential database backup. RESTORE LOG MyAdvWorks FROM MyAdvWorks_log1 WITH NORECOVERY;
GO
RESTORE LOG MyAdvWorks FROM MyAdvWorks_log2 WITH RECOVERY;
GO
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-differential-database-backup-

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A