T-SQL Entry strategy modify database Alter DB

Change database name

(1)

ALTER DATABASE database_name

Modify Name=new_database_name

(2)

Sp_renamedb Olddbname,newdbname

Add data files and filegroups (expand)

(1) Add data file

Use master

Go

DECLARE @data_path nvarchar (256);

--Get the location of the master data file

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);

--Add data files (placed in the same directory as the main data file)

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) Adding log files

Use master

Go

DECLARE @data_path nvarchar (256);

--Get the location of the master data file

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);

--Add data files (placed in the same directory as the main data file)

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) Add files and Filegroups

Use master

Go

--Create Filegroup _1

ALTER DATABASE MyDatabase

ADD FILEGROUP userfg1_1;

Go

DECLARE @data_path nvarchar (256);

--Get the location of the master data file

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);

--Add data files (placed in the same directory as the main data file)

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

Note: If you do not specify a filegroup when you add the data file, it will be added to the primary filegroup by default

change data files and filegroups

(1) Change the data file of the database

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) Change the log file of the database

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,--Set Initial size

MAXSIZE = 50MB,--set maximum storage space for a file

FileGrowth = 5MB--Set the automatic growth rate

)

(3) Change the filegroup name

Use master

Go

ALTER DATABASE MyDatabase6

MODIFY FILEGROUP userfg6_2 NAME = Newuserfg6_2

Go

(4) Change the default file group

Use master;

Go

ALTER DATABASE MyDatabase6

MODIFY FILEGROUP Newuserfg6_2 DEFAULT;

Go

ALTER DATABASE MyDatabase6

MODIFY FILEGROUP [PRIMARY] DEFAULT;

Go