Ms SQL Server 2000 administrator manual series-9. database creation

9. Create a database
Database Structure
System Database
Create a database
View Database
Delete Database
Summary
After you design and install the Microsoft SQL Server 2000 database, you can learn how to create a database. Compared with earlier versions, SQL Server 2000 databases use SQL Server 7 data storage and space management methods. Earlier versions use logical devices and fixed-size data segments to place data. SQL Server 2000 allows users to choose, whether to allow an archive or archive group to automatically grow or contract according to the amount of data. This chapter describes archives, archive groups, and how to manage the growth of databases. This chapter also describes three database creation methods and how to view and delete databases.
Database Structure
　
Each database is composed of a group of system archives, which can be combined into a group of archives. Archive groups facilitate data management and classification and improve system execution efficiency. This section describes the archives and archive groups and their roles in the database.
Archives
　
As mentioned earlier, databases are composed of a group of system files. System files can also be divided into data files and log files ). Data files are used to store data and objects (such as data tables, indexes, views, triggers, and pre-stored programs). Data files are classified into primary data files and secondary data files by type. The record files are only used to store data about transaction records. The record file space is separated from the data file space, and cannot be regarded as a part of the data file.
Each database must contain at least one data file and one record file, which cannot be shared with other databases. That is to say, the data and record files of this database can only belong to this database. Let's take a look at the three types of database archives.
• As the name suggests, the main data file contains a major data file of the system, such as the system database startup information and system data tables and objects. The main data files can also store user-defined data tables and objects. Each database can have only one (and must have one) primary data file. The recommended file extension is. MDF.
　
• Secondary Data Files secondary data files can store data and objects that are not in the primary data files. A database does not have to have secondary data files (different from primary data files). You can store all the data in the primary data files. Some databases use one or more secondary data files to distribute data on different disks. (This method is different from the raid we will discuss in the next section). The recommended extension for secondary data files is. NDF.
　
• The transaction record file records all transaction records, which can be used to restore database data. Each database must have at least one transaction record file. The recommended extension is. LDF.
　
________________________________________
Description
For an SQL server resource, the maximum size of the data file is 32 TB, and the record file is 4 TB.
________________________________________
In a simple database, a major data file can store all the data, objects, and transaction records. A complex database may contain a primary data file, five secondary data files, and two transaction record files. How should such a database integrate the data scattered in various data files? The answer is: Use archive groups.
Archive Group
　
Archive groups allow you to group archives for management and storage (similar to data segments of earlier versions of SQL Server ). Archive groups distribute databases on multiple disks, disk control cards, or disk arrays (RAID discussed in chapter 5) to improve database execution efficiency. Archive groups can be used to create data tables and indexes on a specified disk. That is to say, you can specify a physical disk, disk controller, or disk array for a data table or index I/O. There are several sample operations in the later section.
Archive groups can be divided into three types:
• The main archive groups include the primary data files and files not placed in other archive groups. System Data Tables-Define User, object, and database permissions-are all placed in the main archive group. When you create a database, SQL Server automatically creates a system data table.
　
• User-Defined archive groups are user-defined archive groups during database creation. You can create data tables and indexes in a user-defined archive group.
　
• If the archive group is not specified when the data table and index are created, all data tables and index pages are placed in the default archive group. In each database, only one archive group can be used as a preset archive group at a time. If no preset archive group is specified, the default value is the primary archive group. Users who are members of the db_owner role can switch their preset status from one archive group to another. At the same time, there can only be one preset archive group. We can use the alter database command to change the archive group. The following is the syntax of transact-SQL:
　
Alter database database_name modify filegroup filegroup_name
Default
(You will learn how to use T-SQL in article 3 ). You can set a preset archive group to a user-defined group, so that the objects you create in the database can be automatically created in the specified archive group, you do not need to specify the archive group to which the object belongs each time.
Creating data tables and indexes in different archival groups can control data storage locations and improve database efficiency. For example, you can place commonly used data tables in a large disk array (for example, a raid consisting of 10 disks ), data tables that are rarely used are placed in a small raid consisting of four disks. Therefore, frequently accessed data tables are allocated to multiple disks, allowing more disk I/O operations in parallel. If you have multiple disks but do not use raid, you can still leverage the archive group. For example, you can create an archive on your disk and put the archive into your custom archive group. In this way, when you create a data table or index to specify an archive group, the created data table and index will be placed in the specified archive (and the disk where the archive group is located ). Figure 9-1 shows an example of this architecture: the primary data files are stored in the primary archive group on disk C, the secondary files are placed in the User-Defined FG1 and FG2 file groups on disk E and disk F respectively, and the record files are placed on disk G. Then you can create data tables and indexes in the User-Defined archive group-FG1 or FG2.

　
 
