70-764 Administering a SQL Database Infrastructure Exam

Which of the following server scoped permissions grants SELECT permissions on server level dynamic management objects?

Options are :

  • ALTER SERVER STATE
  • ALTER ANY LOGIN
  • VIEW SERVER STATE (Correct)
  • ALTER TRACE

Answer : VIEW CT * FROM sys.user_token; (Correct)

  • SELECT * FROM sys.loginuser_token;
  • SELECT * FROM sys.userlogin_token;
  • SELECT * FROM sys.login_token;
  • Answer : SELECT * FROM sys.user_token;

    Explanation SELECT * FROM sys.user_token; statement identifies and authorizes user tokens.

    A company named XYZ has a SQL Server environment in Azure. The databases are stored directly in Azure blob storage.

    You have to ensure that you can restore database to specific point in time between backups while minimizing the number of Azure storage containers required.

    Which of the following option should you use?

    Options are :

    • backup encryption
    • SQL Server backup to URL
    • SQL Server managed backup to Azure (Correct)
    • backup compression

    Answer : SQL Server managed backup to Azure

    Explanation You should use SQL Server managed backup to Azure because it supports point in time restore for the retention time period specified.

    Which of the following command checks the consistency of the system tables with a database?

    Options are :

    • DBCC CHECKTABLE
    • DBCC CHECKALLOC
    • DBCC CHECKCATALOG (Correct)
    • DBCC CHECKDB

    Answer : DBCC CHECKCATALOG

    Explanation DBCC CHECKCATALOG: It checks the consistency of the system tables with a database. DBCC CHECKALLOC: It checks the consistency of disk space allocation structures with a database. DBCC CHECKDB: It checks the logical and physical integrity of all the objects of a database. DBCC CHECKTABLE: It checks the allocation and structural integrity a table.

    Can you use the stop at mark functionally in SSMS?

    Options are :

    • Yes
    • No (Correct)

    Answer : No

    Explanation No, you cannot use the stop at mark functionally in SSMS. It is only available by using the T-SQL statement.

    True or False: You cannot start the instance of SQL Server if model database is corrupt?

    Options are :

    • False
    • True (Correct)

    Answer : True

    Explanation Yes, you cannot start the instance of SQL Server if model database is corrupt. The model database is the template for all databases that are created on the instance of SQL Server.

    You have a database that includes a table named Applicant.

    You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the column.

    Solution: you the following query:

    Use CustomerDb

    GO

    Update Statistics Person.Applicant(Skills)

    RESAMPLE, NORECOMPUTE

    GO

    Does this solution meet the goal?

    Options are :

    • Yes
    • No (Correct)

    Answer : No

    Explanation NORECOMPUTE: Disable the automatic statistics update option, AUTO_UPDATE_STATISTICS, for the specified statistics. If this option is specified, the query optimizer completes this statistics update and disables future updates. To re-enable the AUTO_UPDATE_STATISTICS option behavior, run UPDATE STATISTICS again without the NORECOMPUTE option or run sp_autostats.

    Which of the following statement is true?

    Options are :

    • Extensible Key Management(EKM) is not available for production use in SQL Server Enterprise edition.
    • Extensible Key Management(EKM) is only available for production use in SQL Server Enterprise edition. (Correct)
    • TDE is not available in Azure SQL database.

    Answer : Extensible Key Management(EKM) is only available for production use in SQL Server Enterprise edition.

    Explanation Extensible Key Management(EKM) is only available for production use in SQL Server Enterprise edition. TDE is available in the Azure SQL database.

    You are the administer of Microsoft SQL Server 2016 database.

    You want to make a full backup of the database to a file on disk.

    In doing backup, you need display a message indicating the progress of the backup operation.

    Which of the following backup option should you use?

    Options are :

    • SKIP
    • SERVER CERTIFICATE
    • STATS (Correct)
    • STOPAT

    Answer : STATS

    Explanation You should use the STATS backup option; it controls at what percentage intervals the database engine should display a message indicating the progress of the backup operation.

    Which of the following System Database is recreated every time when an instance of SQL Server starts?

    Options are :

    • master
    • tempdb (Correct)
    • model
    • msdb

    Answer : tempdb

    Explanation The tempdb System Database is recreated every time when an instance of SQL server starts. There is no need to back up or restore the tempdb database.

    You have a database named Demo that contains two tables.

    You have to encrypt one column in each table using the Always Encrypted feature.

    The solution must support groupings on the encrypted columns.

    Which action should you perform?

    Solution: You encrypt both columns by using randomized encryption.

    Does this solution meet the goal?

    Options are :

    • Yes
    • No (Correct)

    Answer : No

    Explanation No, the solution does not meet the goal. Randomized encryption: It prevents searching, joining, grouping and indexing on encrypted columns. It generates different ciphertext for the same plaintext.

    You are using Microsoft SQL Server.

    You have to save the data that has been changed since the last full backup.

    Which of the following backup should you use?

    Options are :

    • Differential Backups (Correct)
    • Partial Backups
    • Transaction Log Backups
    • Full Backups

    Answer : Differential Backups

    Explanation Differential Backup: It is used to save the data that has been changed since the last full backup.

    True or False: SQL Server supports the creation of copy only backups.

    Options are :

    • False
    • True (Correct)

    Answer : True

    Explanation The given statement is true. SQL Server supports the creation of copy only backups. Copy-only backups: It can be used to create a copy of the backup to take offsite to a safe location.

    Which of the following server action group includes actions that create, drop or alter a database?

    Options are :

    • BACKUP_RESTORE_GROUP
    • SUCCESSFUL_LOGIN_GROUP
    • DATABASE_CHANGE_GROUP (Correct)
    • SERVER_OPERATION_GROUP

    Answer : DATABASE_CHANGE_GROUP

    Explanation DATABASE_CHANGE_GROUP: It includes actions that create, drop or alter a database. BACKUP_RESTORE_GROUP: It includes all backup and restore actions. SERVER_OPERATION_GROUP: It includes actions that alter server settings. SUCCESSFUL_LOGIN_GROUP: It includes details of successful logins.

    In Mirrored Backup, the backup data is written to each backup device concurrently.

    Which of the following Edition of SQL Server support Mirrored Backup?

    Options are :

    • Express Edition
    • Developer Edition
    • Enterprise Edition (Correct)
    • Standard Edition

    Answer : Enterprise Edition

    Explanation Only the Enterprise Edition of SQL Server support Mirrored Backup.

    In which of the following System Database you cannot perform Backup?

    Options are :

    • msdb
    • model
    • tempdb (Correct)
    • master

    Answer : tempdb

    Explanation Yes, you cannot perform backup of tempdb System Database. tempdb database is created during instance startup.

    You use STOPAT option with RESTORE statement for point-in-time recovery.

    TRUE or False: The STOPAT option specify a recovery point that is based on a datetime value.

    Options are :

    • False
    • True (Correct)

    Answer : True

    Explanation The STOPAT option specify a recovery point that is based on a datetime value.

    Comment / Suggestion Section
    Point our Mistakes and Post Your Suggestions