The Complete SQL Server 2019 Database Administration Course

Master restoring databases to their base structure using restore scripts, and pay attention to the correct file paths and drive locations to ensure the restore succeeds.

Explore how a clustered index builds a single tree with a root, intermediate nodes, and leaves that hold data, and why only one cluster index per table is possible.

Explore the definition and creation of non-clustered indexes, compare them with cluster indexes, and observe how index type affects data organization and page structure in SQL Server 2019.

Explore how a primary key forms a clustered index and how a non clustered index complements it, with key values replicated on every data page and fragmentation implications.

The demo shows that a table scan may read about 4,000 pages, but an appropriate cluster index reduces reads to about 210 pages, changing the execution plan.

This demo shows the table seek, illustrating how index seeks with execution plans speed data access, compare cluster index and heap table scenarios, and discuss when not to force indexes.

Discover how covering indexes speed queries by avoiding key lookups, compared with clustered and nonclustered indexes, using execution plans and SSN filters.

Explore how unique indexes and unique constraints affect query plans, and learn to avoid duplicate indexes while examining index usage in SQL Server 2019 database administration.

Consolidate redundant indexes to reduce storage and improve query performance in SQL Server 2019, using last name, first name, and middle initial coverage and execution plan analysis.

Demonstrates deactivating an index to improve insertion times and reveals which index the query uses. Test scenarios and compare activated versus deactivated indexes, while considering recovery mode and log size.

Demonstrates how filtered indexes influence query execution plans, showing when an index is used or ignored, how to force the index, and how covering indexes affect results.

Explore how clustering on a NEWID column causes fragmentation and performance degradation, and examine index statistics and page density to understand the impact.

Explore how forcing an index, choosing between clustered and nonclustered indexes, and creating a covering index can improve query performance by reducing reads and favoring seeks over scans.

This demo shows how to create an indexed view, first building a clustered index, then a nonclustered index, to materialize a view and accelerate large, aggregated queries in data warehouses.

Explore how the fill factor (fear factor) affects index pages and fragmentation, showing how 70 percent fill leaves free space to prevent splits, while 100 percent causes splits.

Explore two common approaches to SQL Server 2019 corruption and why most issues stem from physical disk sectors rather than memory tests, guiding efficient restoration.

This lecture explains that corruption in SQL Server 2019 databases never disappears and remains persistent despite nightly checks, and highlights vigilant monitoring and implications of index creation on detecting corruption.

Demonstrate how Windows reports data read errors, identifies suspect pages, and guides correcting a page such as page 225 within a database.

Demonstrates torn page detection and page checksum modes for validating pages against their headers, exposing anomalies and guiding when to switch default settings via the graphical interface.

Demonstrates using DBCC IND and DBCC PAGE to inspect pages and indexes in SQL Server, highlighting cluster, heap, and nonclustered indexes, page depth, and fragmentation.

Learn to create a job to check data consistency, schedule weekly checks on Sunday, and review results for potential corruption to ensure data integrity.

Learn how checkdb validates the physical integrity of all database objects, checks index validity and page data consistency, and assesses catalog integrity, with notes on usage and performance considerations.

Explore SQL Server 2019 checkdb options, compare physical only checks to full checks, assess data consistency messages, and understand space estimation and snapshot usage for efficient maintenance.

Analyze a worldwide survey on the frequency of checkdb on production databases. Show that many run it without action and many lack awareness of data integrity checks.

Watch a hands-on demo of backup with checksum, detecting a corrupted page (147) and validating data integrity with checks, while noting performance impact and safety considerations.

Demonstrates how to approach repairing corrupted sql server databases, weighing repair versus restoration, and the importance of backups before assessing data loss and integrity checks.

Demonstrate repairing a corrupted cluster index by deactivating and rebuilding it, addressing fragmentation, reassigning pages, and choosing repair or rebuild options to restore index integrity.

Demonstrates restoring a corrupted SQL Server 2019 database from backups, preserving the active log portion and validating integrity during the restore process.

Demonstrates using emergency mode to repair a damaged database, including switching to single-user mode, running repair data, and evaluating when emergency mode fully resolves corruption.

Demonstrates repairing a corrupted SQL Server 2019 database by attaching a damaged file, using offline and emergency modes, and swapping in a copy.

Understand that corruption can occur at any time, affecting disks, snapshots, and keys. Use daily backups and tools to detect, resolve corruption, and regularly check and protect the system database.

Watch a practical demo of backup and restore logs in SQL Server 2019, including simulating a crash, restoring the database, and validating recovery steps.

Learn how differential backups use a full backup to speed restores, save time, and optimize production backup strategy.