Figure 9-1 use archive groups to control data locations
Alternatively, you can use User-Defined archive groups to distribute data across several disks. Figure 9-2 shows secondary data files (record files on disk g and primary data files on disk C) in the User-Defined file group (FG1) in disk E and F respectively ). In this example, we assume that each database file is created on a single physical disk (raid is not used) and the SQL Server proportional write mode is used, data Tables and indexes created in user-defined archive groups can be distributed on two different disks.

　
 
Figure 9-2 use an archive group to distribute data to multiple disks
If you are using a RAID system, you may need to distribute large data tables to two or more Logical Disk Arrays controlled by raid controllers. You can set up a user-defined archive group. An archive exists in each control card. Assume that you create a secondary data file on two different disk arrays. Each logical array consists of eight physical disks and is set to RAID 5, the two arrays are on different raid control cards. To create a data table or index across the control card (equal to 16 disks), you must first define a user-defined archive group to hold the two files, create a data table or index in the archive group. Figure 9-3 shows that the user-defined file group (FG1) is scattered across sixteen physical disks (or two logical raid disk arrays ). The figure also shows that the main data file is on another controller (raid1), and the record file is on another controller (raid10.
SQL Server can automatically add data files to several disks in proportion to each data file to provide optimal automatic file configuration. "Striping" is a database term and is stored in more than one database file in an exponential manner. The stripping method of the SQL Server File is independent of the raid disk's equal sector. It can be used independently or mixed with raid.

　
 
Figure 9-3 user-defined archive groups are distributed across several raid control cards
To perform stripping on data files, SQL Server compares the available space of each file, then, the data is written to each file in proportion (instead of writing all the data to the first file, and then write the data to the next file after the data is fully written ). The data table and index space is configured according to the extend. A range is the basic unit for configuring space for data tables and indexes. A range contains eight pages, each of which is 8 KB. Therefore, a range is measured in 64kb. For example, if the archive F1 has 400 mb of available space, and the archive F2 has 100 MB of available space, if you want to write 5 range-based filling policies into the archive, then four ranges are allocated to F1 and one range is allocated to F2. As a result, these two files will be filled up at roughly the same time, making the cross-disk allocation of I/O performance better. Both F1 and F2 files belong to the User-Defined archive group and the main archive group, data is written separately according to the proportion. If all the archives in the archive group have the same starting size, the data is evenly configured to each archive when the data is loaded. We recommend that you set the start size of all files in the archive group to the same, so that the data is evenly configured across disks to achieve the I/O average allocation.
Another benefit of archive groups is that databases can be backed up based on archives or archive groups. If your database is too large to back up all the content at a time, you can back up the archives or archive groups in the database without backing up the entire database at a time. Chapter 1 describes how to use archive groups for partial backup.
Rules and Suggestions
　
When using archives and archive groups, remember the following SQL Server 2000 rules:
• Archives and archive groups can only be used in one database.
　
• An archive can belong to only one archive group.
　
• Data and transaction record file data cannot be in the same file. The file space and data file space should be managed separately.
　
• The transaction record file is not part of the archive group.
　
• Once an archive is created, the archive cannot be moved to another archive group unless you delete the archive and recreate it.
　
To help you design databases, the following are some suggestions for using archives and archive groups:
• Most databases only need one data file and one transaction record file for smooth operation. We recommend that you use this design for databases where I/O is not particularly intensive.
　
• As described in Chapter 5th, record files and data files must be placed on different physical disks.
　
• If you need to use multiple data files, you can set primary data files to store system data tables and objects, while user data and objects are stored with one or more secondary data files.
　
• To achieve the best performance, try to create an archive and archive group across multiple physical disks to use the most parallel disk I/O.
　
• Store frequently accessed data tables and non-clustered indexes contained in these data tables in a different physical disk from the data table in the form of a archive group. If the file is on a different disk, the performance will be improved with parallel I/O. (The index will be discussed in chapter 17th .)
　
• If possible, store the data tables used in the same query on different physical disks, so that you can use parallel disk I/O when searching data.
　
When a system uses a large number of raid and many disks, the last two suggestions are not necessarily applicable. If you have many disks, try to distribute indexes and data tables to different disks to use the most parallel disk I/O.
Automatic File Growth
　
SQL Server allows automatic file growth as needed. When creating an archive, you can specify whether SQL Server allows automatic growth. We recommend that you set the file to allow automatic growth (the default value is also to allow automatic growth), because it can reduce the workload of administrators to manually monitor and increase the file space.
The initial size of an archive. When the initial space is filled up, SQL Server will increase the archive space according to the specified growth volume. This growth volume is called the Growth Increment value (growth increment). When the new space is filled up, SQL server allocates a new growth increment value. Data Files will grow as needed until the disk is full or reaches the maximum file size (if specified ).
________________________________________
Description
Automatic File growth is different from proportional writing. Automatic File growth means that when the file is filled up, SQL Server will automatically increase the file space; while proportional writing means that SQL server will store data proportionally based on the remaining space of the file, without increasing the space of archives.
________________________________________
The maximum file size is the maximum size that can be grown. This value is specified when the file is created. You can use the Enterprise Manager or alter database command to change the maximum file size. If the maximum file size is not set, SQL Server allows the file to grow as needed until the disk space is used up. To avoid SQL Server errors caused by insufficient disk space on the disk drive, you should set the maximum size for each file. If the file does grow to the maximum size, you can also use the alter database statement to reset the maximum file size, or add another file to the disk when the disk has space. If the file can be expanded without limit (by default), when the disk space is exhausted, you must create the file on another disk with the remaining space.
You should use automatic file growth and maximum file size as the design rules. When you create a database, set the file to the maximum size that may grow. Although the growth of archives can automatically grow as needed, we recommend that you monitor the growth of archives on a regular basis to help you predict the future growth of archives, and decide whether to modify the database to add more files.
System Database
　
When You Install SQL Server, four system databases will be created: Master, tempdb, model, and MSDB.
• The master node records system-level information, SQL Server installation information, and configuration settings, and records all logon accounts, other existing databases, and storage locations of the primary files of all user databases. You should save the backup of the latest master database.
　
• Tempdb is used to control temporary data tables and temporary pre-stored programs. This database is also used for temporary storage of SQL Server, such as sorting data. Each time SQL Server is started, a clean copy of The tempdb database is created based on the preset size of the model. Then it will automatically grow as needed. If you need a large tempdb database space, you can use the alter database command to set a larger default value. Search for the tempdb database in online books for more information.
　
• The model system creates templates for other databases, including the tempdb database. When a database is created, the first part is the backup of the model database. The rest of the database is filled by blank pages. The system must have a model database, because each time when SQL Server is started, you need to use the model database to recreate the tempdb database. You can also modify the model database to include user-defined data types and data tables so that each new database you create includes these attributes. Search for the model database in online books for more information.
　
• Msdb SQL Server Agent uses it to schedule jobs, warn and record operator information. (Operator) is the person designated to take charge of the work and warn .)
　
Each system database has its own primary data file and record file. The database is stored in the directory specified when SQL Server is installed.
Create a database
　
SQL Server provides three ways to create a database: using the database creation wizard, SQL Server Enterprise Manager, and T-SQL commands. T-SQL commands can be stored in files and executed as instructions. The three methods are discussed below:
Note the limitations of the database creation wizard. If you use the Wizard to create a database, the created data files will be stored in a single disk you specified, this is because the wizard does not allow you to specify the file in other locations during database creation (that is, in addition to the specified location at the beginning, during the wizard installation process, all created data files are specified to the same disk and file ). Record files can be stored on disks or files different from data files, but they can only be stored on a single disk. User-defined groups cannot be specified, and files in them will inherit the same growth options. Due to these restrictions, we recommend that you use the database creation wizard only when the database requires a major data file and transaction record file.
If you want to store secondary data files on disks that are different from primary data files, or you want to add a user-defined archive group, or you need to specify the growth option for each archive, you must use enterprise manager or T-SQL script to create a database.
Use the database creation wizard
　
If you are a beginner in database creation, you can use the database creation Wizard to create a database. Follow these steps:
1. enable SQL Server Enterprise Manager and select a server to create a database (first expand the folder by clicking the plus sign next to the name of the Microsoft SQL servers folder, and then expand the SQL server group data folder, you can see the registered server ). After selecting a server, select the tool/genie from the menu to go to the select genie dialog box and expand the database, as shown in 9-4.
　
 
