SQL Server DBA Operations Toolkit

Build reusable T-SQL scripts for health checks, backups, and user access.

Introduction

30 Second Summary

When something goes wrong with a database in production, someone needs to quickly check if the server is healthy, find what's blocking users, and restore data if needed. That someone is a Database Administrator.

In this project, you will build a SQL Server DBA Operations Toolkit. This is a collection of reusable T-SQL scripts that perform the core daily tasks of a junior DBA, from health checks to backups to user access management.

What You'll Build

A set of T-SQL scripts that query Dynamic Management Views, perform backup and restore operations, manage user access, and verify database integrity on a local SQL Server instance running in Docker.

By the end of this project, you'll have:

  • A working set of health check scripts that query DMVs to inspect server status, database health, and performance metrics.
  • A completed backup and restore cycle proving you can recover a database from a backup file.
  • User access management scripts demonstrating login creation, user mapping, and role assignment.
  • Secret Mission: Simulate a blocking incident and resolve it by killing the offending session like a real production response.

Are there any prerequisites?

This project assumes you already have Docker Desktop running with a SQL Server 2022 container, and DBeaver connected to that instance.

You should be comfortable writing basic SQL queries like SELECT, INSERT, and CREATE TABLE.

Verify Your Setup and Create a Practice Database

Every DBA toolkit needs a database to practice on. You already have SQL Server running in a Docker container and DBeaver connected to it. Before you start writing scripts, you need to confirm that connection still works.

Once verified, you will create a practice database called DBA_Toolkit with realistic tables and sample data. This gives you something meaningful to run health checks, backups, and user management against in later steps.

In this step, get ready to:
  • Confirm your Docker container and DBeaver connection are working.
  • Create a practice database with Employees and SalesOrders tables.
  • Verify the database state and recovery model using system catalog views.
Confirm your Docker container and DBeaver connection

Your SQL Server instance runs inside a Docker container. You need to confirm it is still running before you start executing scripts.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Check that your SQL Server container is running by running this command:
docker ps

You should see your SQL Server container listed with a status of Up and the image mcr.microsoft.com/mssql/server:2022-latest.

Container not running?

If you don't see your container, start it with docker start <container_name> using the container name from docker ps -a. Make sure Docker Desktop is running first.

Help me troubleshoot my SQL Server Docker container

  • Switch to DBeaver and open a new SQL editor tab by right-clicking your SQL Server connection in the Database Navigator panel on the left.
  • Select SQL Editor, then New SQL Editor.
  • Confirm connectivity by typing and executing this query:
SELECT @@VERSION;
  • Click the Execute Statement button (orange play icon) in the toolbar, or press Ctrl+Enter (macOS: Cmd+Enter).

You should see a result containing Microsoft SQL Server 2022 in the results panel at the bottom. This confirms DBeaver can talk to your SQL Server instance.

Create the DBA_Toolkit database with tables and sample data

A DBA toolkit is only useful if you have a realistic database to practice on. You will create a database with an Employees table and a SalesOrders table linked by a foreign key.

  • Clear the SQL editor tab you just used, or open a fresh one.
  • Create the database and the Employees table by pasting this code into the editor:
-- Step 1: Create the DBA_Toolkit practice database
CREATE DATABASE DBA_Toolkit;
GO

USE DBA_Toolkit;
GO

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Department NVARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    Salary DECIMAL(10,2) NOT NULL
);
GO

What does this code do?

CREATE DATABASE DBA_Toolkit creates a new database on your SQL Server instance. GO is a batch separator that tells SQL Server to execute everything above it before moving on.

USE DBA_Toolkit switches your session context so that all following commands run inside that database. The Employees table uses IDENTITY(1,1) to auto-generate IDs starting at 1.

  • Select all the code you just pasted and click Execute Script (the icon with multiple play arrows) or press Alt+X (macOS: Option+X).

You should see messages like Commands completed successfully in the output panel.

Seeing a 'database already exists' error?

If you ran this before and the database already exists, drop it first with DROP DATABASE DBA_Toolkit; and then re-run the create script.

Help me fix my CREATE DATABASE error

  • Add the SalesOrders table below the code you already have:
-- Create SalesOrders table
CREATE TABLE SalesOrders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeID INT NOT NULL,
    OrderDate DATE NOT NULL,
    Amount DECIMAL(10,2) NOT NULL,
    CustomerName NVARCHAR(100) NOT NULL,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
GO

What does the foreign key do?

FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) links every sales order to an existing employee. SQL Server will reject any insert where the EmployeeID does not match a row in the Employees table.

  • Select and execute the SalesOrders block you just added.

You should see Commands completed successfully again.

Invalid object name 'Employees'?

This means the Employees table was not created yet. Make sure you executed the first block (with CREATE DATABASE, USE, and CREATE TABLE Employees) before running this one. The USE DBA_Toolkit line must run first so SQL Server knows which database to create the table in.

  • Populate the Employees table with sample data by adding this code below:
-- Insert sample employees
INSERT INTO Employees (FirstName, LastName, Department, HireDate, Salary)
VALUES 
    ('Priya', 'Sharma', 'Engineering', '2023-01-15', 75000.00),
    ('Rahul', 'Patel', 'Sales', '2022-06-01', 65000.00),
    ('Anita', 'Gupta', 'Engineering', '2024-03-10', 72000.00),
    ('Vikram', 'Singh', 'Marketing', '2021-09-20', 68000.00),
    ('Sneha', 'Reddy', 'Sales', '2023-11-05', 62000.00);
GO

What does this code do?

This inserts 5 employees across three departments: Engineering, Sales, and Marketing. The IDENTITY column auto-assigns EmployeeIDs 1 through 5. You will reference these IDs in the sales orders.

  • Select and execute the INSERT statement.

