Week 1 – SQL DBA Detailed Study Guide
🎯 Goal: Be strong in SQL DBA so you can clear at least Round 1 anywhere.
This week covers SQL Server fundamentals including backup/restore, performance tuning,
high availability, disaster recovery, and security with real-time scenarios and interview
Q&A.
Day 1–2: SQL Backup & Restore
Definition
A backup is a copy of database data that can be used to restore and recover the database
after a failure. Types include Full, Differential, and Transaction Log backups.
Syntax & Examples
-- Full Backup
BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB_Full.bak'
-- Differential Backup
BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB_Diff.bak' WITH DIFFERENTIAL
-- Transaction Log Backup
BACKUP LOG MyDB TO DISK = 'C:\Backups\MyDB_Log.trn'
-- PIT Restore Example
RESTORE DATABASE MyDB FROM DISK = 'Full.bak' WITH NORECOVERY
RESTORE DATABASE MyDB FROM DISK = 'Diff.bak' WITH NORECOVERY
RESTORE LOG MyDB FROM DISK = 'Log.trn' WITH STOPAT = '2023-09-18 10:15:00',
RECOVERY
Real-Time Scenario
The database crashed at 10:30 AM. You have:
- Full backup at 1 AM
- Differential backup at 9 AM
- Transaction log backups every 15 minutes
➡ Solution: Restore Full → Diff → Logs until 10:15 AM.
Interview Q&A
Q: What’s the difference between NORECOVERY and RECOVERY in restore?
A: NORECOVERY keeps DB in restoring state (for applying multiple backups). RECOVERY
brings it online.
Checklist
☐ Understood backup types (Full, Diff, Log)
☐ Practiced backup/restore syntax
☐ Solved real-time scenario
☐ Revised interview Q&A
Day 3: Performance Tuning
Definition
Performance tuning ensures queries and databases run efficiently. It includes optimizing
queries, indexes, and server configuration.
Syntax & Examples
-- Find top expensive queries
SELECT TOP 10 total_worker_time/execution_count AS [Avg CPU Time],
execution_count, text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY [Avg CPU Time] DESC;
-- Check blocking sessions
SELECT blocking_session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
Real-Time Scenario
A query taking 5 minutes is reduced to 20 seconds by adding a missing index.
Interview Q&A
Q: How do you identify a deadlock?
A: Using SQL Server Profiler, Extended Events, or trace flags.
Checklist
☐ Learned to read execution plans
☐ Practiced DMV queries
☐ Understood blocking & deadlocks
☐ Revised Q&A
