T-SQL入門攻略之10-修改数据库alter database

seloba

--> Title : T-SQL入門攻略之10-修改数据库alter database
--> Author : wufeng4552
--> Date : 2010-05-21
更改数据库名
(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