You should see 5 rows affected in the output.

  • Add the sample sales orders below the employee insert:
-- Insert sample sales orders
INSERT INTO SalesOrders (EmployeeID, OrderDate, Amount, CustomerName)
VALUES
    (2, '2024-01-10', 1500.00, 'Tata Consultancy'),
    (2, '2024-01-15', 2300.00, 'Infosys Ltd'),
    (5, '2024-02-01', 890.00, 'Wipro Technologies'),
    (2, '2024-02-14', 3100.00, 'HCL Technologies'),
    (5, '2024-03-01', 1750.00, 'Tech Mahindra'),
    (2, '2024-03-15', 4200.00, 'Cognizant');
GO

What does this code do?

This inserts 6 sales orders. Notice that EmployeeID 2 (Rahul) and EmployeeID 5 (Sneha) are the salespeople handling these orders. The foreign key constraint ensures these IDs exist in the Employees table before the insert succeeds.

  • Select and execute the sales orders INSERT statement.

You should see 6 rows affected in the output.

Seeing a foreign key violation?

This means the EmployeeID values in your INSERT don't match any rows in the Employees table. Make sure you executed the employee INSERT first. If you dropped and recreated the table, the employee rows need to be re-inserted before the orders.

Help me fix my foreign key error

Verify the database creation

SQL Server stores metadata about every database in a system catalog view called sys.databases. Querying this view is how DBAs confirm a database exists and check its configuration.

  • Add the verification query at the bottom of your script:
-- Verify database creation
SELECT 
    name AS DatabaseName,
    state_desc AS State,
    recovery_model_desc AS RecoveryModel,
    create_date AS CreatedDate
FROM sys.databases
WHERE name = 'DBA_Toolkit';
GO

What does this code do?

sys.databases is a system catalog view that holds one row per database on the server. You are pulling the database name, its state (ONLINE, OFFLINE, RECOVERING), its recovery model, and when it was created.

The recovery model controls how transaction logs behave. FULL means every transaction is logged and you can do point-in-time restores. SQL Server defaults new databases to FULL.

  • Select and execute the verification query.

You should see a single row in the results grid showing: DatabaseName = DBA_Toolkit, State = ONLINE, RecoveryModel = FULL, and today's date as CreatedDate.

Query returns no rows?

Double check the database name in your WHERE clause matches exactly: DBA_Toolkit with an underscore. If the CREATE DATABASE statement did not run successfully earlier, the database will not exist. Scroll up in your output panel to check for any error messages from earlier blocks.

Help me figure out why my database is not showing up

  • Save your script by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Name the file 01_create_practice_db.sql and save it somewhere you can find it later (your Desktop works fine).

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the complete script. Your file should match this exactly:

-- Step 1: Create the DBA_Toolkit practice database
CREATE DATABASE DBA_Toolkit;
GO

USE DBA_Toolkit;
GO

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Department NVARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    Salary DECIMAL(10,2) NOT NULL
);
GO

-- Create SalesOrders table
CREATE TABLE SalesOrders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeID INT NOT NULL,
    OrderDate DATE NOT NULL,
    Amount DECIMAL(10,2) NOT NULL,
    CustomerName NVARCHAR(100) NOT NULL,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
GO

-- Insert sample employees
INSERT INTO Employees (FirstName, LastName, Department, HireDate, Salary)
VALUES 
    ('Priya', 'Sharma', 'Engineering', '2023-01-15', 75000.00),
    ('Rahul', 'Patel', 'Sales', '2022-06-01', 65000.00),
    ('Anita', 'Gupta', 'Engineering', '2024-03-10', 72000.00),
    ('Vikram', 'Singh', 'Marketing', '2021-09-20', 68000.00),
    ('Sneha', 'Reddy', 'Sales', '2023-11-05', 62000.00);
GO

-- Insert sample sales orders
INSERT INTO SalesOrders (EmployeeID, OrderDate, Amount, CustomerName)
VALUES
    (2, '2024-01-10', 1500.00, 'Tata Consultancy'),
    (2, '2024-01-15', 2300.00, 'Infosys Ltd'),
    (5, '2024-02-01', 890.00, 'Wipro Technologies'),
    (2, '2024-02-14', 3100.00, 'HCL Technologies'),
    (5, '2024-03-01', 1750.00, 'Tech Mahindra'),
    (2, '2024-03-15', 4200.00, 'Cognizant');
GO

-- Verify database creation
SELECT 
    name AS DatabaseName,
    state_desc AS State,
    recovery_model_desc AS RecoveryModel,
    create_date AS CreatedDate
FROM sys.databases
WHERE name = 'DBA_Toolkit';
GO

Your practice database is live and loaded with data. Next up, you will query Dynamic Management Views to run real health checks against your SQL Server instance.

Run Instance Health Checks with DMVs

Your practice database is set up and confirmed ONLINE. Now it is time to do what a DBA does first thing every morning: check the health of the SQL Server instance.

SQL Server exposes internal performance and status information through Dynamic Management Views (DMVs). These are special system views that let you ask questions like "How long has the server been running?" and "What is SQL Server spending most of its time waiting on?" without installing any external monitoring tool.

In this step, get ready to:
  • Query server hardware and uptime information using DMVs.
  • Check the state, recovery model, and file sizes of every database on the instance.
  • Identify the top wait types to understand where SQL Server spends its time.
Query server information

When you first log into a SQL Server instance, you want to know the basics: what version is running, how many CPUs are available, how much memory it has, and when it was last restarted. This is your "is the server alive and healthy?" baseline.

  • In DBeaver, open a new SQL editor tab by right-clicking your SQL Server connection in the Database Navigator panel and selecting New SQL Editor.
  • Paste the following server information query into the new editor:
