邏輯檔案名稱是 SQL Server 內部識別資料/記錄檔的名稱,與實體檔名、資料庫名稱皆可不同。掌握查詢、重命名與移動檔案的 T-SQL 指令,對資料庫遷移、重構至關重要。
USE mydb;
GO
-- 方法 1:FILE_NAME(file_id)
SELECT FILE_NAME(1) AS 'Data_Logical_Name', FILE_NAME(2) AS 'Log_Logical_Name';
-- 方法 2:sys.database_files(含實體路徑)
SELECT name AS LogicalName, physical_name AS PhysicalPath, type_desc
FROM sys.database_files;
ALTER DATABASE [mydb]
MODIFY FILE (NAME = mydb, NEWNAME = new_mydb);
ALTER DATABASE [mydb]
MODIFY FILE (NAME = mydb_log, NEWNAME = new_mydb_log);
執行後立即生效,無需重啟。
SELECT name, physical_name FROM sys.database_files;
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE;
move、robocopy 等)。ALTER DATABASE [mydb]
MODIFY FILE (NAME = new_mydb, FILENAME = 'D:\NewPath\new_mydb.mdf');
ALTER DATABASE [mydb]
MODIFY FILE (NAME = new_mydb_log, FILENAME = 'D:\NewPath\new_mydb_log.ldf');
ALTER DATABASE [mydb] SET ONLINE;
SELECT name, physical_name FROM sys.database_files;
USE master;
GO
DECLARE @DBName SYSNAME = 'mydb',
@NewDataPath NVARCHAR(260) = 'D:\SQLData\',
@NewLogPath NVARCHAR(260) = 'D:\SQLLog\';
-- 1. 強制中斷連線並離線
ALTER DATABASE [@DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [@DBName] SET OFFLINE;
-- 2. 取得邏輯名與實體檔名
DECLARE @DataLogical SYSNAME, @LogLogical SYSNAME,
@DataPhysical NVARCHAR(260), @LogPhysical NVARCHAR(260);
SELECT @DataLogical = name, @DataPhysical = physical_name
FROM sys.master_files WHERE database_id = DB_ID(@DBName) AND type = 0;
SELECT @LogLogical = name, @LogPhysical = physical_name
FROM sys.master_files WHERE database_id = DB_ID(@DBName) AND type = 1;
-- 3. OS 移動檔案(需啟用 xp_cmdshell)
EXEC xp_cmdshell 'MOVE "' + @DataPhysical + '" "' + @NewDataPath + '"';
EXEC xp_cmdshell 'MOVE "' + @LogPhysical + '" "' + @NewLogPath + '"';
-- 4. 更新路徑
DECLARE @DataFile NVARCHAR(260) = @NewDataPath + RIGHT(@DataPhysical, CHARINDEX('\', REVERSE(@DataPhysical)) - 1);
DECLARE @LogFile NVARCHAR(260) = @NewLogPath + RIGHT(@LogPhysical, CHARINDEX('\', REVERSE(@LogPhysical)) - 1);
ALTER DATABASE [@DBName] MODIFY FILE (NAME = @DataLogical, FILENAME = @DataFile);
ALTER DATABASE [@DBName] MODIFY FILE (NAME = @LogLogical, FILENAME = @LogFile);
-- 5. 上線
ALTER DATABASE [@DBName] SET MULTI_USER;
ALTER DATABASE [@DBName] SET ONLINE;
GO
xp_cmdshell 預設關閉,需 sp_configure 'xp_cmdshell', 1; RECONFIGURE 啟用,用畢建議關閉。BACKUP/RESTORE WITH MOVE 或 ALTER DATABASE ... MODIFY FILE + 計劃性停機視窗,避免直接 OS 移動風險。熟練邏輯檔案名稱管理與檔案遷移,讓資料庫佈署與維護更具彈性。