第5章数据库创建与管理

SQLServer2012数据库管理教程第5章第5章数据库的创建与管理数据库创建、修改和删除数据库数据库的分离和附加数据库的备份和还原数据库快照5.1数据库主数据文件：扩展名为.mdf，包含数据库目录的启动信息，并指向其他数据文件。主数据文件可以包含对象和用户数据。每个数据库都有一个主数据文件。次要数据文件：默认扩展名为.ndf，用来存放除主数据文件之外的对象和用户数据。次要数据文件是可选的，一个数据库可以包含零至多个次要数据文件。事务日志文件：默认扩展名为.ldf，用来记录所有对数据库的操作，以便在数据库系统出现故障时对数据库进行恢复操作。每个数据库至少包含一个事务日志文件。5.1.1数据库文件数据库中通常包含以下三类文件。5.1.2系统数据库每个SQLServer都包含两种类型的数据库：系统数据库和用户数据库。系统数据库存储有关SQLServer的系统信息，以便SQLServer对系统进行管理。SQLServer2012中包含master，model，msdb，tempdb和resource五个系统数据库。安装完SQLServer2012后，MicrosoftSQLServerManagementStudio会自动显示前四个系统数据库，如右图所示。各系统数据库的作用如下表所示。5.1.3示例数据库示例数据库是Microsoft公司给出的用户数据库，其中包含了各种数据库对象，使用户可以自由地对其中的数据或表结构进行查询、修改等操作，以便用户更好地理解和学习SQLServer。默认情况下，SQLServer2012中不安装示例数据库；用户若想使用，可以从网站中查找并下载这些示例和示例数据库。5.2创建、修改和删除数据库5.2.1创建数据库1．使用SQLServerManagementStudio创建数据库SQLServerManagementStudio是SQLServer系统运行的核心窗口，它提供了用户数据库管理的图形工具和功能丰富的开发环境。下面以jw系统为例，演示如何通过SQLServerManagementStudio完成数据库的创建。1）打开“开始”菜单，选择“程序”→MicrosoftSQLServer2012→SQLServerManagementStudio命令，打开SQLServerManagementStudio窗口，设置登录的“服务器类型”为“数据库引擎”，并使用合适的身份验证方式与服务器建立连接。2）连接成功后，在“对象资源管理器”窗格中，右击“数据库”节点，从弹出的快捷菜单中选择“新建数据库”命令，打开“新建数据库”窗口，如下图所示。“新建数据库”命令

“新建数据库”窗口3）在“常规”选项页中，首先在“数据库名称”文本框中输入数据库名称“jw”，可以选择数据库的所有者，数据库的默认所有者为本次SQLServer连接的登录者。其次，设置数据库文件属性，包括如下内容。逻辑名：表示相应数据文件的逻辑名称，它必须在数据库中是唯一的，且须符合标识符命名规则。文件类型：其中“数据”表示数据文件，“日志”表示日志文件。文件组：用于存放数据文件的文件组。默认情况下，数据文件存放于PRIMARY文件组中，日志文件不属于任何一个文件组。初始大小：文件的初始大小。需注意的是，主数据文件的初始大小至少为3MB；次要数据文件的初始大小至少为1MB；事务日志文件的初始大小至少为512KB。文件增长：文件增长的方式，单击“自动增长”选项右边的

按钮，会出现如右图所示的对话框，用于指定是否允许文件自动增长；文件增长方式是以百分比增长，还是以兆字节增长；文件大小是否有限制。路径：数据文件或日志文件的物理路径。默认情况下，SQLServer2012会将文件存储于C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA文件夹中，单击“路径”选项右边的

按钮，在弹出的对话框中可为数据文件或日志文件指定其他路径。4）在“选项”选项页中，可以定义所创建数据库的排序规则、恢复模式、兼容级别等选项。在此均保持默认选项。5）在“文件组”选项页中，可以查看数据库中现有的文件组，也可以分别通过“添加”和“删除”按钮添加和删除文件组6）全部设置完毕后，单击“确定”按钮即可完成jw数据库的创建。2．使用Transact-SQL语句创建数据库在SQLServer中，可以使用CREATEDATABASE语句来创建数据库。CREATEDATABASE语句的语法格式如下：CREATEDATABASEdatabase_name[ON[PRIMARY][(NAME=logical_name,FILENAME='path'[,SIZE=database_size][,MAXSIZE=database_maxsize][,FILEGROWTH=growth_increment])[,FILEGROUPfilegroup_name[(NAME=datafile_name,FILENAME='path'

[,SIZE=datafile_size][,MAXSIZE=datafile_maxsize][,FILEGROWTH=growth_increment])]]][LOGON[(NAME=logfile_name,FILENAME='path'[,SIZE=database_size][,MAXSIZE=database_maxsize][,FILEGROWTH=growth_increment])]]]其中，ON用于显式定义存储数据库数据部分的磁盘文件（数据文件）。默认情况下，数据文件存放于PRIMARY主数据文件组中。可以通过FILEGROUP关键字，将数据文件存放于用户自定义文件组中；LOGON关键字用于创建事务日志文件；NAME用于指定数据或日志文件的逻辑文件名；FILENAME用于指定各文件的存储路径；SIZE用于定义文件的初始大小；MAXSIZE用于指定文件的最大容量；FILEGROWTH用于指定文件的增长速率。5.2.2修改数据库1．创建文件组使用文件组的优势在于提高系统的性能，表现为三方面：第一，可以平衡多个磁盘上的数据访问负荷；第二，可以使用并行线程来提高数据访问的效率；第三，简化数据库的维护工作，如可以备份或恢复单个文件或文件组，而不必备份或恢复整个数据库，对于海量数据库来说，备份文件或文件组是有效的备份策略。创建文件组可以通过SQLServerManagementStudio和Transact-SQL两种方式。【例5-2】通过SQLServerManagementStudio在jw数据库中增加用户自定义文件组secondary，并向其中增加次要数据文件jw_data2.ndf。1）打开“开始”菜单，选择“程序”→MicrosoftSQLServer2012→SQLServerManagementStudio，打开SQLServerManagementStudio窗口，设置登录的“服务器类型”为“数据库引擎”，并使用适合的身份验证方式与服务器建立连接。2）连接成功后，在“对象资源管理器”窗格中展开“数据库”节点，右击“jw”数据库，从弹出的快捷菜单中选择“属性”命令，打开“数据库属性”窗口。在左窗格中单击“文件组”选项，打开“文件组”选项页，如右图所示。3）单击“添加”按钮，在“名称”文本框中输入用户自定义文件组名称：sencondary。单击“确定”按钮，则用户自定义的文件组创建成功。4）打开“文件”选项页，单击“添加”按钮，输入次要数据文件的名称：jw_data2，在“文件组”选项中选择secondary，并配置其余文件选项，如下图所示。5）回到“文件组”选项页，此时会发现secondary文件组中的文件数已变为1。通过SQLServerManagementStudio也可将secondary文件组设为默认文件组或只读文件组，只需选中相应的复选框即可。Transact-SQL语言没有提供独立的管理文件组的命令，只能通过ALTERDATABASE语句对文件组进行添加和删除操作。添加文件组的语法格式如下：ALTERDATABASEdatabase_nameADDFILEGROUPfilegroup_nameALTERDATABASEdatabase_nameREMOVEFILEGROUPfilegroup_name删除文件组的语法格式如下：通过Transact-SQL语句也可以将文件组设置为默认文件组或只读文件组，其中设置为默认文件组的语法格式如下：ALTERDATABASEdatabase_nameMODIFYFILEGROUPfilegroup_nameDEFAULTALTERDATABASEdatabase_nameMODIFYFILEGROUPfilegroup_nameREAD_ONLY设置为只读文件组的语法格式如下：2．增加数据库容量当数据库中的数据量不断增加，导致数据库系统性能下降时，就需要通过增加数据容量来解决该问题。增加数据库容量的方法有以下三种。1）在创建数据库时设置数据库为自动增长方式。2）在SQLServerManagementStudio中修改数据库属性。3）通过ALTER语句来增加数据库容量。【例5-5】使用Transact-SQL语句将jw数据库的主数据文件jw.mdf的初始大小设置为5MB。ALTERDATABASEjwMODIFYFILE(NAME=jw_data,SIZE=5MB)3．收缩数据库容量收缩数据库的方式有以下三种。1）设置数据库为自动收缩方式。当文件中超过25%的部分包含未使用的空间时，“自动收缩”选项可用于收缩文件。文件将收缩至未使用空间占文件25%的大小，或收缩至文件创建时的大小，以两者中较大者为准。“自动收缩”选项设置于“数据库属性”窗口中。打开“数据库属性”窗口，选择“选项”选项；打开“选项”选项页，在“其他选项”列表框的“自动”选项组中，找到“自动收缩”选项，将其值设置为True；单击“确定”按钮即可，如图所示。2）通过MicrosoftSQLServerManagementStudio收缩数据库。右击需要收缩的数据库，从弹出的快捷菜单中选择“任务”→“收缩”→“数据库”命令。在打开的“收缩数据库”窗口中可以查看当前数据库的大小及可用空间，并可以自动设置收缩数据库的大小。设置完成后，单击“确定”按钮即可，如下图所示。通过“任务”→“收缩”→“文件”命令可以将数据库中的文件收缩到所需大小，如下图所示。3）通过DBCCSHRINKDATABASE命令收缩数据库。【例5-6】使用Trasact-SQL语句将jw数据库大小收缩至5MB。DBCCSHRINKDATABASE('jw',5)5.2.3删除数据库1．通过MicrosoftSQLServerManagementStudio删除数据库右击要删除的数据库，从弹出的快捷菜单中选择“删除”命令；打开“删除对象”窗口，如左图所示，确认删除信息；单击“确定”按钮即可。2．通过Transact-SQL语句删除数据库。【例5-7】使用Transact-SQL语句删除jw数据库。DROPDATABASEjw5.3数据库的分离和附加1．通过MicrosoftSQLServerManagementStudio分离和附加数据库1）分离jw数据库。在“对象资源管理器”窗格中，展开“数据库”节点，右击“jw”数据库；从弹出的快捷菜单中选择“任务”→“分离”命令；打开“分离数据库”窗口，确认分离信息，单击“确定”按钮即可。2）附加jw数据库。在“对象资源管理器”窗格中，右击“数据库”节点，从弹出的快捷菜单中选择“附加”命令。打开“附加数据库”窗口，单击“添加”按钮，在相应的位置找到jw数据库的.mdf文件，单击“确定”按钮。SQLServer会自动附加日志文件（.ldf），单击“确定”按钮，完成附加工作。2．通过Transact-SQL语句分离和附加数据库1）分离jw数据库EXECSP_DETACH_DB@dbname='jw'EXECSP_ATTACH_DB@dbname=jw,@filename1='F:\jw.mdf'2）附加jw数据库5.4数据库的备份和还原5.4.1备份类型1．完整数据库备份完整数据库备份会将数据库中所有的数据以及可以恢复这些数据的足够的日志都进行备份。它是任何其他类型备份的基础，如果没有执行完整数据库备份，就没有办法执行下述的差异数据库备份或事务日志备份。虽然完整数据库备份可以恢复数据库，但是会花费大量的时间和更多的空间，所以完整数据库备份不需要频繁进行。而且，若只进行完整数据库备份，那么进行数据恢复时只能恢复到最后一次完整数据库备份的状态，该状态之后的所有改变都将丢失。2．差异数据库备份差异数据库备份是指将最近一次完整数据库备份以后发生改变的数据进行备份。通常，创建完整数据库备份后较短时间内，由于数据修改量小，此时创建差异数据库备份速度较快。经过一段时间后，随着数据库的更新量加大，包含在差异数据库备份中的数据量会增加。这使得创建差异数据库备份的速度变慢。此时，必须重新创建一个完整数据库备份，为另一个系列的差异数据库备份提供新的差异基准。还原时，应首先还原完整数据库备份，再还原最新的差异数据库备份。3．事务日志备份事务日志备份依赖于完整数据库备份。事务日志备份并不备份数据库本身，而只记录自上次备份以来到本次备份之间所写的事务日志。事务日志备份比完整数据库备份节约时间和空间，且进行恢复时，允许恢复到故障时刻或一个强制时间点。通常，事务日志备份经常与完整数据库备份以及差异数据库备份结合使用。例如，每周进行一次完整数据库备份，每天进行一次差异数据库备份，每小时进行一次事务日志备份。这样，最多丢失一小时的数据。用事务日志备份进行数据库恢复时，需要应用完整数据库备份后的每一次日志备份。4．文件和文件组备份对于超大型数据库，进行完整数据库备份会花费大量的时间和空间，此时可以对选定的文件和文件组进行备份。但为了使恢复的文件与数据库的其余部分保持一致，执行文件和文件组备份后，必须执行事务日志备份。使用此备份类型允许只还原特定文件而无需还原整个数据库，还原时需用到文件和文件组备份文件和事务日志备份文件。5.4.2备份设备1．使用MicrosoftSQLServerManagementStudio创建备份设备【例5-9】使用MicrosoftSQLServerManagementStudio为jw数据库创建备份设备JWBACK。1）在“对象资源管理器”窗格中单击服务器名称，以展开服务器树。2）展开“服务器对象”节点，右击“备份设备”节点。3）从弹出的快捷菜单中选择“新建备份设备”命令，打开“备份设备”窗口。4）在“备份设备”窗口的“设备名称”文本框中输入“JWBACK”。在“目标”→“文件”选项中设置备份设备的路径。需注意的是，选择的磁盘驱动器上必须有足够的可用空间。单击“确定”按钮完成创建。2．通过SP_ADDUMPDEVICE系统存储过程创建备份设备使用SP_ADDUMPDEVICE系统存储过程创建备份设备的语法格式如下：SP_ADDUMPDEVICE[@devtype=]'device_type',[@logicalname=]'logical_name',[@physicalname=]'physical_name'[,{[@cntrltype=]controller_type|

[@devstatus=]'device_status'}]其中：[@devtype=]'device_type'：指定备份设备的类型，值可以为disk（磁盘）、tape（磁带）、pipe（命名管道）。[@logicalname=]'logical_name'：指定备份设备的逻辑文件名。对应于图5-14中的“设备名称”选项。[@physicalname=]'physical_name'：指定备份设备的物理文件名和存储路径。对应于图5-14中的“文件”选项。[@cntrltype=]controller_type：如果cntrltype的值是2，则表示是磁盘；如果cntrltype的值是5，则表示是磁带。[@devstatus=]'device_status'：device-status如果是noskip，表示读ANSI磁带头；如果是skip，表示跳过ANSI磁带头。【例5-10】使用SP_ADDUMPDEVICE系统存储过程，为jw数据库创建备份设备JWBACK，设备存放于D盘backup文件夹下。USEmasterEXECSP_ADDUMPDEVICE'disk','JWBACK','d:\backup\JWBACK.bak'对已有备份设备的查看可以通过MicrosoftSQLServerManagementStudio工具来进行。在“对象资源管理器”窗格中，展开“服务器对象”→“备份设备”节点即可，如左图所示；也可通过SP_HELPDEVICE系统存储过程查看，其运行结果如右图所示。查看“备份设备”

使用SP_HELPDEVICE系统存储过程查看备份设备5.4.3创建数据库备份1．使用MicrosoftSQLServerManagementStudio执行完整数据库备份1）打开MicrosoftSQLServerManagementStudio，展开服务器→“数据库”节点。2）右击“jw”数据库，从弹出的快捷菜单中选择“任务”→“备份”命令，打开“备份数据库”窗口，如右图所示。3）在“备份数据库”窗口的“常规”选项页中，首先在“数据库”下拉列表框中选择“jw”数据库，在“备份类型”下拉列表框中选择“完整”选面。需要注意的是，由于jw数据库默认的恢复模式为“简单”（Simple）恢复模式，而简单恢复模式备份时只备份数据文件，故在该模式下，不支持事务日志备份。这就需要改变其恢复模式，更改方法如下：打开“数据库属性”窗口，选择“选项”选项；打开“选项”选项页，选择“恢复模式”下拉列表框中的“完整”选项，如右图所示。这样就可以将jw数据库的恢复模式改为“完整”。4）回到“备份数据库”窗口，保持备份集“名称”不变。设置备份到磁盘的目标位置，首先单击“删除”按钮，删除已存在的目标；然后单击添加按钮，打开“选择备份目标”对话框，在该对话框中选中“备份设备”单选按钮；接着从下拉列表框中选择“JWBACK”备份设备，如下图所示。5）设置好后，单击“确定”按钮，返回“备份数据库”窗口，这时可以看到“目标”下面增加了JWBACK备份设备，如下图所示。6）选择“选项”选项，打开“选项”选项页，点选“覆盖所有现有备份集”单选按钮，该选项用于初始化新的备份或覆盖现在的备份设备；选中“完成后验证备份”复选框，该选项用来核对实际数据库与备份副本，并确保它们在备份完成之后是一致的。设置如下图所示。7）完成设置后，单击“确定”按钮开始备份。完成备份将弹出“备份完成”提示对话框，单击“确定”按钮即完成对数据库的备份工作。对备份文件进行检验，具体步骤如下：1）在MicrosoftSQLServerManagementStudio的“对象资源管理器”窗格中，展开“服务器对象”→“备份设备”节点。2）右击“JWBACK”备份设备，从弹出的快捷菜单中选择“属性”命令。3）在打开的属性窗口中选择“媒体内容”选项，打开“媒体内容”选项页，可以看到jw数据库的备份文件信息，如右图所示。2．使用BACKUP命令执行完整数据库备份使用BACKUP命令执行完整数据库备份的基本语法格式如下：其中：BACKUPDATABASEdatabase_nameTO<backup_device>[,……][WITH[[,]NAME=backup_filename][[,]DESCRIPTION='text'][[,](INIT|NOINIT)]]database_name：指定备份数据库。backup_device：指定备份设备，采用“备份设备类型=设备名”的形式。NAME=backup_filename：指定备份文件名称。DESCRIPTION='text'：给出备份的描述。INIT|NOINIT：INIT表示新备份的数据文件覆盖当前备份设备上所有备份文件，功能相当于设置备份“选项”选项页中“覆盖所有备份集”选项；NOINIT表示将新备份的数据添加到备份设备上已有的备份文件之后，其功能相当于设置备份“选项”选项页中“追加到现有媒体集”选项。【例5-12】使用BACKUP命令为jw数据库进行完整数据库备份。USEmasterBACKUPDATABASEjwTODISK='F:\JWBACK'WITHINIT,NAME='jwdatabasebackup',DESCRIPTION='thisisthefullbackupofjw'运行执行结果如右图所示。5.4.4恢复数据库1．使用MicrosoftSQLServerManagementStudio执行完整数据库恢复1）打开MicrosoftSQLServerManagementStudio，展开服务器→“数据库”节点。2）右击“jw”数据库，从弹出的菜单中选择“任务”→“还原”→“数据库”命令，打开“还原数据库”窗口，如图所示。3）在“还原数据库”窗口中，设置“目标数据库”为“jw”；“目标时间点”可以设置为“最近状态”，通过单击按钮选择先前的时间点，此处选择“最近状态”选项；“还原的源”选项用于指定源设备，单击按钮打开“指定备份”窗口，在“备份媒体”下拉列表框中选择“备份设备”选项，在“备份位置”列表框中添加“JWBACK”备份设备，单击“确定”按钮确认，如下图所示。4）在“还原数据库”窗口中的“常规”选项页中，选择用于还原的备份集，如下图所示。5）在“选项”选项页中，还可以配置还原设置选项，如下图所示。覆盖现有数据库：允许还原操作覆盖现有的任何数据库及其相关文件。保留复制设置：当正在还原一个发布的数据库到一个服务器的时候，确保保留任何复制的设置。设置此选项时，必须点选“回滚未提交的事务，使数据库处于可以使用的状态。无法还原其他事务日志”单选按钮。还原每个备份之前进行提示：在成功完成一个还原并且在下一个还原之前自动提示，提示包含一个“取消”按钮，它用于在一个特定的备份被还原的过程中取消该还原操作。当需要为不同的媒体集更换磁带时，此选项很有用。限制访问还原的数据库：将数据库设置为dbo，dbcreator以及sysadmin能够访问的限制用户模式。备选项的意义如下：“选项”选项页中的“恢复状态”选项组可以设置数据库的恢复状态。使数据库处于可用状态：完成整个还原过程，并且应用所有选择的备份。所有完成的事务日志被应用，任何未完成的事务被回滚。当还原过程完成后，数据库返回可以使用的状态。这相当于执行RESTOREWITHRECOVERY语句。使数据库处于不可用状态：SQLServer未完成整个还原过程，后继还有其他还原操作。当还原完成时，数据库没有返回就绪状态，不能使用数据库进行正常操作。这相当于执行RESTOREWITHNORECOVERY语句。使数据库处于只读状态：还原过程结束后，数据库处于只读模式，且可准备应用额外的事务日志。在只读模式下，可以检查数据和测试数据库。如果有必要，可应用额外的事务日志，并可将最后的事务日志的模式设置为“使数据库处于可操作状态”，从而使所有完成的事务日志被应用，其他没有完成的事务日志被回滚。这相当于执行RESTOREWITHSTANDBY语句。6）设置完成后，单击“确定”按钮即可。2．使用RESTORE语句完成数据库恢复使用RESTORE命令执行完整数据库恢复的基本语法格式如下：RESTOREDATABASEdatabase_name[FROM<backup_device>[…n]][WITH[FILE=file_number][[,]MOVE'logical_file_name'TO'operating_system_file_name'][…n][[,]{NORECOVERY|RECOVERY}][[,]REPLACE][[,]RESTART]]其中：database_name：指定要恢复的数据库名称。backup_device：指定从哪个备份设备中恢复。FILE=file_number：指定从备份设备上第几个备份中恢复。NORECOVERY|RECOVERY：NORECOVERY选项相当于图形化界面的“选项”选项页中“恢复状态”选项组的第一个选项，使数据库处于可用状态；RECOVERY选项相当于图形化界面的“选项”选项页中“恢复状态”选项组的第二个选项，使数据库处于不可用状态。REPLACE：指定SQLServer创建一个新的数据库，并将备份恢复到这个新的数据库。如果服务器上存在一个同名的数据库，则原数据库被删除。MOVE'logical_file_name'TO'operating_system_file_name'：将数据库恢复到不同的文件。logical_file_name为数据库中的逻辑文件名，operating_system_file_