-- Health Check 1: Server Information
SELECT 
    @@SERVERNAME AS ServerName,
    @@VERSION AS SQLVersion,
    cpu_count AS LogicalCPUs,
    physical_memory_kb / 1024 AS PhysicalMemoryMB,
    sqlserver_start_time AS ServerStartTime
FROM sys.dm_os_sys_info;
GO

What does this query do?

  • `@@SERVERNAME` returns the name of the SQL Server instance.
  • `@@VERSION` returns the full version string including build number and OS.
  • `sys.dm_os_sys_info` is a DMV that exposes hardware-level details. You are pulling CPU count, memory in MB, and the exact time SQL Server was last started.
  • Run the query by clicking the Execute button (orange play icon) or pressing Ctrl+Enter (macOS) or Ctrl+Enter (Windows).
  • You should see a single row in the results pane showing your server name, SQL Server 2022 version string, CPU count, memory in MB, and the timestamp when the server started.

Seeing an error about permissions?

DMVs require VIEW SERVER STATE permission. If you connected to SQL Server as the `sa` account, you already have this. If you used a different login, run `GRANT VIEW SERVER STATE TO [your_login]` from an admin connection.

help me fix a permissions error when querying DMVs in SQL Server

Check database health across all databases

After confirming the server itself is running, your next check is whether every database on the instance is ONLINE and how much disk space each one is consuming. A database stuck in RECOVERING or SUSPECT state is an immediate escalation.

  • Add the following query below your first health check in the same editor:
-- Health Check 2: Database Status Overview
SELECT 
    d.name AS DatabaseName,
    d.state_desc AS State,
    d.recovery_model_desc AS RecoveryModel,
    d.create_date AS CreatedDate,
    SUM(mf.size * 8 / 1024) AS TotalSizeMB
FROM sys.databases d
LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
GROUP BY d.name, d.state_desc, d.recovery_model_desc, d.create_date
ORDER BY d.name;
GO

What does this query do?

  • `sys.databases` is a system catalog view that lists every database on the instance with its current state and configuration.
  • `sys.master_files` tracks the physical files (data and log) for each database. The `size` column is in 8KB pages, so `size * 8 / 1024` converts it to megabytes.
  • The `LEFT JOIN` and `GROUP BY` give you one row per database with the total size across all its files.
  • Select only the Health Check 2 query text, then run it.
  • You should see a row for every database on your instance including `master`, `tempdb`, `model`, `msdb`, and your `DBA_Toolkit`. All should show ONLINE in the State column.

What are these system databases?

Every SQL Server instance has four system databases. `master` stores instance-level configuration. `tempdb` holds temporary objects and is recreated on every restart. `model` is the template for new databases. `msdb` stores job schedules and backup history.

Seeing NULL in the TotalSizeMB column?

Make sure you are using `LEFT JOIN` and not `INNER JOIN`. Some system databases may not have file entries visible to your session. A NULL result for TotalSizeMB on system databases is harmless in Docker environments.

help me troubleshoot NULL values in my database health check query

Identify top wait types

Wait statistics are the foundation of SQL Server performance troubleshooting. Every time SQL Server cannot immediately fulfil a request, it records what it was waiting on. Over time, these accumulate into a picture of your server's biggest bottlenecks.

  • Add the following query below your second health check:
-- Health Check 3: Top 10 Wait Statistics
SELECT TOP 10
    wait_type,
    wait_time_ms / 1000.0 AS WaitTimeSec,
    waiting_tasks_count AS WaitCount,
    CASE 
        WHEN waiting_tasks_count > 0 
        THEN wait_time_ms / waiting_tasks_count 
        ELSE 0 
    END AS AvgWaitMs
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
    AND wait_type NOT LIKE '%IDLE%'
    AND wait_type NOT LIKE '%QUEUE%'
    AND wait_type NOT IN ('WAITFOR', 'CLR_AUTO_EVENT', 'BROKER_TASK_STOP', 
                          'LAZYWRITER_SLEEP', 'CHECKPOINT_QUEUE',
                          'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                          'SQLTRACE_BUFFER_FLUSH', 'DIRTY_PAGE_POLL',
                          'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
                          'SP_SERVER_DIAGNOSTICS_SLEEP')
ORDER BY wait_time_ms DESC;
GO

What does this query do?

`sys.dm_os_wait_stats` holds cumulative wait data since the server last restarted. The `WHERE` clause filters out background and idle waits that are always present and not actionable (like `LAZYWRITER_SLEEP` or `CHECKPOINT_QUEUE`).

The `CASE` expression calculates average wait time per occurrence. In a real production environment, high values for waits like `PAGEIOLATCH_SH` (disk I/O) or `LCK_M_X` (locking) would tell you where to focus your investigation.

  • Select only the Health Check 3 query text, then run it.
  • You should see up to 10 rows showing wait type names, total wait time in seconds, count of waits, and average wait in milliseconds. The exact wait types depend on what your Docker container has been doing since it started.

Interview gold

If an interviewer asks "How would you troubleshoot a slow SQL Server?", your first answer should be: "I would check the top wait types using sys.dm_os_wait_stats to understand what the server is spending most of its time waiting on." This query gives you that answer.

Getting zero rows back?

If your container was just started, there may not be enough accumulated wait data yet. Run a few queries against `DBA_Toolkit` (like `SELECT * FROM Employees`) and then re-run the wait stats query. The waits accumulate over time.

help me understand why my wait stats query returns no rows

  • Save the file as `02_health_checks.sql` by pressing Cmd+S (macOS) or Ctrl+S (Windows). Name it `02_health_checks.sql`.

✔️ Awesome, I've got everything!

Great. Double check you have saved `02_health_checks.sql` with all three health check queries.

ⓧ I'd like to double check the full code

