70-462: SQL Server Database Administration (DBA)

Learn how to restore a sql server database from a backup by selecting the source, adding a file, configuring the destination, and reviewing point-in-time timelines.

Explore sql server management studio navigation, switch databases, and convert graphical user interface actions to t-sql code, including restore commands, tables, views, and stored procedures.

The video demonstrates backing up an AdventureWorks database, editing data via the GUI, and restoring the backup after an in-use error, illustrating why backups matter.

Explore recovery models in SQL Server, compare simple, full, and bulk-logged options, understand transaction log implications, and learn how to switch models via SSMS or ALTER DATABASE scripts.

Explore how full, differential, and transaction log backups work together to reduce data loss and system load, including bulk logged recoveries and hourly backup strategies.

Change the recovery model from simple to full in SQL Server Management Studio, then perform full and transaction log backups, observe differential backups, and note sizes and restoration implications.

Learn how to perform point in time recovery using a full backup, differential, and all transaction log backups, including tail log backups, in the full or bulk logged recovery models.

Discover how norecovery and recovery control database accessibility during restores, including tail-log backups and multi-step restores, and learn when to apply each to keep the source databases safe.

Identify the system databases in SQL Server and their backup roles. Back up master, model, and MSDB, note the resource database, and skip tempdb.

Explore backup strategies for SQL Server databases, including full, differential, and transaction log backups, test restores, checksums, and copy-only options for redundancy.

Verify backups before restoring and recover from corrupted media with continue after error or no recovery; run dbcc checkdb and repair with data loss if needed, then restore to multi-user.

Master the distinction between logins and users, create a Windows-authenticated login, map it to a specific database, and review the corresponding T-SQL script.

Manage SQL Server access by assigning a login to fixed server roles like sysadmin and dbcreator, using script or graphical tools, while enforcing minimal permissions.

Create and manage user-defined server roles to grant or deny permissions on securables, add members like SQLTest, and learn that deny overrides grant and with grant enables delegation.

Create a database user mapped to a login in AdventureWorks2014, set a default schema, and explore user types, before assigning roles and permissions in a follow-up step.

Explore the nine fixed database roles, including db_owner, db_securityadmin, and datareader. Learn how grants and denials affect permissions, and how to assign members safely to avoid escalation.

Create and manage database roles inside a database, assign members, and grant or deny permissions on securables such as tables (select, insert, update, execute), with examples like humanresources.department and employee.

Create and manage schemas, assign owners, and apply authorization to strengthen security through ownership chaining across procedures, views, and tables.

Demonstrates least-privilege access using fixed server roles, permissions assigned to roles, and schema-level grants; explains grant, deny, revoke, and how ownership chains influence access through stored procedures and objects.

Denying permissions protects a table from modification; set denies on alter, control, delete, insert, and update for a user or role, ensuring deny trumps grant for production.culture.

Learn how indexes improve query efficiency by using clustered indexes to physically sort unique keys and non-clustered indexes to reference data, including includes and maintenance trade-offs.

Learn how to implement clustered and non-clustered indexes in SSMS by creating a new table, adding key columns, scripting the index, and using include columns and drop options.

Explore how index fragmentation arises when pages split and items move, and learn how reorganise and rebuild address it to optimize performance and space.

Learn online index maintenance in SSMS by rebuilding or reorganizing with ALTER INDEX, guided by fragmentation analysis from dm_db_index_physical_stats; apply thresholds: >30% rebuild, 5–29.99% reorganize.

Discover how fill factor leaves space on leaf level pages to accommodate new items, by setting leaf level pages to 80% (or 70%), and applying this via SSMS or T-SQL.

Optimize SQL server indexes by using filtered indexes that cover only the needed rows, such as city = London or city is null, reducing update and query times.

Identify unused indexes using index usage statistics with sys.dm_db_index_usage_stats and sys.indexes. Disable or drop those with little or no seeks, scans, or lookups to reduce maintenance.

Understand how statistics reveal column data distribution to SQL Server, guiding the engine toward seeks or scans. Learn about auto create statistics and creating full or filtered statistics.

Explore data transfer techniques in SQL Server, including backup and restore, detach and attach, and the import and export wizard for importing and exporting flat files.

Learn bulk insert as an alternative to the import and export wizard for flat files. Create a table and load data using field terminator, row terminator, and first row options.

