Download Provisioning SQL Databases.70-765.PassIt4Sure.2018-06-17.107q.tqb

The full recovery model requires log backups. No work is lost due to a lost or damaged data file. Can recover to a specific point in time, assuming that your backups are complete up to that point in time. Incorrect Answers:F: The bulk logged recovery model can recover to the end of any backup. Point-in-time recovery is not supported.M: The simple recovery model can recover only to the end of a backup.References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server

Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up. References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/transaction-log-backups-sql-server

References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server

The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required. The advantage of using log backups is that they let you restore a database to any point of time that is contained within a log backup (point-in-time recovery). You can use a series of log backups to roll a database forward to any point in time that is contained in one of the log backups. Be aware that to minimize your restore time, you can supplement each full backup with a series of differential backups of the same data. References: https://technet.microsoft.com/en-us/library/ms190217(v=sql.105).aspx

REORGANIZE specifies to reorganize the index leaf level. The REORGANIZE operation is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

In a file restore, the goal is to restore one or more damaged files without restoring the whole database. References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/file-restores-simple-recovery-model

The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server. Incorrect Answers:A: sys.dm_db_index_usage_stats returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.B: sys.dm_db_missing_index_details returns detailed information about missing indexes, excluding spatial indexes.D: sys.dm_db_missing_index_columns returns information about database table columns that are missing an index, excluding spatial indexes.References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/database-engine-tuning-advisor

The SQL Server Audit feature enables you to audit server-level and database-level groups of events and individual events. Audits can have the following categories of actions: Server-level. These actions include server operations, such as management changes, such as in this question, and logon and logoff operations.  Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.  Audit-level. These actions include actions in the auditing process. References: http://technet.microsoft.com/en-us/library/cc280663(v=sql.105).aspx

SQL Server has alerts that get more important based on the severity of the alert. Anything of severity 16 or below tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. While those errors are typically important, they don't refer to anything with regards to overall health of the SQL Server. Alerts 17 through 25 do. Those are the ones your health checks are probably firing on.   References: https://www.mssqltips.com/sqlservertip/3384/configuring-critical-sql-server-alerts/

Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. References: http://msdn.microsoft.com/en-us/library/ms188246.aspx