Lesson 7: Configure File Locations — Data / Log / tempdb Separation (Best Practice)

Section 5 — Lesson 7: Configure File Locations — Data / Log / tempdb Separation (Best Practice)

Welcome back. You now have SQL Server and SSMS installed. Next, you will configure one of the most important
“DBA foundations” that prevents performance and operational problems later:
clean, intentional file locations.

Many SQL Server environments run poorly because everything is thrown onto the C: drive with default paths.
In real DBA work, you plan where your data files, log files, tempdb files, and backups live—because they have
different I/O patterns and different risk profiles.

---

Lesson description: You will learn what SQL Server data, log, tempdb, and backup files do,
why their I/O patterns differ, and why DBAs separate them.
You will build a clean folder layout for your lab, configure SQL Server default paths,
move tempdb to the correct location safely, and validate that new databases and tempdb are using your intended directories.
You will finish with a checklist you can reuse for future installations.

---

1) What These Files Are (DBA Definitions)

1A) Data files (.mdf, .ndf)

Data files store the actual table and index pages. They experience mixed reads and writes and can be random I/O heavy.

1B) Log files (.ldf)

Transaction log files store the record of changes. Log writes are mostly sequential.
Log performance is extremely sensitive to storage latency.

1C) tempdb files (data + log)

tempdb is a shared system database used for:
sorts, hash joins, temp tables, table variables, row versioning, and internal worktables.
tempdb can become a major bottleneck under load.

1D) Backup files (.bak, .trn)

Backup files are your recovery lifeline. Their location matters for restore speed and for protecting data from loss.

---

2) Why DBAs Separate Data / Log / tempdb

DBAs separate file locations to achieve:

• Performance: reduce I/O contention between different access patterns
• Stability: avoid tempdb growth choking the OS drive
• Recoverability: keep backups organized and protected
• Predictability: standard paths make automation and troubleshooting easier

DBA reality: In production, these often map to separate disks or storage tiers.
In your lab, you can still separate by folders (and optionally separate virtual disks if you want advanced realism).

---

3) Lab Implementation Options (Choose Your Level)

Option A (Simple, recommended): Separate folders on one disk

This is easiest and still teaches correct DBA habits.

Option B (More realistic): Separate virtual disks

You can add additional virtual disks to your VM and assign them drive letters (D:, E:, F:).
This is closer to production design but requires more setup.

Course recommendation: Start with Option A.
If you want to challenge yourself later, upgrade to Option B.

---

4) Create a Standard Folder Layout (Do This First)

Create these folders (adjust drive letters if you have multiple disks).
If you only have C:, this layout is still valuable:

• C:\SQLData\ — user database data files
• C:\SQLLog\ — user database log files
• C:\SQLTempDB\ — tempdb data and log files
• C:\SQLBackups\ — backup files

DBA note: If you created C:\DBA\ earlier, you can also place these under a consistent structure.
But for realism, DBAs often keep SQL file locations separate from “documentation folders.”

---

5) Configure SQL Server Default Paths (So New Databases Go to the Right Place)

SQL Server has “default” locations used when you create new databases without specifying paths explicitly.
You want defaults to be correct so you don’t accidentally create databases on the wrong drive.

5A) Preferred (GUI): SSMS Server Properties

1. Open SSMS and connect to your instance.
2. Right-click the server name → Properties.
3. Go to Database Settings.
4. Set:
   • Data default location → C:\SQLData\
   • Log default location → C:\SQLLog\
   • Backup default location → C:\SQLBackups\
5. Click OK.

DBA note: Some changes may require service restart to fully apply.

5B) Evidence query: confirm default paths

SELECT
    SERVERPROPERTY('InstanceDefaultDataPath')   AS InstanceDefaultDataPath,
    SERVERPROPERTY('InstanceDefaultLogPath')    AS InstanceDefaultLogPath;

Backup default path is not surfaced the same way in all versions,
so you will validate backup behavior by creating a test backup in the next practice step.

---

6) Move tempdb (The Correct DBA Way)

tempdb is recreated on startup. That makes it “safe” to move by updating file paths and restarting SQL Server.
You do not copy tempdb files manually while SQL Server is running.