Here is the complete `02_health_checks.sql` file for comparison:

-- Health Check 1: Server Information
SELECT 
    @@SERVERNAME AS ServerName,
    @@VERSION AS SQLVersion,
    cpu_count AS LogicalCPUs,
    physical_memory_kb / 1024 AS PhysicalMemoryMB,
    sqlserver_start_time AS ServerStartTime
FROM sys.dm_os_sys_info;
GO

-- Health Check 2: Database Status Overview
SELECT 
    d.name AS DatabaseName,
    d.state_desc AS State,
    d.recovery_model_desc AS RecoveryModel,
    d.create_date AS CreatedDate,
    SUM(mf.size * 8 / 1024) AS TotalSizeMB
FROM sys.databases d
LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
GROUP BY d.name, d.state_desc, d.recovery_model_desc, d.create_date
ORDER BY d.name;
GO

-- Health Check 3: Top 10 Wait Statistics
SELECT TOP 10
    wait_type,
    wait_time_ms / 1000.0 AS WaitTimeSec,
    waiting_tasks_count AS WaitCount,
    CASE 
        WHEN waiting_tasks_count > 0 
        THEN wait_time_ms / waiting_tasks_count 
        ELSE 0 
    END AS AvgWaitMs
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
    AND wait_type NOT LIKE '%IDLE%'
    AND wait_type NOT LIKE '%QUEUE%'
    AND wait_type NOT IN ('WAITFOR', 'CLR_AUTO_EVENT', 'BROKER_TASK_STOP', 
                          'LAZYWRITER_SLEEP', 'CHECKPOINT_QUEUE',
                          'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                          'SQLTRACE_BUFFER_FLUSH', 'DIRTY_PAGE_POLL',
                          'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
                          'SP_SERVER_DIAGNOSTICS_SLEEP')
ORDER BY wait_time_ms DESC;
GO

You now have a reusable set of health check scripts that answer the three big questions: Is the server running? Are all databases healthy? What is the server waiting on? Next up, you will build monitoring queries that detect blocking and identify the most expensive queries on the instance.

Monitor for Blocking and Long-Running Queries

Your health checks from the previous step tell you about the server's history. Wait stats accumulate over time, and database states reflect configuration decisions made days or weeks ago.

Monitoring is different. It answers the question "what is happening right now?" In this step, you'll build queries that show currently running queries, detect blocking in real time, and identify the most expensive queries in the plan cache.

In this step, get ready to:
  • Query currently executing sessions to see what's running on your instance.
  • Write a blocking detection query that identifies lock conflicts between sessions.
  • Find the top CPU-intensive queries from the plan cache for performance troubleshooting.
See what's running right now with dm_exec_requests

When users report slowness, the first thing you check as a DBA is "what queries are currently executing?" The DMV sys.dm_exec_requests shows every active request on the server, including its status, CPU consumption, and whether anything is blocking it.

  • In DBeaver, open a new SQL Editor tab by right-clicking your connection in the **Database Navigator** and selecting **New SQL Editor**.
  • Paste the following query to see all currently executing user queries:
-- Monitoring 1: Currently Executing Queries
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time AS CPUTimeMs,
    r.total_elapsed_time AS ElapsedTimeMs,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS WaitTimeMs,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS QueryText
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1;
GO

What does this query do?

  • sys.dm_exec_requests returns one row per currently executing request. It shows status, CPU time, elapsed time, and whether the session is being blocked.
  • sys.dm_exec_sessions adds context about who is running the query (login name, host, program).
  • CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) decodes the SQL handle into readable query text so you can see exactly what each session is running.
  • The WHERE s.is_user_process = 1 filter excludes SQL Server's internal system processes so you only see real user activity.
  • Run the query by clicking the **Execute** button (or press **Ctrl+Enter**).
  • You should see at least one row. That row is your own query executing. The status column will show running and the QueryText column will show the monitoring query itself.

Getting zero rows back?

If you see no results, it means the query finished before it could capture itself. This can happen on fast instances. Try running a longer query in a second tab (like WAITFOR DELAY '00:00:05') and then re-run this monitoring query.

help me troubleshoot why my dm_exec_requests query returns no rows

Write a blocking detection query

Blocking happens when one session holds a lock that another session needs. The blocked session waits until the lock is released. In production, this is one of the most common causes of "the app is slow" complaints.

Your blocking detection query checks the blocking_session_id column. When this value is not zero, the session is waiting on another session's lock.

  • Add the following blocking detection query below your first monitoring query in the same file:
-- Monitoring 2: Blocking Detection
SELECT 
    r.session_id AS BlockedSessionID,
    r.blocking_session_id AS BlockingSessionID,
    r.wait_type,
    r.wait_time AS WaitTimeMs,
    r.wait_resource,
    blocked_text.text AS BlockedQuery,
    blocking_text.text AS BlockingQuery
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) blocked_text
LEFT JOIN sys.dm_exec_requests br 
    ON r.blocking_session_id = br.session_id
OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) blocking_text
WHERE r.blocking_session_id <> 0;
GO

What does this query do?

  • The WHERE r.blocking_session_id <> 0 filter finds only sessions that are currently blocked by another session.
  • CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) gets the text of the blocked (victim) query.
  • The LEFT JOIN to sys.dm_exec_requests br finds the blocker's request, and OUTER APPLY gets the blocker's query text. It uses LEFT/OUTER because the blocker might be idle (not actively running a request).
  • wait_resource tells you exactly which resource (table, page, row) the victim is waiting to access.
  • Highlight just the blocking detection query and run it.
  • You should see an empty result set. This is expected because nothing is currently blocking on your instance.

Why is an empty result good news?

An empty result means no sessions are waiting on locks held by other sessions. In production, you would run this query when users report slowness. If it returns rows, you have found your problem. You will simulate this exact scenario in the Secret Mission later.

