SQL Server 2008 database structure
System database
| master | A database that records all system level information of the SQL Server system |
| msdb | Template database for all user databases and tempdb database |
| model | Used by SQL Server agent to schedule alerts and jobs |
| temp | Global resources available to all users connected to the SQL Server instance, save all temporary tables and temporary stored procedures, and regenerate empty tempdb each time |
SQL Server 2008 database has three types of files: primary data file, secondary data file and transaction log file.
| file type | explain |
| Master data file | The main data file contains the startup information of the database and points to other files in the database. User data and objects can be stored in this file or in a secondary data file. Each database has a primary data file. The recommended file extension for the primary data file is. mdf. |
| Secondary data files | Secondary data files are optional, user-defined and store user data. By placing each file on a different disk drive, secondary files can be used to spread data across multiple disks. In addition, if the database exceeds the maximum size of a single Windows file, you can use secondary data files so that the database can continue to grow. |
| Transaction log file | The transaction log file holds the log information used to recover the database. Each database must have at least one log file. The recommended file extension for the transaction log is. 1df. |
The database contains at least 2 files:
Master data file (. mdf) A database has only one master data file
Transaction log file (. ldf) At least one log file, or multiple log files
Secondary data file (. ndf) ) A database can have 0 or more secondary data files
Create database
CREATE DATABASE database_name ON (NAME = data_logical_file_name , FILENAME = 'data_os_file_name' [,SIZE = size [ KB | MB | GB | TB ]] [,MAXSIZE = max_size [ KB | MB | GB | TB ] |UNLIMITED] [,FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ]]) LOG ON (NAME = log_logical_file_name , FILENAME = 'log_os_file_name' [,SIZE = size [ KB | MB | GB | TB ]] [,MAXSIZE = max_size [ KB | MB | GB | TB ] |UNLIMITED] [,FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ]] )
Management database
view the database
Syntax format: [EXECUTE] sp_helpdb [Database name] Note: when executing the stored procedure, if the database name is given as a parameter, the relevant information of the database will be displayed. If the database name parameter is omitted, the information of all databases in the server will be displayed. sp_helpdb xs sp_helpdb
modify the database
ALTER The DATABASE statement can modify a DATABASE
ALTER DATABASE database_name
{
ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP filegroup_name ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
| MODIFY FILE <filespec>
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY NAME = new_database_name
}
explain:
(1)ADD FILE <filespec> [ ,...n ] [ TO FILEGROUP filegroup_name]: adds a new data file to the specified filegroup.
(2) Add log file < filespec > [,... N]: add a new log file.
(3) REMOVE FILE logical_file_name: deletes the file description and physical file from the database system table.
(4) Modify file < filespec >: modify the physical file name.
(5) ADD FILEGROUP filegroup_name: adds a filegroup.
(6) REMOVE FILEGROUP filegroup_name: deletes the specified filegroup.
(7) MODIFY NAME = new_database_name: rename the database.
The configuration database is read-only
EXEC sp_dboption 'Database name', 'read only', 'TRUE'
Shrink databases and data files
DBCC SHRINKFILE Logical name of data file DBCC SHRINKFILE Log file logical name The shrunk database needs to be connected For example: DBCC SHRINKFILE(myteach_dat,6)