页数：52页文件大小：886.3 KB来源： SQLServer2005数据库技术及应用ppt课件
本章从数据库的操作系统文件与文件组讲起，通过CREATE DATABASE、ALTER DATABASE等语句演示创建、查看、修改、压缩、重命名和删除数据库的全过程，并说明数据库备份的意义与备份计划，以及用T-SQL创建备份设备、执行完整备份、日志备份、文件组备份与相应恢复操作。
数据库技术及应用
第5章SQL SERVER数据库管理及数据库的备份与恢复
5.1操作系统文件
5.2数据库文件组
5.3创建数据库
5.3.1利用T-SQL语言创建数据库
1．用T-SQL创建数据库
CREATEDATABASEdatabase_name[LOGON{<filespec>[，…n]}]（[NAME=logical_file_name，] FILENAME=‘os_file_name’2．参数说明
LOG ON：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。2．参数说明SQL Server 2005
FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。3、使用CREATE DATABASE创建数据库
CREATE DATABASE SCHOOL ON ( NAME=school_data, FILENAME='d:\data\school_data.mdf',LOG ON ( NAME=school_log, FILENAME='d:\data\school_log.ldf',FILEGROWTH=10%)SQL Server 20053、使用CREATE DATABASE创建数据库3、使用CREATE DATABASE创建数据库
【例5.2】创建一个指定了多个数据和日志文件的数据库。CREATEDATABASEstudentONPRIMARY(NAME=Student1,FILENAME='d:\data\student1.mdf',SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=student2,FILENAME='d:\data\student2.ndf',SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),3、使用CREATE DATABASE创建数据库(NAME=student3,FILENAME='d:\data\student3.ndf',SIZE=100MB,MAXSIZE=200,FILEGROWTH=20)LOGON(NAME=studentlog1,FILENAME='d:\data\studentlog1.ldf',SIZE=10MB,MAXSIZE=20,FILEGROWTH=20%),(NAME=studentlog2,FILENAME='d:\data\studentlog2.ldf',SIZE=10MB,MAXSIZE=20,FILEGROWTH=20%)3、使用CREATE DATABASE创建数据库【例5.3】创建一个定义文件组的数据库。CREATEDATABASEbookONPRIMARY(NAME=book1,FILENAME='d:\data\Book1.mdf',SIZE=10MB,MAXSIZE=30,FILEGROWTH=2),(NAME=book2,FILENAME='d:\data\book2.ndf',SIZE=10MB,MAXSIZE=30,FILEGROWTH=2),3、使用CREATE DATABASE创建数据库SQL Server 2005FILEGROUPbgroup1(NAME=bg1,FILENAME='d:\data\bg1.ndf',SIZE=10MB,MAXSIZE=30,FILEGROWTH=2),(NAME=bg2,FILENAME='d:\data\bg2.ndf',SIZE=10MB,MAXSIZE=30,FILEGROWTH=2),3、使用CREATE DATABASE创建数据库SQL Server 2005FILEGROUPbgroup2(NAME=bg3,FILENAME='d:\data\bg3.ndf',SIZE=10MB,MAXSIZE=30,FILEGROWTH=2),(NAME=bg4,FILENAME='d:\data\bg4.ndf',SIZE=10MB,MAXSIZE=30,FILEGROWTH=2)3、使用CREATE DATABASE创建数据库SQL Server 2005LOGON(NAME=booklog,FILENAME='d:\data\booklog.ldf',SIZE=10MB,MAXSIZE=20,FILEGROWTH=20%)GO这个例子中创建了一个book数据库，包括三个文件组：主文件组包含文件book1和book2，bgroup1文件组包含文件bg1和bg2；bgroup2文件组包含文件bg3和bg4。注意事项：
另外在FILENAME中的路径一定要在语句运行前创建好,比如本例中的数据库保存在D盘下的data文件夹中,如果事先D盘下并不存在data文件夹,那么要先在D盘根目录下创建data文件夹,然后再书写语句运行。5.4查看数据库的信息使用数据库、修改数据库、为数据库排除故障，经常需要了解数据库的信息，SQL Server提供了用于检索数据库信息的语句。可以使用USE语句选择要执行操作的数据库，语法如下：USE{database_name}例如：USEmodel表示当前操作数据库是model，在没用USE改变数据库前，所有的操作都是对model数据库执行。
5.4查看数据库的信息
5.4.1使用SP_HELPDB查看数据库信息使用SP_HELPDD语句的语法如下：SP_HELPDB[@dbname]参数说明：@dbname:指要提供信息的数据库名称。Name的数据类型为sysname，无默认值。如果没有指定name，则查看当前服务器上的所有数据库信息。【例5.4】查看book数据库的信息。SP_HELPDB book5.4查看数据库的信息
5.4.2使用SP_DATABASES查看所有可以使用的数据库使用SP_DATABASES语句的语法如下：SP_DATABASES 5.4.3使用SP_HELPFILE查看当前数据库文件使用SP_HELPFILE语法如下：SP_HELPFILE[@file_name]参数说明：@file_name:要查询的数据库文件的名称。如果没有指定文件名称，那么将查询所有当前数据库上的文件。5.4查看数据库的信息
【例5.5】查看book数据库里所有文件的信息，以及文件bg2的信息。USEbookGOSP_HELPFILEGOSP_HELPFILEbg2GO 5.4.4使用sp_helpfilegroup查看当前数据库的文件组使用SP_HELPFILEGROUP语法如下：SP_HELPFILEGROUP[@filegroup_name]参数说明：@filegroup_name:要查询的数据库文件组的名称。如果没有指定名称，则查询当前数据库中的所有文件组信息。5.4查看数据库的信息
【例5.6】查询数据库book中bgroup2文件组的信息。USEbookGOSP_HELPFILEGROUPbgroup2GO。5.4查看数据库的信息
5.4.5使用SP_SPACEUSED查看当前数据库中的数据空间SP_SPACEUSED语法如下：SP_SPACEUSED[@file_name]参数说明：@file_name:要查询的数据库中的表名。如果没有指定表名，则查询当前数据库中的数据空间信息。【例5.7】查询JWGL数据库中的数据空间以及teacher表数据空间。USEJWGLGOSP_SPACEUSEDGOSP_SPACEUSEDteacherGO5.4查看数据库的信息
5.4.6使用DBCCSQLPERF查看数据库日志空间使用DBCCSQLPERF语法如下：DBCCSQLPERF(LOGSPACE)5.5管理数据库5.5.1修改数据库1．使用T-SQL语句修改数据库使用ALTERDATABASE修改数据库的语法结构如下：ALTERDATABASEdatabasename{addfile<filespec>[,…n][tofilegroupfilegroupname]|addlogfile<filespec>[,…n]|addfilegroupfilegroup_name|removefilelogical_file_name|removefilegroupfilegroup_name|modifyfile<filespec>|modifyfilegroupfilegroup_name{filegroup_property|name=new_filegroup_name}}参数说明
modifyname=new_databasename：重命名数据库。5.5管理数据库【例5.8】向SCHOOL数据库的默认Primary文件组里增加一个数据文件。ALTERDATABASESCHOOLADDFILE(NAME=school_data1,FILENAME='d:\data\school_data1.ndf',SIZE=3MB,MAXSIZE=15MB,FILEGROWTH=1MB)GO5.5管理数据库【例5.9】向book数据库的bgroup2文件组添加两个数据文件，并将该文件组设置为默认文件组。ALTERDATABASEbookADDFILE(NAME=bg5,FILENAME='d:\data\bg5.ndf',SIZE=10MB,MAXSIZE=30,FILEGROWTH=2),5.5管理数据库(NAME=bg6,FILENAME='d:\data\bg6.ndf',SIZE=10MB,MAXSIZE=30,FILEGROWTH=2)TOFILEGROUPbgroup2GOALTERDATABASEbookMODIFYFILEGROUPbgroup2DEFAULTGO5.5管理数据库【例5.10】将book数据库中的bg5数据文件的长度改变为20MB。ALTERDATABASEbookMODIFYFILE(NAME=bg5，SIZE=20MB)GO注意事项：用MODIFYFILE语句修改文件大小时赋予的新值一定要大于初始值。2、使用SQLServerManagementStudio修改数据库5.5管理数据库5.5.2压缩数据库和数据库文件1．使用DBCCSHRINKDATABASE压缩数据库使用DBCCSHRINKDATABASE的语法结构如下：DBCCSHRINKDATABASE(database_name[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}])参数说明
NOTRUNCATE：被释放的文件空间依然保持在数据库文件中。如果未指定，将所释放的文件空间被操作系统回收。 TRUNCATEONLY：将数据文件中未使用的空间释放给操作系统。使用TRUNCATEONLY时，将忽略target_percent的限制。DBCCSHRINKDATABASE(SCHOOL,20) GO5.5管理数据库2．使用DBCCSHRINKFILE压缩数据库指定的数据或日志文件使用DBCCSHRINKFILE的语法结构如下：DBCC SHRINKFILE(file_name{[,target_size]|[,{EMPTYFILE|NOTRUNCATE|TRUNCATEONLY}]})参数说明：NOTRUNCATE和TRUNCATEONLY：与DBCCSHRINKDATABASE命令中相同。5.5管理数据库【例5.12】将student数据库中的数据文件student1压缩到30MB。USEstudentGODBCCSHRINKFILE(student1,30)GO3、使用SQLServerManagementStudio压缩数据库5.5管理数据库5.5.3修改数据库名字1．使用系统存储过程SP_RENAMEDB修改数据库的名字语法如下：SP_RENAMEDB@old_name,@new_name【例5.13】把数据库book的名字改为BK。SP_RENAMEDBbook,BK2．使用SQLServerManagementStudio修改数据库的名字1)打开SQLServerManagementStudio，选择想要修改名字的数据库。2)单击鼠标右键选择“重命名”命令，就可以为数据库更名。5.5管理数据库5.5.4删除数据库1．使用DROP语句删除数据库使用DROP语句的语法结构如下：DROP database database_name[,…n]参数说明：database_name[,…n]：要删除的数据库的名字，可以一次删除多个数据库。只有处于正常状态下的数据库，才能使用DROP语句删除。当数据库处于以下状态时不能被删除：数据库正在使用；数据库正在恢复；数据库包含用于复制的已经出版的对象。【例5.14】删除SCHOOL和student数据库。DROPDATABASESCHOOL,student2．使用SQLServerManagementStudio删除数据库5.6数据库备份概念5.6.1备份的重要意义备份就是对SQL Server数据库或事务日志进行备份，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏时能够及时地将其恢复，使丢失的数据量减少到最小。造成数据丢失的因素很多，常见的有如下情况：
5.6数据库备份概念5.6.2数据库备份计划数据库备份前，应该对备份内容、备份频率以及数据备份存储介质进行计划。5.6.3备份设备创建备份时，必须选择存放备份数据的备份设备。SQLServer使用物理设备名称或逻辑设备名称标识备份设备。物理备份设备是操作系统用来标识备份设备的名称。逻辑备份设备是用来标识物理备份设备的别名或公用名称，用以简化物理设备的名称。例如，一个物理备份设备名可能是D:\Student\data.bak，用逻辑备份设备则可简单的表示为Student_Backup。5.7执行数据库备份与恢复5.7.1创建备份设备1．使用系统存储过程SP_ADDUMPDEVICE创建备份设备用系统存储过程SP_ADDUMPDEVICE可以实现创建永久备份设备的逻辑名称，它们记录在数据库master的sysdevices系统表中；未经SP_ADDUMPDEVICE创建的备份设备为临时备份设备。SP_ADDUMPDEVICE的语法格式为：SP_ADDUMPDEVICE[‘device_type’][,‘logical_name’][,‘physical_name’][,{{controller_type|’device_status’}}]参数说明参数说明：
‘logical_name’：备份设备的逻辑名称，该逻辑名称用于BACKUP或RESTORE语句中。5.7.1创建备份设备【例
5.15】添加本地磁盘备份设备。USEmasterGOEXECSP_ADDUMPDEVICE'disk','mydiskdump','d:\data\student.bak'该例子添加了一个逻辑文件名为mydiskdump的磁盘备份设备，其物理名称为d:\data\student.bak。【例 5.16】添加网络磁盘备份设备USEmasterGOEXECSP_ADDUMPDEVICE'disk','networkdevice',‘\\Servername\sharename\Path\filename.ext'2．使用SQLServerManagementStudio创建备份设备5.7.2备份数据库1．。使用T-SQL语句备份数据库SQLServer针对不同的备份对象给出了不同的备份语句，在这里只是简单介绍一下，分以下三中情况：1)备份整个数据库的简单语法结构如下：BACKUPDATABASE{database_name|@database_name_var}to<backup_device>[,…n][withDIFFERENTIAL]5.7.2备份数据库参数说明：DATABASE：指定一个完整的数据库备份。{database_name|@database_name_var}：指定要备份的数据库。<backup_device>：指定备份操作时要使用的逻辑或物理备份设备。DIFFERENTIAL：说明以差异备份的方式进行备份数据库。差异备份一般会比完整备份占用更少的空间。【例5.17】备份整个book数据库。USEmasterGOEXECSP_ADDUMPDEVICE'disk','bookbp1','d:\data\bookbp1.bak'GOBACKUPDATABASEBooktobookbp1GO5.7.2备份数据库2)备份日志文件的简单语法结构如下：BACKUPLOG{database_name|@database_name_var}to<backup_device>[,…n][withNO_TRUNCATE]参数说明：LOG：指定只备份事务日志。NO_TRUNCATE：表示在完成事务日志备份后，并不清空原有日志的数据。5.7.2备份数据库【例5.18】备份book数据库的日志文件。USEmasterGOEXECSP_ADDUMPDEVICE'disk','booklog1','d:\data\booklog1.bak'GOBACKUPLOGbooktobooklog1GO5.7.2备份数据库3)备份文件或文件组的简单语法结构如下：BACKUPDATABASE{database_name|@database_name_var}<file_or_filegroup>[,…n]to<backup_device>[,…n]<file_or_filegroup>::={FILE={logical_file_name|@logical_file_name_evar}|FILEGROUP={logical_filegroup_name|@logical_filegroup_name_evar}5.7.2备份数据库参数说明:
必须通过使用BACKUPLOG提供事务日志的单独备份，才能使用文件和文件组备份来恢复数据库。5.7.3恢复数据库1．使用T-SQL语句恢复数据库与前面的BACKUP语句相对应，RESTORE语句也有三种形式，分别用来恢复整个数据库备份、事务日志备份以及文件和文件组备份。1)利用数据库备份恢复数据库的语法结构如下：RESTORE DATABASE database_name[from<backup_device[],…n>][WITH[[,]{NORECOVERY|RECOVERY}][[,]REPLACE]]5.7.3恢复数据库参数说明：backup_device：指定还原操作要使用的逻辑或物理备份设备。NORECOVERY：表示在执行恢复操作后不回滚任何未提交的事务。RECOVERY：表示在执行恢复操作后回滚未提交的事务，为默认值。REPLACE：表示如果已经存在具有相同名称的数据库时，则删除已有的数据库，创建指定的数据库。【例5.19】利用数据库备份恢复book数据库。RESTOREDATABASEbookFROMbookbp1WITHNORECOVERY5.7.3恢复数据库2)利用事务日志备份恢复数据库的语法结构如下：RESTORE LOG database_name[from<backup_device[],…n>][WITH[[,]{NORECOVERY|RECOVERY}][[,]STOPAT=date_time]]参数说明：STOPAT=date_time：指定将数据库恢复到指定时刻的状态。【例5.20】利用事务日志备份恢复book数据库。RESTORELOGbookFROMbooklog1WITHSTOPAT='July28,200612:00AM'5.7.3恢复数据库3)利用文件及文件组备份恢复数据库的语法结构如下：RESTOREDATABASEdatabase_name<file_or_filegroup>[,…n][from<backup_device[],…n>][WITH[[,]{NORECOVERY|RECOVERY}][[,]REPLACE]]<file_or_filegroup>::={FILE={logical_file_name|@logical_file_name_evar}|FILEGROUP={logical_filegroup_name|@logical_filegroup_name_evar}具体参数前面已经介绍过，这里就不再说明了。2．使用SQLServerManagementStudio恢复数据库下载地址：{{downurl}}
{{{a}}}
{{#b}}{{{b}}}
{{/b}} {{/title}} {{#nolog}}登陆注册
{{/nolog}} {{#djsx}}点我刷新
{{/djsx}} {{#gocz}} {{/gocz}} {{#ffqr}} {{/ffqr}}