更改數據庫名
(1)
alter database database_name
modify name=new_database_name
(2)
sp_renamedb olddbname,newdbname
添加數據文件和文件組(擴大)
(1)添加數據文件
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);
-- 添加數據文件(放在主數據文件所在的目錄下)
go
ALTER DATABASE MyDatabase
ADD FILE
(
NAME = LogicNameOfDataFile1_1,
FILENAME = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/DataFile1_1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
(2)添加日志文件
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);
--添加數據文件(放在主數據文件所在的目錄下)
go
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
)
(3)添加文件及文件組
USE master
GO
-- 創建文件組_1
ALTER DATABASE MyDatabase
ADD FILEGROUP UserFG1_1;
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);
--添加數據文件(放在主數據文件所在的目錄下)
Go
ALTER DATABASE MyDatabase
ADD FILE
(
NAME = LogicNameOfDataFile1_2,
FILENAME = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/DataFile1_2.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB
)，
(
NAME = LogicNameOfDataFile1_3,
FILENAME = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/DataFile1_3.ndf',
SIZE = 5MB,
MAXSIZE = 50MB,
FILEGROWTH = 2MB
)
TO FILEGROUP UserFG1_1
注：以上添加數據文件時如果不指定所在文件組那麼將被默認添加到主文件組中
更改數據文件和文件組
(1)更改數據庫的數據文件
USE master
GO
ALTER DATABASE MyDatabase2
MODIFY FILE
(
NAME = LogicNameOfDataFile2,
NEWNAME = newLogicNameOfDataFile2,
FILENAME
='C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/newDataFile2.mdf',
SIZE = 25MB,
MAXSIZE = 150MB,
FILEGROWTH = 10MB
)
(2)更改數據庫的日志文件
USE master
GO
ALTER DATABASE MyDatabase3
MODIFY FILE
(
NAME = MyDatabase3_log,
FILENAME
='C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/LogFile3.LDF',
SIZE = 10MB , -- 設置初始大小
MAXSIZE = 50MB, -- 設置文件的最大存儲空間
FILEGROWTH = 5MB -- 設置自動增長幅度
)
(3)更改文件組名稱
USE master
GO
ALTER DATABASE MyDatabase6
MODIFY FILEGROUP UserFG6_2 NAME = newUserFG6_2
GO
(4)更改默認文件組
USE master;
GO
ALTER DATABASE MyDatabase6
MODIFY FILEGROUP newUserFG6_2 DEFAULT;
GO
ALTER DATABASE MyDatabase6
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO
