Renaming SQL Server Database Files: Physical Names vs. Logical Names

The Distinction

Logical Name

The name SQL Server uses internally:

SELECT name, physical_name FROM sys.database_files;

-- Output:
-- name (logical)          physical_name (on disk)
-- MyData                  C:\SQLData\MyDatabase.mdf
-- MyLog                   C:\SQLLogs\MyDatabase.ldf

The logical name is what you reference in BACKUP, RESTORE, and T-SQL. Easy to change.

Physical Name

The actual file path on disk. Hard to change because SQL Server has the file open.

When to Rename Logical Names

Scenario: Standardizing Naming Convention

You inherited a database with non-standard logical names:

-- Before (bad naming)
SELECT name, physical_name FROM sys.database_files;
-- Output:
-- db1                    C:\SQLData\database.mdf
-- logfile                C:\SQLLogs\database.ldf

-- Rename for clarity
ALTER DATABASE MyDatabase MODIFY FILE (NAME = db1, NEWNAME = MyDatabase_Data);
ALTER DATABASE MyDatabase MODIFY FILE (NAME = logfile, NEWNAME = MyDatabase_Log);

-- After
-- MyDatabase_Data        C:\SQLData\database.mdf
-- MyDatabase_Log         C:\SQLLogs\database.ldf

Note: Physical filenames stay the same. Only the logical names change.

Complete Procedure: Renaming Logical Names

-- Step 1: Check current names
SELECT name, physical_name, type_desc FROM sys.database_files;

-- Step 2: Rename logical names (no restart required)
USE master;
GO
ALTER DATABASE MyDatabase MODIFY FILE (NAME = OldLogicalName, NEWNAME = NewLogicalName);
GO

-- Step 3: Verify
SELECT name, physical_name FROM sys.database_files;

-- That's it! No restart, no file moves needed.

When to Rename Physical Names

Scenario: Moving Data Files to Faster Storage

You're migrating from slow SSD to NVMe:

-- Before
-- C:\SQLData\MyDatabase.mdf (on slow SSD)

-- Goal
-- D:\NVMe\MyDatabase.mdf (on fast NVMe)

This requires taking the database offline, moving files, and bringing it back online.

Procedure: Renaming Physical Names (Offline Method)

-- Step 1: Check current files
SELECT name, physical_name FROM sys.database_files;

-- Step 2: Take database offline
ALTER DATABASE MyDatabase SET OFFLINE;

-- Step 3: Move files on disk (using File Explorer or PowerShell)
-- Move C:\SQLData\MyDatabase.mdf → D:\NVMe\MyDatabase.mdf
-- Move C:\SQLLogs\MyDatabase.ldf → E:\NVMeLogs\MyDatabase.ldf

-- Step 4: Update SQL Server metadata
ALTER DATABASE MyDatabase MODIFY FILE (
    NAME = MyDatabase_Data,
    FILENAME = 'D:\NVMe\MyDatabase.mdf'
);

ALTER DATABASE MyDatabase MODIFY FILE (
    NAME = MyDatabase_Log,
    FILENAME = 'E:\NVMeLogs\MyDatabase.ldf'
);

-- Step 5: Bring database online
ALTER DATABASE MyDatabase SET ONLINE;

-- Step 6: Verify
SELECT name, physical_name FROM sys.database_files;

Common Mistakes

Mistake 1: Forgetting to take the database offline before moving files
Result: SQL Server still has file handles open. The move fails or corrupts the database.

Mistake 2: Moving files, but forgetting to update the metadata
Result: Database comes online but can't find the files. OFFLINE state.

Mistake 3: Not having a backup before starting
Result: If anything goes wrong, you're stuck.

Renaming vs. Moving: What's the Difference?

Operation	What Changes	Requires Offline	Risk
Rename Logical Name	Only the name in sys.database_files	No	Low
Move Physical File	File location on disk + metadata	Yes	High (if done wrong)
Both	Logical name + physical location	Yes (for physical move)	High

Advanced: Online File Move (SQL Server 2012+)

SQL Server 2012 and later support online file moves for data files (not log files):

-- Online move (no OFFLINE required for data files)
ALTER DATABASE MyDatabase
MODIFY FILE (
    NAME = MyDatabase_Data,
    FILENAME = 'D:\NVMe\MyDatabase.mdf'
);

-- Verify: File is still online
SELECT state_desc, name FROM sys.database_files WHERE type = 0;

-- SQL Server handles the move in the background
-- Monitor the move
SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%DBMOVE%';

Note: Even though the database stays online, the file move happens in the background. The database is still accessible, but I/O on that file may be slower during the move.

Renaming TempDB Files

The Special Case

TempDB is recreated on every SQL Server restart. Renaming its files is tricky:

-- Check current TempDB files
SELECT name, physical_name FROM tempdb.sys.database_files;

-- Rename logical names (temporary until restart)
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, NEWNAME = tempdev_new);

-- After restart, TempDB reverts to defaults
-- To make changes permanent, use SQL Server Configuration Manager or:

-- Modify registry (advanced, not recommended)
-- Or: Recreate TempDB entirely (even more complex)

Better approach: If you need to move TempDB to different storage, use SQL Server Setup or Configuration Manager to rebuild TempDB on the new path.

Renaming System Databases

System databases (master, model, msdb) have additional constraints:

master: Can't be taken offline. No online moves. Limited options.
model: Can be taken offline (briefly). Moves are rare and risky.
msdb: Similar to user databases, but requires rebuilding indexes/jobs afterward.

Recommendation: Plan carefully before moving system database files. Test on non-production first.

Checklist: Safe File Move

[ ] Backup database (full backup to safe location)
[ ] Document current physical and logical names
[ ] Verify target location has sufficient free space
[ ] Take database offline (if moving to new path)
[ ] Verify database is truly offline
[ ] Move files on disk (copy first, then delete old if successful)
[ ] Update SQL Server metadata (ALTER DATABASE MODIFY FILE)
[ ] Bring database online
[ ] Run DBCC CHECKDB to verify integrity
[ ] Verify backups still work (full backup + restore test)
[ ] Update any scripts/jobs that reference file paths
[ ] Update documentation

PowerShell Script: Automating Physical Move

# PowerShell script for safe file move
$DatabaseName = "MyDatabase"
$NewDataPath = "D:\NVMe\MyDatabase.mdf"
$NewLogPath = "E:\NVMeLogs\MyDatabase.ldf"

# Step 1: Backup
Invoke-Sqlcmd -Query "BACKUP DATABASE $DatabaseName TO DISK = 'C:\Backups\$DatabaseName.bak';" -ServerInstance localhost

# Step 2: Offline
Invoke-Sqlcmd -Query "ALTER DATABASE $DatabaseName SET OFFLINE;" -ServerInstance localhost

# Step 3: Move files
Copy-Item "C:\SQLData\MyDatabase.mdf" $NewDataPath
Copy-Item "C:\SQLLogs\MyDatabase.ldf" $NewLogPath

# Step 4: Update metadata
Invoke-Sqlcmd -Query "ALTER DATABASE $DatabaseName MODIFY FILE (NAME = MyDatabase_Data, FILENAME = '$NewDataPath');" -ServerInstance localhost -Database master
Invoke-Sqlcmd -Query "ALTER DATABASE $DatabaseName MODIFY FILE (NAME = MyDatabase_Log, FILENAME = '$NewLogPath');" -ServerInstance localhost -Database master

# Step 5: Online
Invoke-Sqlcmd -Query "ALTER DATABASE $DatabaseName SET ONLINE;" -ServerInstance localhost

# Step 6: Verify
Invoke-Sqlcmd -Query "SELECT name, physical_name FROM sys.database_files;" -ServerInstance localhost -Database master

Troubleshooting: Database Won't Come Online

Symptom: "Cannot find file" after ONLINE

-- The new path is wrong or the file wasn't moved
-- Check what SQL Server is looking for
SELECT name, physical_name FROM sys.database_files WHERE database_id = DB_ID('MyDatabase');

-- Verify files exist in the paths listed
-- If files are in wrong location, move them
-- Then try ALTER DATABASE ... SET ONLINE again

Symptom: Database stays SUSPECT

-- Files were corrupted during move
-- Restore from backup
RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak' WITH REPLACE;

The Verdict

Renaming logical names is easy and safe. Moving physical files requires care: backup, offline, move files, update metadata, online. Automate with scripts and test on non-production first.

Key takeaways:

Logical names: Easy to change, no downtime (ALTER DATABASE MODIFY FILE)
Physical names: Requires offline/online cycle, higher risk
Always backup before moving files
Verify with DBCC CHECKDB after move
Use PowerShell/T-SQL scripts, not manual file ops
TempDB is special; consider rebuilding instead of moving

← Back to Blog