name为将该文件恢复的物理文件名。RESTART：在上一次还原操作意外中断时使用，指定此次恢复从上次中断的地方开始。【例5-13】使用RESTORE命令为jw数据库进行完整数据库恢复。USEmasterRESTOREDATABASEjwFROMJWBACKWITHFILE=1,NORECOVERY,REPLACE运行结果如图所示。5.5数据库快照5.5.1数据库快照的定义数据库快照是某个指定时间点上数据库（源数据库）的只读、静态视图，相当于用户在某一时间点为数据库拍摄的静态图像。多个快照可以位于一个源数据库中，并且可以作为数据库始终驻留在同一服务器实例上。数据库快照是一个数据库的只读副本，是数据库在创建快照时刻的所有数据的映射。在意外或恶意损坏数据库中数据的情况下，数据库快照作为快速还原点将非常有用。但是，它不能用来替代备份，因为它不包含数据库中的所有记录。5.5.2数据库快照的工作原理数据库快照是在数据页级进行的。当创建了一个数据库的数据库快照后，它为源数据库创建了一个数据库的壳；随着数据库内文件不断被修改，这些修改页都被写入稀疏文件中。数据库快照就是使用稀疏文件来维护源数据的，其工作方式如下图所示。5.5.3数据库快照的优点和局限性1．数据库快照的优点使用数据库快照有如下优点：数据库快照非常适用于存档用户仍需访问的历史数据。数据库快照可以在出现用户错误或丢失数据时，用于恢复到数据库的一个早期的副本。数据库快照用于产生报表时可以提高性能，因为在一个用户从快照中读取数据期间，其他用户可以继续向源数据库中写数据，而不必等第一个用户完成数据读取。2．数据库快照的局限性与源数据库相比，数据库快照存在如下限制。快照必须位于源数据库所在的服务器。不能为model，master，tempdb