Find the most expensive queries in the plan cache

SQL Server caches execution plans for queries it has run. The sys.dm_exec_query_stats DMV stores performance statistics for each cached plan, including total CPU time and execution count. This lets you find which queries consume the most resources over time.

  • Add the following query below your blocking detection query in 03_monitoring.sql:
-- Monitoring 3: Top 5 CPU-Intensive Queries from Plan Cache
SELECT TOP 5
    total_worker_time / execution_count AS AvgCPUTimeMicrosec,
    execution_count AS ExecutionCount,
    total_worker_time / 1000 AS TotalCPUTimeMs,
    SUBSTRING(
        t.text, 
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1
    ) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY total_worker_time / execution_count DESC;
GO

What does this query do?

  • total_worker_time / execution_count calculates the average CPU cost per execution. This is the best metric for finding poorly-performing queries because it normalizes for how often the query runs.
  • The SUBSTRING expression extracts the exact SQL statement from the batch text using statement_start_offset and statement_end_offset. Without this, you would see the entire batch rather than the specific expensive statement.
  • The results are ordered by average CPU time descending, so the most expensive queries appear first. These are the queries you would escalate to senior DBAs or developers for optimization.
  • Highlight the plan cache query and run it.
  • You should see results showing queries you ran earlier (the INSERT statements, CREATE TABLE statements, or health check queries from Step 2). The AvgCPUTimeMicrosec column tells you how expensive each query is on average.

Seeing unexpected or system queries in results?

The plan cache includes queries from DBeaver itself (metadata lookups, autocomplete queries). This is normal. In production, you would filter these out by program name or focus on queries with high execution counts.

help me understand my dm_exec_query_stats results

✔️ Awesome, I've got everything!

Great. Double check you have saved your 03_monitoring.sql file with all three monitoring queries.

ⓧ I'd like to double check the full code

Here is the complete 03_monitoring.sql file with all three monitoring queries:

-- Monitoring 1: Currently Executing Queries
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time AS CPUTimeMs,
    r.total_elapsed_time AS ElapsedTimeMs,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS WaitTimeMs,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS QueryText
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1;
GO

-- Monitoring 2: Blocking Detection
SELECT 
    r.session_id AS BlockedSessionID,
    r.blocking_session_id AS BlockingSessionID,
    r.wait_type,
    r.wait_time AS WaitTimeMs,
    r.wait_resource,
    blocked_text.text AS BlockedQuery,
    blocking_text.text AS BlockingQuery
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) blocked_text
LEFT JOIN sys.dm_exec_requests br 
    ON r.blocking_session_id = br.session_id
OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) blocking_text
WHERE r.blocking_session_id <> 0;
GO

-- Monitoring 3: Top 5 CPU-Intensive Queries from Plan Cache
SELECT TOP 5
    total_worker_time / execution_count AS AvgCPUTimeMicrosec,
    execution_count AS ExecutionCount,
    total_worker_time / 1000 AS TotalCPUTimeMs,
    SUBSTRING(
        t.text, 
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1
    ) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY total_worker_time / execution_count DESC;
GO

You now have a complete set of real-time monitoring queries. Next up, you'll perform a full backup and restore cycle to prove your database is recoverable.

Perform Backup and Restore Operations

Your monitoring scripts can now show you what is happening on the server in real time. The next question an interviewer will ask is: "How do you protect data from loss?"

In this step, you will perform a complete backup and restore cycle. You will take a full backup, verify it is restorable, simulate new data arriving, capture a transaction log backup, and then restore everything to a new database to prove recoverability.

In this step, get ready to:
  • Take a full database backup and validate it with RESTORE VERIFYONLY.
  • Insert new data and take a transaction log backup to capture incremental changes.
  • Restore the backup as a new database and verify data integrity.
Take a full backup and verify it

Before SQL Server can write backup files, the destination folder must exist inside the container. The standard location is /var/opt/mssql/backup/.

  • Open Terminal by pressing Cmd+Space (macOS) or the Windows key (Windows), then type Terminal and press Enter.
  • Create the backup directory inside your SQL Server container by running this command:
docker exec -it [[CONTAINER_NAME="your SQL Server container name"]] mkdir -p /var/opt/mssql/backup

Find your container name

If you are not sure of your container name, run docker ps first. The container name is in the last column of the output.

Seeing "No such container"?

Make sure your SQL Server container is running. Open Docker Desktop and check that your SQL Server container shows a green "Running" status. If it is stopped, click the play button to start it.

Help me troubleshoot my Docker container not being found

Now you have a place for backup files. Time to write the backup script.

  • Switch back to DBeaver.
  • Create a new SQL editor tab by right-clicking your connection in the Database Navigator panel on the left and selecting New SQL Editor.
  • Take a full backup of your database and verify it by running this code:
-- Step 4.1: Create backup directory (run in Terminal first):
-- docker exec -it <container_name> mkdir -p /var/opt/mssql/backup

-- Full Backup
BACKUP DATABASE DBA_Toolkit
TO DISK = '/var/opt/mssql/backup/DBA_Toolkit_Full.bak'
WITH FORMAT, INIT, NAME = 'DBA_Toolkit Full Backup';
GO

-- Verify the backup is valid
RESTORE VERIFYONLY
FROM DISK = '/var/opt/mssql/backup/DBA_Toolkit_Full.bak';
GO

What does this code do?

BACKUP DATABASE writes a complete copy of the database to a .bak file. The FORMAT, INIT options overwrite any existing backup in that file rather than appending to it.

