SQL Server Database Administrator: Learn SQL DBA Skills

Learn how SQL Server provides a built-in backup process and master the basics of full, differential, copy, and transaction log backups for reliable data recovery.

explain the copy database backup method, which backs up the entire database without resetting the differential marker, and contrast it with full backups that reset the marker for differential backups.

Perform a full backup of the Adventure Works database using SQL Server Management Studio, setting the recovery model to full and saving the backup to a local file.

Perform a differential backup in SQL Server 2016, copying only changes since the last full backup and requiring a full backup in place.

Back up the transaction log in SQL Server, record change times and queries, configure append and truncate options, and review backup sets, including full, differential, and transaction log restores.

Perform a guided database recovery using full, differential, and transaction log backups to restore the Adventure Works 2016 database in SQL Server, including tail log backups and restore verification.

Perform a lab on Adventure Works 20:16 in MSDE to create full, differential, and transaction log backups, changing person table's middle name to z for row five.

Perform a recovery of the Adventure Works 20:16 database using the backups created in the lab exercise. Please report any issues encountered during the process.

Perform and validate full, differential, and transaction log backups, then recover the database using these backups, with backups scheduled off-peak to minimize network impact.

Explore the SQL Server configuration manager to manage services, configure network protocols, and view service accounts and start modes for instances like analysis server, report server, and sequel agent.

Use Windows PowerShell to list running services on a Windows Server and identify SQL Server instances by name. See database engine, analysis services, and reporting services running on the machine.

Connect to multiple SQL Server instances using SQL Server Management Studio, exploring database engine, Analysis Services, and Reporting Services across versions from 7.0 to 2016, including virtualization.

Identify installed databases in SQL Server Management Studio, explore the Adventure Works 2016 database and its schemas, and use the query window and properties to inspect files and permissions.

Identify various server objects in SQL Server using the object explorer, focusing on security, fixed server roles, and service accounts with full access.

Explore Transact-SQL basics for SQL Server, focusing on data manipulation language and data definition language, including select, insert, update, delete, create, alter, drop, and truncate.

Learn the T-SQL DML select query to retrieve data from a database, compare selecting all columns versus specific columns, and explore schema and table concepts in Adventure Works 2016.

Exercise the T-SQL delete query with a where clause to remove data from the human resources schema's employee pay history table, using a filter to avoid deleting all records.

Master the T-SQL update query to modify existing records using set and where clauses. See how the where filter targets specific rows in a lab, preventing unintended updates.

Master the T-SQL insert into command to add single or multiple rows in SQL Server, using SSMS with the Adventure Works sample database, while respecting primary key constraints.

Master the t-sql ddl create query to define and create the fruits table in the person schema, with a primary key and not null constraint, using integer and varchar columns.

Learn how the T-SQL DDL ALTER command adds, deletes, or modifies columns in an existing table, with an example of adding a new column and inspecting the updated table.

Learn how the T-SQL DDL drop command deletes a table and its data, while checking for dependencies and using drop table <table_name> in an MSDE virtual lab.

Learn how to truncate a table with T-SQL: remove all rows while preserving the table structure and schema, demonstrated on the fruits table in the persons schema.

Learn the basics of SQL server and the T-SQL dialect, focusing on data manipulation language (select, insert, update, delete) and data definition language (create, drop, truncate) with practical lab practice.

Learn to monitor SQL Server performance as a database administrator by using the activity monitor, collecting data, performing performance monitoring, and leveraging extended events.

Explore how to use the SQL Server Activity Monitor to view processes, resource waits, data files, and recent expensive queries in a virtual lab.

Configure and monitor data collection by creating a management data warehouse, mapping logins, and configuring data collection tasks to track disk usage, server activity, and query statistics.

Explore how to use performance monitor to track SQL Server performance by adding counters for physical disk, process time, and user time, and create data collection sets for diagnostics.

Learn to use the extended events wizard to monitor live data, analyze server queries, performance, and health, and configure a new session with a default template and 1 GB retention.

Discover how an index speeds up SQL Server queries by locating data without scanning the whole table, and learn to create a last name index with T-SQL.

Explore authentication methods, including domain accounts (Windows-based), manage permissions with roles and group access, and implement auditing with mindful scope and storage considerations on a SQL Server instance.

Explore SQL Server authentication, including mixed mode, Windows versus SQL Server logins, and how to map logins to databases. Learn about local and domain logins and the application role.

Learn to manage server and database level permissions in SQL Server, using grant, deny, and revoke to control access for users and groups, assign server roles, and set table permissions.

Learn to configure SQL Server auditing to track user activities, set destinations and file sizes, enable server audit specifications, and diagnose permissions issues affecting audit activation.

Explore SQL Server database security by mastering authentication options, such as Windows and mixed mode, and applying permissions to database users and groups to control access and auditing.