Enable SQL Server Agent and create a full backup job at midnight using steps and a recurring schedule, with T-SQL scripts and logging to MSDB.

Discover how to administer SQL Server Agent jobs using the job activity monitor, MSDB queries, and TSQL. Learn to view, start, disable, edit steps and schedules, and track job history.

Raise custom alerts with RAISERROR by adding a message (error 50001, severity 16, state 1) in sys.messages and trigger a SQL Server Agent response, such as a backup or operator notification.

Watch how a SQL Server Agent job runs and is monitored, then raise a raiserror with log to trigger an alert and verify it in the job history.

Enable database mail and configure smtp profiles and accounts for SQL Server Agent alerts. Link an operator to alerts to email on job completion, whether it succeeds or fails.

Configure SQL server alerts for critical conditions, including performance counters and user thresholds, notify by email when 11 or more users connect, and explore WMI event alerts.

Configure and standardize databases by applying auto close, auto shrink, and recovery model settings via the model database for new databases, and avoid auto close and auto shrink.

Learn how primary, log, and secondary data files form filegroups in SQL Server and how distributing data across disks improves performance while keeping system and user data separate.

Learn to create a SQL Server database with primary and secondary file groups, multiple files (MDF, NDF, LDF), and autogrowth settings, and configure recovery and compatibility options.

Add new files and filegroups, set the default filegroup, and move tables via clustered indexes between filegroups using GUI and T-SQL.

Learn how to partition a table using a partition function and partition scheme across file groups, enabling year-based data separation and faster queries with indexed year/date fields.

Learn to implement partitioning in SQL Server using the GUI and T-SQL, creating a partition function and partition scheme that map a date column to filegroups (primary, secondary, third).

Define and apply t-sql partitioning by creating a partition function and scheme, assign values to range right or left, and implement on a clustered index to speed queries.

Master multi-terabyte backups by targeting specific file groups or files instead of the whole database, then restore with file-group restores using full, differential, and transaction log backups.

Monitor log space usage with sys.dm_db_log_space_usage, shrink with dbcc shrinkfile, add extra log files on separate disks, and rely on transaction log backups to truncate the log.

Explore dbcc, the database console commands for maintenance, information gathering, and validation, including shrink database to reclaim free space and inspect file sizes with sys.database_files.

learn to implement partially contained databases to avoid orphaned logins when moving databases between SQL Server instances, enabling password-based database users and appropriate permissions for portability.

explore how page compression combines row, prefix, and dictionary methods to shrink data, weigh cpu overhead, and apply or estimate savings via scripts.

Save storage by using sparse columns for mostly-null fields; note non-null values use more space; apply when 40–60% nulls, via table design or t-sql.

Discover non-clustered columnstore indexes, which compress data up to ten times and can improve query performance two to four times, ideal for scanning large tables.

Identify and resolve concurrency problems by understanding update, exclusive, and shared locks, blocking, live locking, and deadlocking in SQL Server.

Demonstrate how transactions and locks cause blocking and deadlocks in SQL Server, and practice diagnosing them with SP_WHO2 and the Activity Monitor.

Monitor SQL Server with dynamic management views like sys.dm_tran_locks and dm_exec_requests to diagnose waits such as LCK_M_S and analyze lock types (S, X, IX).

Examine deadlocking with permanent trace flags 1204 and 1222 using DBCC, view and export SQL Server logs, view XML deadlock details, and analyze SPIDs to identify the victim.

Diagnose SQL Server performance issues using DMVs like dm_os_schedulers and dm_os_buffer_descriptors to assess CPU, memory, and disk I/O. Monitor buffer cache hit ratio, buffer manager pages, and page life expectancy.

Learn to use SQL Server Profiler in SSMS to capture and analyze T-SQL statements, deadlocks, and performance data, then save traces to file or table and inspect deadlock graphs.

Learn how Extended Events replaces SQL Server Profiler with lightweight, live data capture, including templates, session setup, filtering, and event fields for deadlocks and SQL transactions.

Learn to use system monitor (perfmon) to collect SQL Server performance data, enable disk performance counters with diskperf -Y, and create data collector sets.

Identify io, memory, and cpu bottlenecks by examining counters such as memory available bytes, memory pages/sec, cache hit ratio, pages in the buffer pool, and average disc seconds read/write.