创建和管理SQL_Server_2008数据库

第5章创建和管理SQLServer2008数据库主要内容5.1系统数据库概述5.2创建数据库5.3管理数据库本章小节SQLServer2008数据库概述数据库是SQLServer存放数据和数据对象（如表、索引、视图、存储过程、触发器）的容器，用户使用数据库管理系统提供的功能时，首先必须将自己的数据放置和保存到用户数据库中。数据库可以分为系统自带数据库和用户自有数据库。5.1系统数据库概述master数据库msdb数据库model数据库tempdb数据库Resource数据库Master数据库Master数据库是“数据库的数据库”，Master数据库记录SQLServer系统的所有系统级信息。Master数据库中包含以下重要信息实例范围的元数据（例如登录账户）、端点、连接服务器和系统配置设置。记录SQLServer系统的所有系统级信息。SQLServer的初始化信息。Msdb数据库存储计划信息存储备份和恢复相关的信息，尤其是SQLServe代理需要使用它来执行安排工作和警报，记录操作者等操作。Model数据库创建的所有数据库的模板。当执行CREATEDATABASE语句时，将通过复制model数据库中的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。如果修改model数据库，之后创建的所有数据库都将继承这些修改。Tempdb数据库tempdb保存所有临时表和临时存储过程。每次启动SQLServer时，都要重新创建tempdb。Resource数据库Resource数据库是只读数据库，它包含了SQLServer2008中的所有系统对象。Resource数据库是隐藏的，通常应该由MicroSoft客户服务专家来打开，用于查找问题和进行客户支持。5.2用户自定义数据库数据库用户使用SQLServer2008存放数据，需要用户创建数据库。关于用户数据库，需要掌握以下内容数据库文件使用ManagementStudio创建数据库用SQL命令创建数据库5.2.1数据库文件每个SQLServer2008数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。数据文件日志文件数据文件数据库文件是存放数据库数据和数据库对象的文件。一个数据库可以有一个或多个数据库文件。一个数据库只能有一个主数据库文件，扩展名为mdf。其它数据库文件被称为次数据库文件，扩展名为ndf。日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。也可以为多个事务日志的建议文件扩展名是.ldf。关于数据库文件-1每个数据库文件有两个名称：物理文件名（os_file_name）：是包括目录路径的物理文件名。如：D:\DBFILE\CJGL.mdf逻辑文件名（logical_file_name）：是在所有Transact-SQL语句中引用物理文件时所使用的名称。如cjgl_data文件大小指定数据和日志文件的初始大小，或采用默认大小。如果已经超过了初始数据文件的大小并且文件开始自动增长，则重新计算预期的数据库大小最大值。关于数据库文件-2文件组使用文件组可以隔离用户和文件，使得用户针对文件组来建立表和索引，而不是实际磁盘中的文件。当文件移动或修改时，由于用户建立的表和索引是建立在文件组上的，并不依赖具体文件，这大大加强了可管理性.使用文件组来管理文件可以使得同一文件组内的不同文件分布在不同的硬盘中，极大的提高了IO性能.关于数据库文件-2文件组文件组可以分为主文件组（Primary）和用户自定义的文件组。一个数据库只能有主文件组（Primary），但可以有多个自定义文件组。自定义文件组可在创建数据库时定义。【例5.1】在Master数据库下，执行存储过程sys.sp_helpfile，则得到结果如图5.2所示，逻辑文件名在“name”列，物理文件名在“filename”列，最大容量在maxsize列，增长容量在growth列，文件组在“filegroup”列。查看数据库文件信息5.2.2使用ManagementStudio创建数据库在对象资源管理器中数据库节点上右击,在弹出菜单中单击“新建数据库…”，则出现“新建数据库”对话框。5.2.3用SQL命令创建数据库CREATEDATABASEdatabase_name[ON[PRIMARY][filespec[,...n][,filegroup[,...n]][LOGON{filespec[,...n]}]][COLLATEcollation_name][WITHexternal_access_option]]其参数说明如下。（1）database_name：新数据库的名称。（2）ON：指定数据库文件或文件组的明确定义。（3）PRIMARY：指明主数据库文件或主文件组。一个数据库只能有一个主文件，如果没有指定PRIMARY，那么CREATEDATABASE语句中列出的第一个文件将成为主文件。（4）filegroup：控制文件组属性。其语法格式为：filegroup::=FILEGROUPfilegroup_namefilespec[，...n]其中filespec：控制文件属性。其格式如下：filespec::={(NAME=logical_file_name，FILENAME='os_file_name'[,SIZE=size[KB|MB|GB|TB]][,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]])[,...n]}其中有逻辑文件名（NAME），物理文件名（FILENAME），初始大小（SIZE，默认单位为MB），可增大到的最大容量（MAXSIZE），自动增长（FILEGROWTH）。每个文件之间以逗号分隔。（5）LOGON：明确指定存储数据库日志的磁盘文件（日志文件）。LOGON后跟以逗号分隔的用以定义日志文件的filespec项列表。如果没有指定LOGON，将自动创建一个日志文件，其大小为该数据库的所有数据文件大小总和的25%或512KB，取两者之中的较大者。[例5.2]创建未指定文件的数据库USEMasterGOIFDB_ID(N'test')ISNOTNULLDROPDATABASEtestGOCREATEDATABASEtest--创建数据库GOSELECTname,size,size*1.0/128AS[SizeinMBs]--检查数据库文件和大小FROMsys.master_filesWHEREname=N'test'【例5.3】创建指定文件的数据库，数据文件、日志文件的初始尺寸，大小和增长幅度（单位都是MB）都已指定。CREATEDATABASEtestON(NAME=test_dat,FILENAME='E:\sqlserver2008db\testdat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=test_log,FILENAME='E:\sqlserver2008db\testlog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)【例5.4】通过指定多个数据和事务日志文件创建数据库test。该数据库具有2个10MB的数据文件和2个10MB的事务日志文件。主文件是列表中的第一个文件，并使用PRIMARY关键字显式指定。事务日志文件在LOGON关键字后指定。请注意用于FILENAME选项中各文件的扩展名：.mdf用于主数据文件，.ndf用于辅助数据文件，.ldf用于事务日志文件。CREATEDATABASEtestonprimary(name=test_data,filename='E:\sqlserver2008db\test_dat.mdf',size=10,maxsize=100,filegrowth=5),(name=test_data1,filename='E:\sqlserver2008db\test_dat1.ndf',size=10,maxsize=100,filegrowth=10)logon(name=test_log,filename='E:\sqlserver2008db\test_log.ldf',size=10MB,maxsize=50MB,filegrowth=5MB),(name=test_log1,filename='E:\sqlserver2008db\test_log1.ldf',size=10MB,maxsize=50MB,filegrowth=5MB)5.3管理数据库查看数据库属性修改数据库收缩数据库删除数据库查看数据库属性对象资源管理器是ManagementStudio的一个组使用ManagementStudio查看数据库属性用视图、系统函数和系统存储过程来查看数据库属性视图函数存储过程和其他语句sys.databasesDATABASE_PRINCIPAL_IDsp_databasessys.database_filesDATABASEPROPERTYEXsp_helpdbsys.data_spacesDB_IDsp_helpfilesys.filegroupsDB_NAMEsp_helpfilegroupsys.allocation_unitsFILE_IDsp_spaceusedsys.master_filesFILE_IDEXDBCCSQLPERFsys.partitionsFILE_NAMEsys.partition_functionsFILEGROUP_IDsys.partition_parametersFILEGROUP_NAMEsys.partition_range_valuesFILEGROUPPROPERTYsys.partition_schemesFILEPROPERTYsys.dm_db_partition_statsfn_virtualfilestats【例5.5】显示Master数据库信息。sp_helpdbMaster执行结果如图5.10所示。修改数据库可以在ManagementStudio中利用数据库属性的设置更改数据库各项参数，也可使用ALTERDATABASE命令来更改数据库，ALTERDATABASE可以更改数据库的属性或其文件和文件组。ALTERDATABASE的语法格式如下：ALTERDATABASEdatabase_name{add_or_modify_files|add_or_modify_filegroups|set_database_options|MODIFYNAME=new_database_name|COLLATEcollation_name}参数简要说明（1）database_name：要修改的数据库的名称。（2）MODIFYNAME=new_database_name：使用指定的名称new_database_name重命名数据库。（3）add_or_modify_files：指定添加、修改或删除的数据库文件。其语法格式为：add_or_modify_files::={ADDFILEfilespec[,...n][TOFILEGROUP{filegroup_name|DEFAULT}]|ADDLOGFILEfilespec[,...n]|REMOVEFILElogical_file_name|MODIFYFILEfilespec}（4）add_or_modify_filegroups：指定添加、修改或删除的文件组。（5）set_database_options：更改数据库参数。【例5.6】将数据库名test更改为test1。ALTERDA