RESTORE VERIFYONLY checks that the backup file is structurally valid and restorable without actually restoring it. This is a standard safety check you run immediately after every backup.

  • Save this file as 04_backup_restore.sql.
  • Run the full backup statement first. You should see a message confirming the database was backed up, showing the number of pages processed.
  • Run the RESTORE VERIFYONLY statement. You should see "The backup set on file 1 is valid" in the results.

Seeing "Cannot open backup device"?

This means the /var/opt/mssql/backup directory does not exist inside the container. Go back to Terminal and re-run the docker exec ... mkdir -p command from earlier in this substep.

Help me fix my backup path error

Insert new data and take a transaction log backup

A full backup captures the entire database at a point in time. In production, new data arrives constantly between full backups. A transaction log backup captures only the changes since the last backup, letting you recover to any point in time.

Why does DBA_Toolkit use the FULL recovery model?

The FULL recovery model keeps every transaction in the log until you explicitly back it up. This enables point-in-time recovery. The SIMPLE model auto-clears the log at checkpoints, which means you can only restore to your last full backup.

Production databases almost always use FULL because data loss between backups is unacceptable. The tradeoff is you must schedule regular log backups or the transaction log file grows indefinitely.

  • Simulate new data arriving after the full backup by adding this code below the RESTORE VERIFYONLY block in 04_backup_restore.sql:
-- Step 4.2: Insert new data after backup
USE DBA_Toolkit;
GO

INSERT INTO SalesOrders (EmployeeID, OrderDate, Amount, CustomerName)
VALUES
    (3, '2024-04-01', 5600.00, 'Reliance Industries'),
    (4, '2024-04-05', 2100.00, 'Bharti Airtel');
GO

What does this code do?

These two INSERT statements simulate real orders arriving after the full backup was taken. The full backup you just created does NOT contain these rows. Only a transaction log backup will capture them.

  • Run the INSERT block. You should see "2 rows affected" in the output.
  • Now capture those changes with a transaction log backup by adding this code below the INSERT block:
-- Transaction Log Backup
BACKUP LOG DBA_Toolkit
TO DISK = '/var/opt/mssql/backup/DBA_Toolkit_Log.trn'
WITH FORMAT, INIT, NAME = 'DBA_Toolkit Log Backup';
GO

What does this code do?

BACKUP LOG backs up only the transaction log (the changes since the last backup). The file extension .trn is the conventional extension for transaction log backups, just as .bak is for full backups.

  • Run the BACKUP LOG statement. You should see a confirmation message showing pages and files processed.
  • Save 04_backup_restore.sql.

Seeing "BACKUP LOG cannot be performed"?

This error occurs if the database is in SIMPLE recovery mode. Transaction log backups require FULL or BULK-LOGGED recovery. Your DBA_Toolkit database should already be in FULL mode (the default). If it is not, run ALTER DATABASE DBA_Toolkit SET RECOVERY FULL and then take a full backup before retrying the log backup.

Help me fix my transaction log backup error

Restore the database from backup to prove recoverability

A backup is only useful if you can restore from it. In an interview, saying "I verified my backups are restorable" carries far more weight than "I set up scheduled backups."

You will restore the full backup as a separate database called DBA_Toolkit_Restored. This avoids overwriting your live database while still proving the backup works.

  • First, check what logical file names are inside your backup by adding this code below the BACKUP LOG block in 04_backup_restore.sql:
-- Step 4.3: Restore full backup as a new database
-- First check the logical file names
RESTORE FILELISTONLY
FROM DISK = '/var/opt/mssql/backup/DBA_Toolkit_Full.bak';
GO

What does RESTORE FILELISTONLY do?

Every backup file contains logical names for the data file and log file. When you restore to a different database name, you need to tell SQL Server where to put the physical files using WITH MOVE. RESTORE FILELISTONLY shows you the logical names so you know what to reference.

You should see two rows: one with LogicalName DBA_Toolkit (the data file) and one with DBA_Toolkit_log (the log file).

  • Run the RESTORE FILELISTONLY statement. Confirm you see the two logical names in the results.
  • Now restore the full backup as a new database by adding this code:
-- Restore as a new database with MOVE
RESTORE DATABASE DBA_Toolkit_Restored
FROM DISK = '/var/opt/mssql/backup/DBA_Toolkit_Full.bak'
WITH MOVE 'DBA_Toolkit' TO '/var/opt/mssql/data/DBA_Toolkit_Restored.mdf',
     MOVE 'DBA_Toolkit_log' TO '/var/opt/mssql/data/DBA_Toolkit_Restored_log.ldf',
     REPLACE;
GO

What does this code do?

RESTORE DATABASE recreates a database from the backup file. The two MOVE clauses redirect the data and log files to new paths so they do not collide with the original database's files.

REPLACE tells SQL Server to overwrite any existing database with that name. The logical names (DBA_Toolkit and DBA_Toolkit_log) are the ones you saw from RESTORE FILELISTONLY.

  • Run the RESTORE DATABASE statement. You should see a message confirming the database was successfully restored.
  • Verify the restored data is intact by adding and running this verification query:
-- Verify restoration
USE DBA_Toolkit_Restored;
GO

SELECT COUNT(*) AS EmployeeCount FROM Employees;
SELECT COUNT(*) AS OrderCount FROM SalesOrders;
GO

What should I see?

You should see EmployeeCount = 5 and OrderCount = 6. These match the original data from when you took the full backup. The two rows you inserted after the backup (Reliance Industries and Bharti Airtel) are NOT in this restored database because they only exist in the transaction log backup.

  • Save 04_backup_restore.sql.

Seeing "Exclusive access could not be obtained"?

This means another connection is using the target database. In DBeaver, make sure no other SQL editor tab has DBA_Toolkit_Restored selected as the active database. Switch any other tabs to use master and try the restore again.

Help me fix exclusive access errors during restore

✔️ Awesome, I've got everything!