Figure 9-4 select genie
2. Press Create Database Wizard to open the welcome Create Database Wizard dialog box, as shown in 9-5.
　
 
Figure 9-5 "Welcome to database creation wizard" dialog box
3. Click Next to name the database and specify its location (as shown in 9-6), and enter the database name, database file, and transaction record file storage path. If you select..., you can browse the file. After the path is specified, follow the next step.
　
 
Figure 9-6 "name a database and specify its location"
________________________________________
Description
The figure in this section shows how to create a database named mydb from scratch. The main data file of this database is stored in C:/mssql2k/MSSQL/data, and the record file is stored in D: /mssql2k/MSSQL/data.
________________________________________
4. Figure 9-7 shows the name of the database file. You can set the file name and start size for each database. The main data file automatically uses the database name as the prefix. You can accept or enter the name with the extension. MDF. If you know the size of the database file, you can directly enter the start size value. Otherwise, we recommend that you retain the default value. In the future, you can use the enterprise manger or alter database command to change the size. In addition to the primary file set at the beginning, other files created are secondary files and are automatically assigned to the file. NDF extension. These files will be stored in the same directory as the primary files. Click Next to continue. When you use the database creation wizard, there is no user-defined archive group option.
In the example, we save the original preset primary file (mydb_data), add a secondary file, and name it mydb_data2. The two files are stored at the same location specified in step 3. If the specified folder or archive group is placed on a disk that you do not want to place, stop the wizard installation process and use other methods to add a database. In the next section, we will discuss other methods for adding a database. Click Next to continue adding.

　
 
