SQL Server 2000 database (1)

11 SQL Server 2000 Database ManagementAs a storage container for database objects, database management has become the basis for database object management. The following describes how to create, modify, and delete a database. 1.1.1 create a databaseThere are three methods to create a database. (1) Use the Wizard to create a database and select "start" | "program" | Microsoft SQL Server | "Enterprise Manager" to start the SQL Server Enterprise Manager. First, expand the server group and select and start the server for which you want to create the database. Open the "Tools" drop-down menu and select the "Wizard" command. The "select wizard" dialog box is displayed, as shown in Figure 4-1. Figure 4-1 select the "Create Database Wizard" option in the "select wizard" dialog box to create your own database as needed. (2) Use the Enterprise Manager to create a database to open the SQL Server Enterprise Manager, expand the server group, expand the server instance, right-click the database option, and a shortcut menu is displayed, as shown in Figure 4-2. Figure 4-2 use the Enterprise Manager to create a database and select the "create database" command in the shortcut menu to open the "database properties" dialog box, as shown in Figure 4-3. In the dialog box, enter the name of the new database. By default, the system automatically uses the database name as the prefix to create the master database file and transaction log file. Figure 4-3 "database properties" dialog box on the "General" tab, enter the database name, and then select the "data file" or "transaction log" tab, set database attributes (such as automatic file growth and maximum file size. After the settings are complete, click OK to create the database. (3) Use the create database statement in the transact-SQL statement to create a database. The syntax format is as follows: Create Database Database_name[On [primary] [< Filespec> [,... N] [, < Filegroupspec> [,... N] [log on {< Filespec> [,... N]}] [For restore] < Filespec>::= ([Name = Logical_file_name,] Filename =' OS _file_name'[, Size = Size] [, Maxsize = { Max_size| Unlimited}] [, filegrowth = Growth_increment]) [,… N] < Filegroupspec>:: = Filegroup Filegroup_name< Filespec> [,... N] the parameters are described as follows. ● Database_name: Database Name, which can contain a maximum of 128 characters. ● Primary: This option is a keyword that specifies files in the main file group. ● Log on: a clear definition of the transaction log file. ● Name: Specifies the logical name of the database. This is the name used in the SQL Server System and the identifier of the database in SQL Server. ● Filename: Specifies the name and path of the operating system file where the database is located. The logical names of the Operating System File and name correspond one to one. ● Size: Specifies the initial capacity of the database. ● Maxsize: specifies the maximum size of an operating system file. ● Filegrowth: specify the size of the file to be increased each time. If the specified data is 0, the file will not grow. For example, a student database is created. The logical name of the master data file of the database is student_data, the physical file name is student_data.mdf, the initial size is 10 MB, and the maximum size is infinite, the growth rate is 10%. The logical name of the database log file is student_log, the physical file name is student_log.ldf ', the initial size is 10 MB, the maximum size is 20 MB, and the growth speed is 2 MB. Create Database student on primary (name = student_data, filename = 'C:/program files/Microsoft SQL Server/MSSQL/data/student_data.mdf ', size = 10 MB, filegrowth = 10%) log on (name = student_log, filename = 'C:/program files/Microsoft SQL Server/MSSQL/Data student_log.ldf ', size = 10 MB, maxsize = 20, filegrowth = 2) the above three methods show that the newly created database appears in the Database List of the Enterprise Manager, as shown in 4-4. Figure 4-4 newly created database student 1.1.2 modify DatabaseAfter creating a database, you can modify its original definition. There are two ways to change the database: expand the database and contract the database. (1) The Expanded database SQL Server 2000 will automatically expand the database based on the growth parameters defined during database creation. During database expansion, the size of the database must be increased at least 1 MB. If the space allocated to the database is used up and cannot be automatically increased, a 1105 error will occur. You can use the Enterprise Manager to expand the database or the alter database statement to expand the database. When you use the Enterprise Manager to expand the database, open the SQL Server Enterprise Manager, expand the server group, and then expand the server instance of the database to be modified. Expand the "Database" folder, right-click the database to be expanded, and select the "properties" command in the pop-up shortcut menu. The "database properties" dialog box is displayed, as shown in Figure 4-5. Figure 4-5 the student attribute is displayed in the "database attributes" dialog box. You can select different tabs to expand database parameters (such as file growth and file size restrictions). After setting, click OK. The syntax of using the alter database statement to expand the database is as follows: Alter Database Databasename{Add file < Filespec> [,... N] [To filegroup Filegroupname] | Add Log File < Filespec> [,... N] | Remove File Logical_file_name[With delete] | Modify file < Filespec> | Modify name = New_databasename| Add filegroup Filegroup_name| Remove filegroup Filegroup_name| Modify filegroup Filegroup_name{ Filegroup_property| Name = New_filegroup_name} | Set < Optionspec> [,... N] [With < Termination>] | Collate < Collation_name>}Parameters are described as follows. ● Database_name: name of the database to be modified. ● Add file: Add a data file. ● To filegroup: add the data files to be added to the specified file group. ● Add Log File: Add a transaction log file. ● Remove File: deletes the specified file from the database. ● Modify file: Modify the data file. The options that can be modified include filename, size, fileg rowth, and maxsize. ● Modify name: Modify the database name. ● Add filegroup: Add a file group. ● Remove filegroup: delete a file group and all files in the group. ● Modify filegroup: Modify the name of a file group ● set: specifies when incomplete transactions are canceled when the database is changed from one state to another. ● Collate: Specifies the database sorting rules. If no sorting rule is specified, the default sorting rule of the SQL server instance is assigned as the database sorting rule. For example, to add two 5 MB log files to the database student, the creation code is as follows: Alter database studentadd log file (name = studentlog2, filename = 'C: /program files/Microsoft SQL Server/MSSQL/data/studentlog2.ldf 'size = 5 MB, maxsize = 100 MB, filegrowth = 5 MB), (name = studentlog3, filename = 'C: /program files/Microsoft SQL Server/MSSQL/data/studentlog3.ldf 'size = 5 MB, maxsize = 100 MB, filegrowth = 5 MB) (2) shrinking Database SQL Server 2000 allows you to shrink each file in the database to delete unused pages. Data and transaction log files can be automatically contracted at a specified interval. The entire contraction process is controlled and completed by the system, so the user's use of the database is not affected. There are two ways to contract a database: Use the Enterprise Manager to contract the database and use the DBCC shrink database statement to compress the database. When you use the Enterprise Manager to contract a database, open the SQL Server Enterprise Manager, expand the server group, and then expand the server instance of the database to be modified. Expand the "Database" folder, right-click the database to be shrunk, and select "all tasks"> "contract database" from the shortcut menu. The "contract database" dialog box appears, see Figure 4-6. Figure 4-6 shrink database-student in the "contract database" dialog box, set the database contraction parameters. In the "maximum available space in the compressed file" text box, you can enter the proportion of the remaining space in the database after the contraction. Select the "Move the page to the start position of the file before the contraction" check box, the released file space can be retained in the database file, and the page containing data can also be moved to the starting position of the database file. After selecting the check box "shrink Database Based on this schedule, click the "change" button to create or change the frequency and time of automatically shrinking the database. Click the "file" button to contract individual database files. After setting, click OK. Use the DBCC shrinkdatabase statement to compress the database syntax in the following format: DBCC shrinkdatabse ( Database_name[, Target_percent] [, {Notruncate | runcateonly}]) the parameters are described as follows. ● Database_name: name of the database to be compressed. ● Target_percent: indicates the percentage of free space of the database in the whole size after compression. ● Notruncate: indicates that the released file space is retained in the database file. ● Truncateonly: identifies releasing unused space in the data file to the operating system. This is the default situation. It should be noted that compression is limited to the growth of the database, and the size of the compressed database cannot be smaller than the size of the created database. Therefore, if the size of the database is 10 MB and later increases to 100 MB, the minimum size of the database can be reduced to 10 MB (assuming that all data in the database has been deleted ). 1.1.3 delete a databaseYou can delete a database if it is no longer needed, or if it is moved to another database or server. After the database is deleted, all files and their data are deleted from the disk on the server. Once a database is deleted, it is permanently deleted and cannot be searched unless the previous backup is used. You can delete a database in two ways. (1) Use the Enterprise Manager to delete the database to open the SQL Server Enterprise Manager, expand the server group, and then expand the server instance of the database to be modified. Expand the "Database" folder, right-click the database to be deleted, and select "all tasks"> "delete" from the shortcut menu to confirm deletion. (2) You can use the drop statement in a Transact-SQL statement to delete one or more databases from SQL Server. The syntax is as follows: DROP DATABASE Database_name[,... N] where, Database_nameSpecifies the name of the database to be deleted. For example, the syntax for deleting a student database is as follows: drop database student can also be used to delete multiple databases at the same time. You only need to separate the names of the databases to be deleted with commas.