Great. Double check you have saved 04_backup_restore.sql with all the code from this step.

ⓧ I'd like to double check the full code

Here is the complete 04_backup_restore.sql file for comparison:

-- Step 4.1: Create backup directory (run in Terminal first):
-- docker exec -it <container_name> mkdir -p /var/opt/mssql/backup

-- Full Backup
BACKUP DATABASE DBA_Toolkit
TO DISK = '/var/opt/mssql/backup/DBA_Toolkit_Full.bak'
WITH FORMAT, INIT, NAME = 'DBA_Toolkit Full Backup';
GO

-- Verify the backup is valid
RESTORE VERIFYONLY
FROM DISK = '/var/opt/mssql/backup/DBA_Toolkit_Full.bak';
GO

-- Step 4.2: Insert new data after backup
USE DBA_Toolkit;
GO

INSERT INTO SalesOrders (EmployeeID, OrderDate, Amount, CustomerName)
VALUES
    (3, '2024-04-01', 5600.00, 'Reliance Industries'),
    (4, '2024-04-05', 2100.00, 'Bharti Airtel');
GO

-- Transaction Log Backup
BACKUP LOG DBA_Toolkit
TO DISK = '/var/opt/mssql/backup/DBA_Toolkit_Log.trn'
WITH FORMAT, INIT, NAME = 'DBA_Toolkit Log Backup';
GO

-- Step 4.3: Restore full backup as a new database
-- First check the logical file names
RESTORE FILELISTONLY
FROM DISK = '/var/opt/mssql/backup/DBA_Toolkit_Full.bak';
GO

-- Restore as a new database with MOVE
RESTORE DATABASE DBA_Toolkit_Restored
FROM DISK = '/var/opt/mssql/backup/DBA_Toolkit_Full.bak'
WITH MOVE 'DBA_Toolkit' TO '/var/opt/mssql/data/DBA_Toolkit_Restored.mdf',
     MOVE 'DBA_Toolkit_log' TO '/var/opt/mssql/data/DBA_Toolkit_Restored_log.ldf',
     REPLACE;
GO

-- Verify restoration
USE DBA_Toolkit_Restored;
GO

SELECT COUNT(*) AS EmployeeCount FROM Employees;
SELECT COUNT(*) AS OrderCount FROM SalesOrders;
GO

You have just completed a full backup and restore cycle. You can now confidently tell an interviewer that you have taken backups, validated them, and proven recoverability by restoring to a separate database. Next up, you will manage user access and run consistency checks to round out your DBA toolkit.

Manage User Access and Run Consistency Checks

You just proved that your backups work by restoring a full database from scratch. That is the gold standard of recoverability.

Now you will tackle the other daily DBA responsibility: managing who can access your databases. When a developer or application needs database access, they submit a request and you fulfill it by creating logins, users, and assigning the right level of permissions. You will also run a final integrity check to confirm your database has no corruption.

In this step, get ready to:
  • Create a read-only login and database user with the db_datareader role.
  • Create a read-write login and user, then verify both users' permissions.
  • Run DBCC CHECKDB to verify database integrity.
Create a read-only login and database user

In SQL Server, access control works at two levels. A login is a server-level credential that lets someone connect to the SQL Server instance. A user is a database-level identity mapped to that login, controlling what they can do inside a specific database.

  • In DBeaver, open a new SQL editor tab by right-clicking your connection in the **Database Navigator** and selecting **New SQL Editor**.
  • Create the read-only login and user by running this code:
-- Step 5.1: Create a read-only login and user
USE master;
GO

CREATE LOGIN app_readonly WITH PASSWORD = 'ReadOnly@2024!';
GO

USE DBA_Toolkit;
GO

CREATE USER app_readonly FOR LOGIN app_readonly;
GO

ALTER ROLE db_datareader ADD MEMBER app_readonly;
GO

What does this code do?

This script fulfills a standard "read-only access request" in three steps:

  • CREATE LOGIN creates a server-level credential in the master database. This is the "key to the building."
  • CREATE USER maps that login to a user inside DBA_Toolkit. This is the "badge for a specific floor."
  • ALTER ROLE db_datareader ADD MEMBER grants SELECT permission on all tables. The user can read data but cannot insert, update, or delete anything.
  • Run the script by clicking the **Execute** button (or pressing **Ctrl+Enter**).
  • You should see messages confirming each command completed successfully with no errors.

Seeing 'Login already exists'?

If you ran this script before and are re-running it, the login already exists from the previous attempt. Run DROP LOGIN app_readonly; in the master database first, then try again.

Why is my CREATE LOGIN failing?

Create a read-write login and verify permissions

The second access request is for an application that needs to both read and write data. You will create a similar login and user, but assign two roles instead of one.

  • Add the following code below your previous script in the same editor tab, then run it:
-- Step 5.2: Create a read-write login and user
USE master;
GO

CREATE LOGIN app_readwrite WITH PASSWORD = 'ReadWrite@2024!';
GO

USE DBA_Toolkit;
GO

CREATE USER app_readwrite FOR LOGIN app_readwrite;
GO

ALTER ROLE db_datareader ADD MEMBER app_readwrite;
ALTER ROLE db_datawriter ADD MEMBER app_readwrite;
GO

-- Verify user permissions
SELECT 
    dp.name AS UserName,
    dp.type_desc AS UserType,
    r.name AS RoleName
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE dp.name IN ('app_readonly', 'app_readwrite')
ORDER BY dp.name, r.name;
GO

What does this code do?

  • The first half creates app_readwrite with both db_datareader (SELECT on all tables) and db_datawriter (INSERT, UPDATE, DELETE on all tables).
  • The verification query joins sys.database_role_members with sys.database_principals to show which roles each user belongs to. This is how you audit permissions after completing an access request.
  • Check the results grid at the bottom of DBeaver.
  • You should see three rows: app_readonly with db_datareader, and app_readwrite with both db_datareader and db_datawriter.