Figure 9-7 "name a database file"
5. The number of defined database archives (Figure 9-8) appears ). Generally, we recommend that you select automatic growth of database files to allow the system to automatically increase the size of the database as needed, and specify that the database files should grow at a fixed increment value, or, the specified file grows as a percentage of the current file size. You can also choose not to automatically grow the database file to manually adjust the database size, but this will increase manual consumption. You can also control the space for database file growth by choosing not to restrict file growth or to restrict file growth. If you use the Wizard to add a database, you cannot individually define the database file in Step 4. That is to say, if you use the Wizard to add a database, all the databases added in Step 4 will grow in the way defined here. Follow the next step to continue adding.
　
 
Figure 9-8 "define database file growth volume"
6. The "name" dialog box is displayed. This dialog box is similar to the screen in Step 4 for naming database files. Be sure not to confuse these two dialog boxes.
Set the logic to the same as step 4. first enter the name and start size of the transaction record file (note: the transaction record file records the modification records of all databases, when the system fails, it can be used as a basis for data restoration ). The system automatically creates the first transaction data file and uses the database name as the prefix. You can also customize the name. The extension of the transaction data file is. LDF. You can add the transaction record file as needed. Unless you know the starting size of the transaction file, we recommend that you retain the default value. If you need to change it later, you can use the Enterprise Manager or alter database command to change this default value. Follow the next step.
7. The transaction record file growth volume is defined. The logic set here is the same as Step 5. You can specify the file growth mode. After completing the settings, follow the next step.
8. The database creation wizard appears (Figure 9-9 ). Check whether all new information is correct. After confirmation, press finish to add the database. If you want to modify the information, press the previous step to return to the previous screen.
　
 
