Testking offers free demo for 70-462 exam dumps exam. "Administering a SQL Database Infrastructure (beta)", also known as 70-462 exam preparation exam, is a Microsoft Certification. This set of posts, Passing the Microsoft 70-462 questions and answers exam, will help you answer those questions. The 70-462 exam preparation Questions & Answers covers all the knowledge points of the real exam. 100% real Microsoft 70-462 exam dumps exams and revised by experts!
Q1. 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
Q2. You have a database named DB1 that is configured to use the full recovery model. You have a full daily backup job that runs at 02:00. The job backs up data from DB1 to the file B:\DB1.bak.
You need to restore the DB1 database to the point in time of May 25, 2021 at 02:23 and ensure that the database is functional and starts to accept connections.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q3. DRAG DROP
You are configuring a new Microsoft SQL Server Always On Availability Group. You plan to configure a shared network location at \\DATA-CI1\SQL.
You need to create an availability group listener named AGL1 on port 1433.
In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Explanation:
Step 1: Launch the Failover Cluster Manager and..
To support theAlways On availability groups feature, ensure that every computer that is to participate in one or more availability groups meets requirements including:
* Ensure that each computer is a node in a WSFC (Windows Server Failover Clustering).
Step 2: Add andconfigure the replica and…
All the server instances that host availability replicas for an availability group must use the same SQL Server collation.
Step 3: Enable the SQL Server 2021 Always On Availability Group feature.
Enable the Always On availability groups feature on each server instance that will host an availability replica for any availability group. On a given computer, you can enable as many server instances for Always On availability groups as your SQL Server installation supports.
Step 4: Create the Always On Availability Group and..
Using Transact-SQL to create or configure an availability group listener
Step 5: Select the Full data synchronization method and… References:
Q4. 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
Q5. HOTSPOT
You manage a Microsoft SQL Server environment. A server fails and writes the following event to the application event log:
MSG_AUDIT_FORCED_SHUTDOWN
You configure the SQL Server startup parameters as shown in the following graphic:
Use the drop-down menus to select the answer choice that answers each question. NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Box 1: single-user
The startup option -m starts an instance of SQL Server in single-user mode.
Box 2: sysadmin
Starting SQL Server in single-user mode enables anymember of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.
References:https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options
Q6. 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
Q7. 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 commands. Each data load adds 3 GB of data to the database.
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.
On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version. You need to perform a full database backup that will be restored on the development server.
Which backup option should you use?
A. NORECOVERY
B. FULL
C. NO_CHECKSUM
D. CHECKSUM
E. Differential
F. 8ULK_LOGGED
G. STANDBY
H. RESTART
I. SKIP
J. Transaction log
K. DBO ONLY
L. COPY_ONLY
M. SIMPLE
N. CONTINUE AFTER ERROR
Answer: L
Explanation:
Verified answer as correct.
http://msdn.microsoft.com/en-us/library/ms191495.aspx
http://msdn.microsoft.com/en-us/library/ms186858.aspx
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.
You need to configure a Microsoft SQL Server instance to ensure that a user named Mail1 can send mail by using Database Mail.
Solution: You add the DatabaseMailUserRole to Mail1 in the tempdb database. Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database, not the tempdb database, in order to prevent anyone from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a member of DatabaseMailUserRole in order to send emails with the exception of sysadmin who has all privileges.
Note: Database Mail was first introduced as a new feature in SQLServer 2005 and replaces
the SQL Mail feature found in previous versions.
References:http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml
Q9. 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
Q10. 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 commands.
Each data load adds 3 GB of data to the database.
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 the backup size is as small as possible. Which backup should you perform every two hours?
A. BULK_LOGGED
B. NO_CHECKSUM
C. FULL
D. RESTART
E. CHECKSUM
F. STANDBY
G. DBO.ONLY
H. NORECOVERY
I. SIMPLE
J. Transaction log
K. Differential
L. CONTINUE_AFTER_ERROR
M. COPY_ONLY
N. SKIP
Answer: J
Explanation:
Verified answer as correct.
http://msdn.microsoft.com/en-us/library/ms186865.aspx
http://msdn.microsoft.com/en-us/library/ms191429.aspx
http://msdn.microsoft.com/en-us/library/ms179478.aspx
Q11. 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 than runs Microsoft SQL
Server 2021 Standard edition.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
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
Q12. 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
Q13. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off-loaded from the primary database to another server.
You also Need to be able to add indexes to the secondary database. Which configuration should you use?
A. • Two servers configured in different data centers
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• One server configured as an Active Secondary
B. • Two servers configured in the same data center
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• One server configured as an Active Secondary
C. • Two servers configured in the same data center
• A primary server configured to perform log-shipping every 10 minutes
• A backup server configured as a warm standby
D. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. • Two servers configured on the same subnet
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
F. • SQL Server that includes an application database configured to perform transactional replication
G. • SQL Server that includes an application database configured to perform snapshot replication
H. • Two servers configured in a Windows Failover Cluster in the same data center
• SQL Server configured as a clustered instance
Answer: F
Q14. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail.
You also need to minimize the amount of storage used by the database. Which configuration should you use?
A. • Two servers configured in different data centers
• One server configured as an Active Secondary
B. • SQL Server that includes an application database configured to perform transactional replication
C. • Two servers configured in the same data center
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• One server configured as an Active Secondary
D. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. • Two servers configured in the same data center
• A primary server configured to perform log-shipping every 10 minutes
• A backup server configured as a warm standby
F. • Two servers configured on the same subnet
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
G. • SQL Server that includes an application database configured to perform snapshot replication
H. • Two servers configured in a Windows Failover Cluster in the same data center
• SQL Server configured as a clustered instance
Answer: H
Q15. 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 UserA is disallowed to select from any of the tables in the Customers schema. Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Object::Regions FROM Sales
C. REVOKE SELECT ON Schema::Customers FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. REVOKE SELECT ON Object::Regions FROM UserA
G. DENY SELECT ON Schema::Customers FROM Sales
H. DENY SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemember 'Sales', 'UserA'
J. EXEC sp droprolemember 'Sales', 'UserA'
Answer: H
Explanation:
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
Q16. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to be notified immediately when fatal errors occur on Server01. What should you create?
A. an Alert
B. a Server Audit Specification
C. an Extended Event session
D. a Resource Pool
E. a Policy
F. a SQL Profiler Trace
G. a Database Audit Specification
Answer: A
Q17. 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
Q18. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.
What should you create?
A. an Extended Event session
B. a Policy
C. a Database Audit Specification
D. an Alert
E. a Server Audit Specification
F. a SQL Profiler Trace
G. a Resource Pool
Answer: F
Q19. 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 commands.
Each data load adds 3 GB of data to the database.
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 the minimum amount of data is lost.
Which recovery model should the database use?
A. FULL
B. DBO_ONLY
C. CONTINUE_AFTER_ERROR
D. CHECKSUM
E. NO_CHECKSUM
F. SIMPLE
G. Transaction log
H. SKIP
I. RESTART
J. COPY_ONLY
K. NORECOVERY
L. BULK_LOGGED
M. Differential
N. STANDBY
Answer: L
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms189275.aspx
Q20. You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of requests per hour.
Queries on DB1 are taking longer than normal to complete. You run the following Transact-SQL statement:
SELECT * FROM sys.database_query_store_options
You determine that the Query Store is in Read-Only mode.
You need to maximize the time that the Query Store is in Read-Write mode. Which Transact-SQL statement should you run?
A. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
B. ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)
C. ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
D. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)
Answer: C
Explanation:
Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.
By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.
Avoid keeping historical data that you do not plan to use. This will reduce changes to read- only status. The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio or the following script to configure time-based cleanup policy:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
References:https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
Q21. A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup strategy that creates backups for the filegroups.
DB1 experiences a failure. You must restore FG1 and then FG2.
You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After the restoration of FG2 completes, the database must be online.
What should you specify when you run the recovery command?
A. the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
B. the WITH RECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
C. the WITH RECOVERY clause for both FG1 and FG2
D. the WITH NORECOVERY clause for both FG1 and FG2
Answer: A
Q22. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to prevent users from disabling server audits in Server01. What should you create?
A. an Alert
B. a Resource Pool
C. an Extended Event session
D. a Database Audit Specification
E. a SQL Profiler Trace
F. a Server Audit Specification
G. a Policy
Answer: G
Q23. 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 and 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 the Regions table.
Which Transact-SQL statement should you use?
A. REVOKE SELECT ON Schema::Customers FROM UserA
B. REVOKE SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember 'Sales', 'UserA'
D. DENY SELECT ON Schema::Customers FROM Sales
E. EXEC sp_droprolemember 'Sales', 'UserA'
F. REVOKE SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema::Customers FROM UserA
J. DENY SELECT ON Object::Regions FROM Sales
Answer: J
Explanation:
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