Why logins and users are separate

This two-level design means one person can have different permissions in different databases. A login gets them through the front door (the server), but their user mapping in each database controls exactly what they can touch.

Query returns no rows?

Make sure the USE DBA_Toolkit statement ran before the verification query. The sys.database_role_members view is database-specific. If your context is still set to master, it will not find your users.

Why is my permissions verification query returning empty results?

Run DBCC CHECKDB to verify database integrity

The last task in your DBA daily checklist is running a consistency check. DBCC CHECKDB scans every page and structure in a database looking for corruption. If it finds problems, you escalate immediately.

  • Add the following code at the bottom of your script and run it:
-- Step 5.3: Run Database Consistency Check
DBCC CHECKDB('DBA_Toolkit') WITH NO_INFOMSGS;
GO

What does this command do?

  • DBCC CHECKDB checks the logical and physical integrity of every object in the database. It validates page structures, index consistency, and allocation metadata.
  • WITH NO_INFOMSGS suppresses informational messages so you only see errors or warnings. A clean run produces the message "CHECKDB found 0 allocation errors and 0 consistency errors."
  • Check the messages output in DBeaver.
  • You should see a message confirming 0 allocation errors and 0 consistency errors. Your database is clean.

Seeing allocation or consistency errors?

On a freshly created practice database, errors here would be very unusual. If you see any, try running DBCC CHECKDB('DBA_Toolkit') without the NO_INFOMSGS flag to see the full output. In a real environment, any CHECKDB errors get escalated to a senior DBA immediately.

Help me understand my DBCC CHECKDB output.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file as 05_user_management.sql.

ⓧ I'd like to double check the full code

Here is the complete 05_user_management.sql file for comparison:

-- Step 5.1: Create a read-only login and user
USE master;
GO

CREATE LOGIN app_readonly WITH PASSWORD = 'ReadOnly@2024!';
GO

USE DBA_Toolkit;
GO

CREATE USER app_readonly FOR LOGIN app_readonly;
GO

ALTER ROLE db_datareader ADD MEMBER app_readonly;
GO

-- Step 5.2: Create a read-write login and user
USE master;
GO

CREATE LOGIN app_readwrite WITH PASSWORD = 'ReadWrite@2024!';
GO

USE DBA_Toolkit;
GO

CREATE USER app_readwrite FOR LOGIN app_readwrite;
GO

ALTER ROLE db_datareader ADD MEMBER app_readwrite;
ALTER ROLE db_datawriter ADD MEMBER app_readwrite;
GO

-- Verify user permissions
SELECT 
    dp.name AS UserName,
    dp.type_desc AS UserType,
    r.name AS RoleName
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
WHERE dp.name IN ('app_readonly', 'app_readwrite')
ORDER BY dp.name, r.name;
GO

-- Step 5.3: Run Database Consistency Check
DBCC CHECKDB('DBA_Toolkit') WITH NO_INFOMSGS;
GO

You have now completed every core DBA operational task: health checks, monitoring, backups, restores, user access, and integrity checks. Your toolkit is ready. Next up is the Secret Mission where you will simulate a real blocking incident and practice resolving it under pressure.

Secret mission

Simulate and Resolve a Blocking Incident

In production, a developer forgets to commit a transaction and users start complaining about slow queries. Your job is to find the blocker, diagnose the problem, and resolve it. This is exactly the kind of incident you will handle as a junior DBA at CSG.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally using Docker, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • SQL Server Docker container (pre-existing).
  • DBA_Toolkit database.
  • DBA_Toolkit_Restored database.
  • Backup files inside the container at /var/opt/mssql/backup/.
  • Server logins: app_readonly and app_readwrite.
  • SQL script files saved locally.

Keep everything running

No action needed. Choose this if you are still actively building or want to practice running your scripts again.

  • Your Docker container stays running and all databases remain intact.
  • Your SQL scripts are ready to re-run or modify anytime.
  • The container uses some memory while running, but no financial cost.

Pause - I'll come back to this later

Stop the Docker container to free up memory. Your databases and data persist inside the container and will be available when you start it again.

  • Open Terminal and run docker stop followed by your container name to stop the SQL Server container.
  • When you are ready to resume, run docker start followed by your container name.
  • Keep your SQL script files saved locally for future reference.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes everything including databases, backups, logins, and the container itself.

  • Connect to SQL Server in DBeaver and run DROP DATABASE DBA_Toolkit; and DROP DATABASE DBA_Toolkit_Restored; to remove the databases.
  • Run DROP LOGIN app_readonly; and DROP LOGIN app_readwrite; to remove the server logins.
  • Open Terminal and run docker stop followed by your container name to stop the container.
  • Run docker rm followed by your container name to delete the container entirely.
  • Delete your local SQL script files if you no longer need them.

Nice Work!

Nice Work!

You just built a complete SQL Server DBA operations toolkit from scratch. Every script you wrote maps directly to a responsibility in the CSG Systems Administrator-Grad job description.

Here's what you learned:

  • Ran instance health checks and performance monitoring using Dynamic Management Views like sys.dm_os_wait_stats and sys.dm_exec_requests.
  • Performed a full backup and restore cycle, validated it with RESTORE VERIFYONLY, and restored to a new database to prove recoverability.
  • Managed user access by creating logins, mapping database users, and assigning roles like db_datareader and db_datawriter.
  • Simulated and resolved a real blocking incident by identifying the offending session with DMVs and terminating it with KILL.

When an interviewer asks "How would you check if a database is healthy?" you can now walk them through your exact process. That is the difference between theory and experience.

Ready to quiz yourself?