Week 1 – SQL DBA In-Depth Study
Guide
Comprehensive 7-Day Study Plan with Concepts, Syntax, Hands-on Labs, Real-Time
Scenarios, Interview Q&A, Checklists, and Quick Revision Sheet.
Prepared for: Cloud & Database Administrator (3 Yrs Experience)
Focus: SQL Server DBA Fundamentals + Interview Preparation
Table of Contents
1. Day 1–2: SQL Backup & Restore
2. Day 3: Performance Tuning
3. Day 4: High Availability & Disaster Recovery (HA/DR)
4. Day 5: SQL Security
5. Day 6: Real-Time Scenarios
6. Day 7: Revision & Mock Q&A
7. Quick Revision Sheet
Day 1–2: SQL Backup & Restore
Definition & Concept
Backups are the foundation of disaster recovery in SQL Server. They ensure that in the
event of hardware failure, corruption, or accidental data deletion, the database can be
restored to a consistent state.
Types of backups:
- Full Backup: Captures the entire database.
- Differential Backup: Captures only changes since the last full backup.
- Transaction Log Backup: Captures all transactions since the last log backup, allowing
point-in-time recovery.
Syntax with Explanation
```sql
-- Full Backup
BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB_Full.bak' WITH INIT, STATS=10;
-- Differential Backup
BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB_Diff.bak' WITH DIFFERENTIAL,
STATS=10;
-- Transaction Log Backup
BACKUP LOG MyDB TO DISK = 'C:\Backups\MyDB_Log.trn' WITH INIT, STATS=10;
```
- `WITH INIT` overwrites existing file.
- `STATS=10` shows progress every 10%.
- Transaction log backups require the database to be in FULL or BULK-LOGGED recovery
model.
Hands-on Task
1. Create a test database.
2. Take a full backup.
3. Insert some data and take a differential backup.
4. Insert more data and take a log backup.
5. Restore database to a point in time between transactions.
Real-Time Scenario
📌 A production DB crashed at 10:30 AM. Available backups:
- Full backup at 1 AM
- Differential backup at 9 AM
- Log backups every 15 mins
➡ Recovery steps:
1. Restore Full backup WITH NORECOVERY.
2. Restore Differential WITH NORECOVERY.
3. Restore all Log backups sequentially until 10:15 AM WITH NORECOVERY.
4. Finally, restore the last Log WITH STOPAT = '2023-09-18 10:15:00' and WITH
RECOVERY.
Interview Q&A
Q: What’s the difference between NORECOVERY and RECOVERY?
A: NORECOVERY keeps the DB in restoring mode for applying further backups. RECOVERY
brings the DB online.
Q: Can you restore a Differential without a Full backup?
A: No. Differential backups depend on the last Full backup.
Checklist
☐ Understood backup types
☐ Practiced syntax
☐ Completed hands-on
☐ Solved real-time scenario
☐ Revised interview Q&A
Quick Revision Sheet – Week 1 SQL DBA
- Full Backup → Entire DB.
- Differential → Changes since last full.
- Log Backup → Needed for PIT recovery.
- NORECOVERY → Keep DB restoring.
- RECOVERY → Bring DB online.
- Execution Plans → Identify slow queries.
- Blocking vs Deadlocks → Blocking = wait, Deadlock = mutual block.
- Always-On AG → HA with replicas.
- Log Shipping → Async DR.
- TDE → Encrypts DB at rest.
- Suspect DB → Use EMERGENCY + DBCC CHECKDB.