Discover how the copy-only backup option preserves the backup chain, demonstrate full, differential, and copy-only backups, and explain how to restore safely in production.

Explore the different recovery models in SQL Server, including full and simple, and learn how transaction logs, backups, and checkpoints affect point-in-time restores and disk space management.

Demonstrates how a tail log backup complements full backups and log backups to recover and restore a SQL Server 2019 database, via a practical demo.

During this demo, learn how backup with checksum detects page corruption and preserves data integrity in a SQL Server 2019 environment.

Watch a live demo of restoring a single page in a large multi-terabyte database, illustrating a powerful restoration technique that saves hours.

Demonstrates a point-in-time restore using stopat, recovering data from a 10 p.m. backup to a specific moment and outlining the steps to restore production data accurately.

Demonstrates how to consult backup and restore metadata in SQL Server 2019, using system views and scripts to view backup history, media, location, size, and verify validity.

Demonstrates backing up to several files and multiple destination sites to distribute workload and improve backup performance for SQL Server databases.

Demonstrates configuring backup options in SQL Server 2019, focusing on buffercount and maxtransfersize. Explains how memory usage affects backup duration and guides testing different values to optimize performance.

Emphasize backing up the system database, especially the master database, to prevent disaster when corruption or failures occur; the lecture demonstrates restoring system databases and the overall system.

Restore the master system database from a backup. Restart the SQL Server, reconnect, and validate access after restoration.

Observe a step-by-step demo to restore all system databases, set up and create a test database, archive and reattach system databases, and verify the restored instance is accessible.

Explore essential backup and restore strategies, define recovery point objective and recovery time objective, and emphasize regular backups to minimize data loss and ensure quick recovery.

Explore configuring and modifying databases, compare options for database creation, identify common pitfalls, and learn how to activate practical options during demonstrations.

Explore how to define data and log files when creating a SQL Server 2019 database, including file locations, sizes, and multi-file configuration for performance.

Define and create a filegroup in SQL Server 2019 to distribute data across multiple disks, configure primary and secondary filegroups, and improve database performance through strategic placement.

Activate the contained database option to isolate a user database from the master, then demonstrate containment, partial type, and single-database access via connection properties.

Explore how to assess and change the database compatibility mode to match applications and restore scenarios, ensuring appropriate performance and compatibility across versions.

Master single-user and restricted-user modes in the SQL Server 2019 database administration course, switching databases to single-user, restricted, and read-only states. Learn to manage security, connections, and access during maintenance.

Learn to detach and reattach databases using the attach syntax, verify attachments, and manage data and log file associations for smooth database creation by attaching files.

Explore target recovery time and how checkpoints affect SQL Server 2019 databases, including default zero intervals, online vs base database behavior, and adjusting checkpoint frequency to minutes.

Discover the trustworthy option for a database, how to activate and verify it, and the security implications of privileged access and runtime context.

Learn how to change collation in SQL Server, selecting case sensitivity and accent sensitivity for your data, and how to apply and verify settings when creating databases.

Explore how set ansi_null and set quote_identifier influence query behavior through practical demonstrations, including creating tables, triggers, and observing value returns with conditional selections.

Demonstrate how to enable change tracking on a database for a 24-hour retention period, track changes in a category table, and compare it as an alternative to change data capture.

Demonstrate why enabling db chaining across databases risks security, explain the cross-database property and trust, and show turning it off by default per Microsoft guidance.

Demonstrates autogrow settings in SQL Server by creating databases, inserting 11 million rows, and monitoring growth performance with no primary key or index, highlighting timings and configuration tradeoffs.

Learn to clone a database in SQL Server 2019 with a simple one-command syntax, using source and target names, and note you cannot clone a master database.

Explore read committed mode in SQL Server by observing how locks prevent reading uncommitted data, and how commits and rollbacks reveal or revert changes.

Demonstrates read uncommitted isolation and dirty reads in SQL Server 2019. Explains why this least restrictive mode can cause data anomalies during transactions.

Explore the repeatable read isolation mode in SQL Server 2019 and see how it prevents non-reproducible data during concurrent reads and updates.

Explore snapshot isolation mode in SQL Server 2019, using versioned data to avoid locks and ghost reads in transactions, and show its impact on tempdb and storage.

Learn how the XABORT command interrupts a current transaction, triggers an execution error, and performs a full rollback, and explore on conflict behavior with foreign keys in transaction management.

The demo shows two concurrent updates on two separate tables causing a deadlock, and the server resolves it by terminating the less costly transaction.

Explore the SQL Server 2019 uplock command with a hands-on demo of update, commit, and select operations. See how insertion behaves across transactions.