| 标题 | T-SQL入門攻略之修改数据库alter database |
| 内容 | 更改数据库名 (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 |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。