Figure 9-9 "complete database creation wizard"
9. The database has been successfully created. This indicates that the database has been created. Click OK to complete the Add step.
10. If the information box appears, you need to create a maintenance plan for the database. We recommend that you select YES. The maintenance plan ensures database execution efficiency and regular backup to prevent system errors and check data consistency. Select "no" here. We will discuss the maintenance plan later in Chapter 30th.
Use Enterprise Manager
　
SQL Server Enterprise Manager can create more complex database options than the wizard. For example, you do not need to regard all the files in the database as one archive group, but you can define the archive growth option for each created archive or specify the User-Defined archive group. The following describes how to use Enterprise Manager to create a database. In this example, we will create a database called mydb, it contains one primary archive data and three secondary archive data that are simultaneously stored in a user's custom archive group.
1. Enable Enterprise Manager. Expand the SQL server group and then expand the server. Click the right button on the database and then click Add database.
2. In the database Properties dialog box, the common tab is displayed (Figure 9-10 ). Enter the name of the new database in the box.
　
 
Figure 9-10 "general" tab in the "database properties" dialog box
3. Click the data file tab (Figure 9-11 ). Enterprise Manager automatically creates a primary data file, uses the created database name as the prefix, and uses primary as the archive group. You can change the name, location, or size of a primary file, but you cannot change the archive Group of the primary data file. Enter the file name (logical name), location (Object Name), size, and the archive group of each data file. All data files except the main files can be set as user-defined file groups. In this example, we create a secondary data file named mydb_data2 in the my_fg archive group.
　
 
