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.
You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.
< ><>>< ><>>< ><>>< > >< > >< > >< > >< > >< > >>< > >< > >< > >>>You must restore the impacted page from SalesDb1Full_1.bak. A single backup set named SalesDb1Log.bak was created since the latest full backup operation.
You need to restore the impacted page.
Which four 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.

Solution:
Step 1:
Restore page
Start a page restore with a full database, file, or filegroup backup that contains the page. In the RESTORE DATABASE statement, use the PAGE clause to list the page IDs of all of the pages to be restored.
Step 2:
Restore log file with norecovery. Use the first file (FILE = 1). Step 3:
Backup the tail-end of the log.
Create a new log backup of the database that includes the final LSN of the restored pages, that is, the point at which the last restored page is taken offline.
Step 4:
Restore database with recovery. Use second file (FILE = 2).
Restore the new log backup. After this new log backup is applied, the page restore is completed and the pages are now usable.
Example:
The following example restores four damaged pages of file B with NORECOVERY. Next, two log backups are applied with NORECOVERY, followed with the tail-log backup, which is restored with RECOVERY. This example performs an online restore. In the example, the file ID of file B is 1, and the page IDs of the damaged pages are 57, 202, 916, and 1016.
RESTORE DATABASE PAGE='1:57, 1:202, 1:916, 1:1016'
FROM WITH NORECOVERY;
RESTORE LOG FROM WITH NORECOVERY;
RESTORE LOG FROM WITH NORECOVERY;
BACKUP LOG TO ;
RESTORE LOG FROM WITH RECOVERY;
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 1)
You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA.
Currently, Server01l/HA is the primary replica.
You need to ensure that the following requirements are met:
Backup operations occur on Server02/HA.
If Server02/HA is unavailable, backup operations occur on Server03/HA.
Backup operations do not occur on Server01/HA.
How should you configure HaContoso?

1. A. Set the backup preference of HaContoso to Prefer Secondar
2. B. Set the backup priority of Server02/HA to20. Set the backup priority of Server03/HA to 10.
3. C. Set the backup preference of HaContoso to Secondary onl
4. D. Set the backup priority of Server02/HA to20. Set the backup priority of Server03/HA to 10.
5. E. Set the backup preference of HaContoso to Secondary onl
6. F. Set the backup priority of Server02/HA to10. Set the backup priority of Server03/HA to 20.
7. G. set the exclude replica of Server01/HA to tru
8. H. Set the backup priority of Server02/HA to 10. Set the backup priority of Server03/HA to 20.

Correct Answer: B
Secondary only: Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.
Backup Priority (Lowest=1, Highest=100)
Specifies your priority for performing backups on this replica relative to the other replicas in the same availability group. The value is an integer in the range of 0..100. 1 indicates the lowest priority, and 100 indicates the highest priority. If Backup Priority = 1, the availability replica would be chosen for performing backups only if no higher priority availability replicas are currently available.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availab

- (Exam Topic 1)
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.
<>

1. A. sp_updatestats
2. B. sp_lock
3. C. sys.dm_os_waiting_tasks
4. D. sys.dm_tran_active_snapshot_database_transactions
5. E. Activity Monitor

Correct Answer: B
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

- (Exam Topic 1)
You install Microsoft SQL Server 2016 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?

1. A. %programfiles%\\Microsoft SQLServer\\MSSQL11.MSSQLSERVER|MSSQL\\Log\\Errorlog
2. B. %programfiles%\\Microsoft SQL Server\\110\\setupBootstrap\\Log\\Summary.txt
3. C. %programfiles%\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\mastlog.idf
4. D. %programfiles%\\Microsoft SQLServer\\110\\Shared\\ErrorDmpr[XXXX] .mdmp

Correct Answer: A

- (Exam Topic 1)
You have configured Resource Governor with three resource pools.
You have assigned the first resource pool a minimum CPU and memory value of 20%. You have assigned the second resource pool a minimum CPU and memory value of 30%. You want to assign maximum CPU and memory values to the third resource pool.
What is the maximum CPU and memory value you can assign to this resource pool?

1. A. 30%
2. B. 50%
3. C. 70%
4. D. 100%

Correct Answer: B
The maximum resource value assigned to the third pool is 100%; the sum of the minimum resource values assigned to the other pools is 50%.