萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

MSSQL 邏輯檔案名稱查詢、修改與資料庫檔案移動完整指令碼

邏輯檔案名稱是 SQL Server 內部識別資料/記錄檔的名稱,與實體檔名、資料庫名稱皆可不同。掌握查詢、重命名與移動檔案的 T-SQL 指令,對資料庫遷移、重構至關重要。

1. 查詢目前邏輯檔案名稱

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;

2. 修改邏輯檔案名稱

ALTER DATABASE [mydb]
MODIFY FILE (NAME = mydb,     NEWNAME = new_mydb);

ALTER DATABASE [mydb]
MODIFY FILE (NAME = mydb_log, NEWNAME = new_mydb_log);

執行後立即生效,無需重啟。

3. 移動資料/記錄檔至新路徑(離線法)

  1. 查詢目前實體路徑:
    SELECT name, physical_name FROM sys.database_files;
  2. 設為離線:
    ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE;
  3. 作業系統層級移動檔案(總管、move、robocopy 等)。
  4. 更新系統目錄路徑:
    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');
  5. 設為線上:
    ALTER DATABASE [mydb] SET ONLINE;
  6. 驗證:
    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 移動風險。

熟練邏輯檔案名稱管理與檔案遷移,讓資料庫佈署與維護更具彈性。

copyright © 萬盛學電腦網 all rights reserved