Figure 9-11 "Data Files" tab in the "database properties" dialog box
The default position of the file is in the disk data folder where SQL server is located. You can enter a new file location or use the Browse button to specify the file location.
4. in the file attributes option area, you can select the automatic file growth and growth mode. You can also specify the file size limit before the logic is the same. You can select the unlimited file growth option to allow the file to grow without restriction, or select the size (in MB) of the restricted file ). You can select your settings now or retain the default values. If you want to change the default value later, you can go back to the database Properties window of Enterprise Manager to change it. To delete an archive in the list, click the archive to be deleted and press the delete key.
5. After setting all data files, click the volume tab of the transaction record file. The logic of the transaction record file is the same as that of the data file volume tag page, but the archive group option is missing because the transaction record file does not belong to the archive group. Enter the file name (logical name), location (Object Name), size, and start size of the transaction record file and all other files, and select the file to automatically grow.
6. After defining the database properties, follow the instructions to complete the addition. The new database icon is displayed on the right of the screen.
Use T-SQL commands
　
You can use T-SQL commands or script to create and modify databases without using graphical user interfaces. It is useful to use the script code to create a database. For example, if you create a database on a graphical user interface but set the file location to an error, you can delete the entire file and reset the correct location. But using T-SQL commands can quickly fix errors without having to rebuild the database. You can also use the same command code to create another identical database on other systems.
On the other hand, Enterprise Manager can be used to generate a T-SQL directive for creating databases and database objects, but a database must exist before this action. The Enterprise Manager script contains all existing database settings, so you can use the generated script as needed. Whether you use a custom instruction code or a generated instruction code, we recommend that you first understand the T-SQL for creating a database. This section will review the T-SQL commands for creating databases. You can store the script code in the file. Chapter 1 of this book will learn how to establish and execute the instruction code.
________________________________________
Simple databases in the real world
In this example, we will create a database named mydb, which includes a primary data file (mydb_root) and a secondary data file (mydb_data1) that is retained in the predefined primary archive group) and a transaction record file (log_data1 ). The SQL statement for creating mydb is as follows:
Create Database mydb
On
(Name = mydb_root, -- main data file
Filename = 'C:/mssq12k/MSSQL/data/mydbroot. MDF ',
Size = 8 Mb,
Maxsize = 9 MB,
Filegrowth = 100 MB ),
(Name = mydb_data1, -- secondary data file
Filename = 'C:/mssql2k/MSSQL/data/mydbdata1.ndf ',
Size = 100 MB,
Max size = 1500 MB,
Filegrowth = 100 MB)
Log On
(Name = log_data1, -- Record the file
Filename = 'd:/log_files/logdata1.ldf ',
Size = 1000 MB,
Max size = 1500 MB,
Filegrowth = 100 MB)
________________________________________
In this example, you can see that both the primary and secondary data files are in disk C, and the record files are in disk E. As mentioned above, you should store data files and record files separately to improve disk I/O efficiency.
We recommend that you use. MDF,. NDF, And. LDF extension. Specify the size, maxsize, and filegrowth options in kb or MB. The default value is MB.
Xxxxxxx
________________________________________
Real-world complex databases
This database example is based on several disks or disk arrays (in raid systems. Here, "disk" represents a single disk device or a disk array with a raid Disk Area. We will create several archives on different disks, each of which is placed in one of the two archive groups. We name the database sales and include the following files:
• Main data file: sales_root.mdf
　
• Three secondary data files in the customers_group archive group: customer_data1, customer_data2, and customer_data3
　
• Two secondary data files in the products_group archive group: product_data1 and product_data2
　
• One record file: log_data1.ldf
　
The syntax for creating a sales database is as follows. Because we will customize a Data Group, we will see the keyword filegroup in the create databes command.
Create Database sales
On Primary -- specify the primary archive group (this parameter is optional)
(Name = salesroot, -- main data file
Filename = 'C:/mssq12k/MSSQL/data/salesroot. MDF ',
Size = 8 Mb,
Maxsize = 10 MB,
Filegrowth = 1 MB ),
Filegroup customers_group -- Archive group of secondary data files
(Name = customer_data1,
Filename = 'd:/mssq12k/MSSQL/data/costomerdata1.ndf ',
Size = 800 mb,
Max size = 100 MB,
Filegrowth = 100 MB ),
(Name = customer_data2, -- secondary data file
Filename = 'e:/mssql2/MSSQL/data/costomerdata2.ndf ',
Size = 800 mb,
Max size = 1000 MB,
Filegrowth = 100 MB ),
(Name = customer_data3, -- secondary data file
Filename = 'f:/mssq12/MSSQL/data/costomerdata3.ndf ',
Size = 800 mb,
Max size = 1000 MB,
Filegrowth = 100 MB ),
Filegroup products_group -- another archive Group
(Name = product_data1, -- secondary data file
Filename = 'G:/mssq12/MSSQL/data/product_data1.ndf ',
Size = 500 mb,
Max size = 700 mb,
Filegrowth = 100 MB ),
(Name = product_data2, -- secondary data file
Filename = 'H:/mssq12/MSSQL/data/product_data2.ndf ',
Size = 500 mb,
Max size = 700 mb,
Filegrowth = 100 MB)
Log On
(Name = logdata1, -- Record File
Filename = 'I:/log_files/logdata1.ldf ',
Size = 800 mb,
Max size = 1000 MB,
Filegrowth = 200 MB)
________________________________________
With reference to the annotations in the example, if you do not define the archive group to which the archive belongs before saving the archive, the archive will be pre-set to the primary archive group. If you define an archive group before saving it to an archive, the archive will be put into the archive group according to the definition. As shown in the example, after customers_group and products_group are defined, the files in the archive group will be placed in the archive group. After an archive group is defined, all the files listed will be stored in the archive group until another archive group is defined, or until the log on narrative is executed.
Pay attention to the disk code of each file. When creating data tables and indexes in those file groups, create each file on different disks to distribute data to different disks. For example, a data table created in customers_group stores the data in the disk of the customer_data1, customer_data2, and customer_data3 files according to the proportion (in stripping mode. Record Files are also in a separate disk without data files, so that the record files can be written in sequence.
In the preset state, SQL Server allows data files to grow as needed until the disk space is used up. Therefore, if you do not want to allow the growth of the database file to exceed the size of the initial creation, you must set filegrowth to 0, so that the file will be filled up based on the initial size you set. When your data table is a static data table (a data table that does not grow, that is, data is not written), you can select this setting. In this case, you do not need to specify the maxsize value, because the maximum size is the initial size setting.
________________________________________
Description
If you have used SQL 6.5 or earlier, you will find that SQL 7.0 does not use the disk init command to create a logical device before creating a database. The logical device is now replaced by an archive.
________________________________________
View Database
　
After the database is created, you can use enterprise manager or osql in the Command column to execute SQL commands to browse and view objects in the database. The following two methods are described in sequence:
Use Enterprise Manager
　
Follow these steps to view database information using Enterprise Manager:
1. In Enterprise Manager, expand the server name and database data folder of the SQL server group/database, as shown in 9-12.
　
 
Figure 9-12 the expanded Enterprise Manager and database data folder
2. Select a database view, as shown in 9-13.
　
 
Figure 9-13 objects displayed in the northwind database
Use SQL
　
You can also use the command column Prompt window, or run the T-SQL command in query analyzer to view database information. To view database information by using the SQL command column, open the Command Prompt window first, use osql to connect to SQL Server, as shown in the following example:
Osql-u <username>-P <password>-S <servername>
When you enter the command in the upper column, enter the data in the angle brackets correctly (excluding the angle brackets ). Run the following SQL command to list database information.
Use mydb -- specify which database environment to execute
Go
Sp_helpfile -- display all database file information. You can also provide the file name to get
To specific file information
Go
Sp_helpdb mydb -- the above information plus the size of the space allocated by the file
Go
Sp_helpfilegroup -- displays information about the archive group in the database. You can also provide the archive group name
Obtain specific file information.
Go
Sp_helpdb -- View All Database Information
Go
________________________________________
Related information
You can find details related to these commands and output methods in SQL Server online series.
________________________________________
Delete Database
　
Sometimes, you may need to delete the database. Note that once the database is deleted, no reply steps can be performed. to recover the database, you must restore the backup version. For the sake of security, back up the database before deleting it, in case you will use it again in the future. You can use enterprise manager or SQL commands to delete databases.
Use Enterprise Manager
　
As mentioned in chapter 8, Enterprise Manager can perform database management or view data. You can follow these steps to permanently delete databases and all files:
1. Expand the SQL server group in Enterprise Manager and press the server name of the database to be deleted twice.
2. Expand the database data folder to display the database.
3. click the right button on the name of the database you want to delete and select Delete from the shortcut menu. The delete database message box appears. If you choose to delete the backup and restore the history of the database, all backup-Restoration Information in the backup-restoration history data table in the MSDB database will be deleted, and the database will be deleted after selection.
________________________________________
Description
The master database cannot be deleted.
________________________________________
Use SQL
　
You can use the T-SQL command to delete a database. To use a T-SQL, you must enable the query analyzer or Command Prompt window to connect to SQL Server through osql, see the following example:
Osql-u <username>-P <password>-S <servername>
The deletion of a database is permanent. You can use the drop database Command to delete the database and all files. Follow these steps to delete the database:
Use master -- you must use the master database to execute the DROP DATABASE
Command
Go
Drop database mydb -- the database name is a unique parameter.
Go
After deleting a database, we recommend that you create a new backup for the master database to keep the latest user database, so as to avoid having deleted database data in the master database. You cannot delete a database when the user is still online with the database. Before deleting a database, make sure that the user is not connected to the database.
Summary
　
In this chapter, you will learn more about the automatic growth of data, record files, archive groups, and archives. You can also build databases by creating database genie, SQL Server Enterprise manger, and T-SQL commands, and learn two ways to view and archive data and delete databases. The next chapter describes how to create a data table in a database.