ALTER DATABASE database_name
MODIFY NAME = new_database_name;
EXEC sp_renamedb 'olddbname', 'newdbname';
USE master;
GO
DECLARE @data_path NVARCHAR(256);
SELECT @data_path = physical_name
FROM MyDatabase.sys.database_files
WHERE file_id = 1;
SET @data_path = SUBSTRING(@data_path, 1,
CHARINDEX(LOWER('MyDatabase.mdf'), LOWER(@data_path)) - 1);
ALTER DATABASE MyDatabase
ADD FILE (
NAME = LogicNameOfDataFile1_1,
FILENAME = @data_path + 'DataFile1_1.ndf',
SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB
);
ALTER DATABASE MyDatabase
ADD LOG FILE (
NAME = LogicNameOfLogFile1_1,
FILENAME = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/LogFile1_1.ldf',
SIZE = 2MB, MAXSIZE = 50MB, FILEGROWTH = 3MB
);
ALTER DATABASE MyDatabase ADD FILEGROUP UserFG1_1;
GO
ALTER DATABASE MyDatabase
ADD FILE (
NAME = LogicNameOfDataFile1_2,
FILENAME = 'D:/Data/DataFile1_2.ndf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
),
(
NAME = LogicNameOfDataFile1_3,
FILENAME = 'D:/Data/DataFile1_3.ndf',
SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 2MB
)
TO FILEGROUP UserFG1_1;
提示:未指定
TO FILEGROUP時,檔案預設加入PRIMARY檔案群組。
ALTER DATABASE MyDatabase2
MODIFY FILE (
NAME = LogicNameOfDataFile2,
NEWNAME = newLogicNameOfDataFile2,
FILENAME = 'C:/NewPath/newDataFile2.mdf',
SIZE = 25MB, MAXSIZE = 150MB, FILEGROWTH = 10MB
);
注意:修改
FILENAME需先將資料庫設為離線,實體移動檔案後再上線,或重啟服務生效。
ALTER DATABASE MyDatabase3
MODIFY FILE (
NAME = MyDatabase3_log,
FILENAME = 'C:/NewPath/LogFile3.LDF',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
);
ALTER DATABASE MyDatabase6
MODIFY FILEGROUP UserFG6_2 NAME = newUserFG6_2;
ALTER DATABASE MyDatabase6 MODIFY FILEGROUP newUserFG6_2 DEFAULT;
GO
-- 切回 PRIMARY
ALTER DATABASE MyDatabase6 MODIFY FILEGROUP [PRIMARY] DEFAULT;
ALTER DATABASE ... SET OFFLINE)CREATE TABLE 無指定群組時的放置位置MODIFY FILE 不支援 NEWNAME,僅能改路徑/大小/成長ALTER DATABASE 提供彈性的儲存架構調整能力。生產環境操作前請完整測試、備份並安排維護窗口,確保資料安全與服務連續性。