6A) Step 1: Check current tempdb file locations

USE tempdb;
GO
SELECT
    name,
    type_desc,
    physical_name
FROM sys.database_files
ORDER BY type_desc, name;

6B) Step 2: Change tempdb file paths

Adjust the file names if your instance uses different naming.
These names are common defaults:
tempdev (data) and templog (log).

USE master;
GO

-- Move tempdb data file
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\SQLTempDB\tempdb.mdf');
GO

-- Move tempdb log file
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'C:\SQLTempDB\templog.ldf');
GO

6C) Step 3: Restart SQL Server service

Restart the SQL Server service (not just SSMS).
You can do it via:

• SQL Server Configuration Manager (recommended)
• Services.msc (acceptable in lab)

After restart, tempdb will be recreated in the new location.

6D) Step 4: Validate tempdb is in the new location

USE tempdb;
GO
SELECT
    name,
    type_desc,
    physical_name
FROM sys.database_files
ORDER BY type_desc, name;

If you see C:\SQLTempDB\ paths, you succeeded.

6E) Common issue: SQL Server fails to start after tempdb move

The #1 cause is missing folder permissions or folder not existing.
Fix by confirming:

• C:\SQLTempDB\ exists
• SQL Server service account has access (in lab, defaults usually work if folder exists)

DBA rule: Create directories first, then change SQL configuration, then restart.

---

7) Validate New Database Uses Your Default Paths

7A) Create a small test database (no explicit paths)

CREATE DATABASE DBALab_TestPaths;
GO

7B) Check where the files were created

SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalName,
    type_desc,
    physical_name
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DBALab_TestPaths'
ORDER BY type_desc, name;

You should see data files in C:\SQLData\ and log files in C:\SQLLog\.

7C) Clean up the test database (safe removal)

DROP DATABASE DBALab_TestPaths;
GO

---

8) Validate Backup Location Behavior

Run a test backup and confirm the file lands in your backup directory:

-- Create a small test DB for backup validation
CREATE DATABASE DBALab_BackupTest;
GO

BACKUP DATABASE DBALab_BackupTest
TO DISK = 'C:\SQLBackups\DBALab_BackupTest_FULL.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 5;
GO

DROP DATABASE DBALab_BackupTest;
GO

Confirm the backup file exists at:
C:\SQLBackups\DBALab_BackupTest_FULL.bak

DBA habit: Always include CHECKSUM and prefer COMPRESSION when available.

---

9) Best Practice Notes (Important, Even for Labs)

9A) Don’t store SQL data and logs on the OS drive in production

In production, the OS drive must remain stable even if databases grow unexpectedly.
In a lab, you may be limited to one disk, but the habit of separation is still correct.

9B) tempdb deserves special attention

Many performance incidents are tempdb incidents.
DBAs plan tempdb storage and file configuration intentionally.
(You will learn advanced tempdb tuning later.)

9C) Backups must be organized and protected

In real environments, backups should also be copied off the server.
A backup stored on the same server is not a disaster recovery plan.

---

Practice (Required Deliverables)

1. Create the directories: C:\SQLData\, C:\SQLLog\, C:\SQLTempDB\, C:\SQLBackups\
2. Set SQL Server default Data/Log/Backup locations via SSMS Server Properties.
3. Move tempdb to C:\SQLTempDB\ using ALTER DATABASE and restart SQL Server.
4. Validate tempdb file paths after restart.
5. Create a test database and confirm its files land in the correct folders.
6. Create a test backup and confirm the backup file is created in your backup folder.
7. Save proof output (query results) into: C:\DBA\Logs\file-path-validation.txt

---

Lesson Summary

• Data, log, tempdb, and backups have different I/O patterns and risk profiles.
• DBAs separate them to reduce contention, protect the OS, and improve predictability.
• Configure default paths so new databases do not accidentally land in the wrong place.
• Move tempdb safely by changing file paths and restarting SQL Server.
• Always validate with evidence queries and keep outputs as proof.

---

Next Lesson: Lab — Build a “Clean Install Checklist” Students Can Reuse

Next, you will combine everything from this section into a clean, professional installation checklist
that you can reuse for future servers and show in interviews as proof of operational discipline.