DB1: Creation of databases and modification of files

In SQL Server, you create a database by using the ALTER DATABASE command, which enables you to modify the database's data files and log files.

One, create a database

1, when creating a database, the best practices are:

• Create one or more filegroups and set the default file groups
• The data files in each filegroup are the same as the kernel data of the CPU, distributing the files evenly on different physical hard disks, so that the IO is evenly distributed on different physical disks;
• The initial size of the data file, the file growth and maximum size are consistent, so that each file can be relatively uniform IO times;
• Log file allocation on the best performance of the physical hard disk, write log performance directly affect the performance of data modification;
• When creating a file, allocate a large enough initial space for each file to avoid the growth of the data file size

Example of creating a database:

Create DATABASE Newdbon Primary (name= ' newdb ',  filename= ' D:\MSSQLServer\newdb.mdf ',  SIZE=1GB,MAXSIZE=10GB, FILEGROWTH=1GB), (name= ' newdb_1 ', filename= ' D:\MSSQLServer\newdb_1.ndf ', SIZE=1GB,MAXSIZE=10GB,FILEGROWTH=1GB), ( Name= ' newdb_2 ', filename= ' D:\MSSQLServer\newdb_2.ndf ', SIZE=1GB,MAXSIZE=10GB,FILEGROWTH=1GB), (name= ' newdb_3 ', Filename= ' D:\MSSQLServer\newdb_3.ndf ', SIZE=1GB,MAXSIZE=10GB,FILEGROWTH=1GB), (name= ' newdb_4 ', filename= ' d:\ Mssqlserver\newdb_4.ndf ', SIZE=1GB,MAXSIZE=10GB,FILEGROWTH=1GB) log on (name= ' Newdb_log ', filename= ' D:\MSSQLServer\ Newdb_log.ldf ', SIZE=1GB,MAXSIZE=10GB,FILEGROWTH=1GB) go

The parameter name specifies the logical file name (logical_file_name), the parameter filename specifies the physical file name, and SQL Server references the data file or log file through the logical file name.

2. View the files of the current database

Use Newdbgoselect df.file_id,    df.type,    df.type_desc,    df.name,    df.physical_name,    df.state,    Df.state_desc,    df.size*8/1024/1024 as SIZE_GB,    df.max_size*8/1024/1024 as MAX_SIZE_GB,    df.growth,    df.is_percent_growth,    fg.name as filegroup_name,    fg.is_defaultfrom sys.database_files DF INNER JOIN Sys.filegroups FG on    df.data_space_id=fg.data_space_id

3. View files for all databases

Select     db_name (mf.database_id) as dbname,    mf.file_id,    mf.type as File_type,    Mf.type_desc,    mf.data_space_id as File_group,    mf.name as Logical_file_name,    mf.physical_name,    Mf.state_desc ,    mf.size*8/1024/1024 as SIZE_GB,    mf.max_size*8/1024/1024 as MAX_SIZE_GB,    mf.growth,    mf.is_ Percent_growthfrom sys.master_files MF--where database_id=db_id (' newdb ')

Second, add data/log files

1, increase the filegroup (file group)

Use master go ALTER DATABASE NEWDBADD filegroup FG_NEWDB1;

2. Add data files to the FG_NEWDB1 filegroup (file group)

ALTER DATABASE Newdbadd file (name=fg_newdb1_1,filename = ' d:\MSSQLServer\fg_newdb1_1.ndf ', SIZE=1GB,MAXSIZE=10GB, FILEGROWTH=1GB) to filegroup fg_newdb1;

3. Add log files

ALTER DATABASE Newdbadd log file (name=newdb_log_1,filename = ' d:\MSSQLServer\newdb_log_1.ldf ', SIZE=1GB,MAXSIZE=10GB, FILEGROWTH=1GB);

Third, modify the database file

1. Modify the logical file name of the data files (Logical file name)

example, modify the logical file name of the data file Fg_newdb1_1 fg_newdb1_file1

ALTER DATABASE newdbmodify file (NAME = ' fg_newdb1_1 ', newname= ' fg_newdb1_file1 ')

2. Modify the physical file name of the data files

MODIFY FILE (NAME = logical_file_name, FILENAME = ' new_path/os_file_name ')

Modifying the physical file names of the data files to move the data files to different locations is implemented in four steps:

Step1, registering a new physical file name for data files in the database

ALTER DATABASE Newdbmodify file (name= ' fg_newdb1_file1 ', filename= ' F:\MSSQLServer\fg_newdb1_file1.ndf ');

After the statement executes successfully, the database displays: the physical file name has been modified in the system directory

The file "Fg_newdb1_file1" has a been modified in the system catalog. The new path would be used the next time, the database is started.

Step2, make the database offline

Use Mastergo ALTER DATABASE newdbset offlinewith rollback immediate;

When the database is taken offline (offline, take offline), if there is a sleeping session in the data, or if there is a query executing, then the take offline operation waits until there is no query run, using ROLLBACK The IMMEDIATE option enables immediate rollback of all transactions being performed in the database, allowing the take Offline operation to be completed quickly.

Step3, move the data file to a new location

Run the move command with administrator privileges to move the file to a new location and rename it to a new physical file name

Move D:\MSSQLSERVER\FG_NEWDB1_1.NDF F:\MSSQLSERVER\FG_NEWDB1_FILE1.NDF

STEP4, bringing the database online

Use Mastergo ALTER DATABASE Newdbset online

Four, remove the data file from the database

In SQL Server, whether it is a log file or a data file, it must be emptied before it is deleted, and only empty files can be deleted from the database.

1. Delete the data file from the database

Step1, emptying the data file

Use [db_study]godbcc shrinkfile (N ' db_study_file1 ', emptyfile) GO

STEP2, deleting data files from the database

Use master; Goalter DATABASE db_studyremove FILE db_study_file1; GO

2. Delete log files from the database

Step1, emptying the log file

Use [db_study]godbcc shrinkfile (N ' db_study_log1 ', emptyfile) GO

Step2, deleting log files from the database

Use master; Goalter DATABASE db_studyremove FILE db_study_log1; GO

3. Delete filegroups from the database

Use master; Goalter DATABASE db_studyremove FILEGROUP fg_study1; GO

DB1: Creation of databases and modification of files