第8章-数据库的日常管理与数据传输

数据库原理与应用主讲：林祥果通讯系电子教研室（09民航运输信息管理）第8章

数据库的创建和管理学习要点：SQLServer数据库的存储结构事务日志的概念数据库的创建、删除、管理主要内容8.1数据库的存储结构8.2创建和管理数据库8.3备份和还原数据库8.4数据的导入导出数据库的存储结构是指数据库文件在磁盘上如何存储。SQLServer中每个数据库由一组操作系统文件组成。数据库中的所有数据、对象和数据库操作日志都存储在这些文件中。8.1数据库的存储结构8.1.1数据库文件数据库文件可以分为三类：主数据库文件次数据库文件事务日志文件1.主数据库文件(PrimaryDatabasefile)每个数据库有且仅有一个主数据库文件，主数据库文件用来存储数据库的启动信息和部分或全部数据。一个数据库可以有1到多个数据库文件，其中只有一个文件为主数据库文件。主数据库文件的文件扩展名为mdf。8.1.1数据库文件2.次数据库文件(SecondaryDatabaseFile)用于存储主数据库文件中未存储的剩余数据和数据库对象。一个数据库可以没有次数据库文件，也可以有多个次数据库文件。次数据库文件的文件扩展名为ndf。3.事务日志文件(TranscationLogFile)用于存储数据库的更新情况等事务日志信息。当数据库损坏时，可以使用事务日志文件恢复数据库。一个数据库可以有1到多个事务日志文件。事务日志文件的扩展名为ldf。8.1.1数据库文件1.逻辑文件名逻辑文件名是在所有Transact-SQL语句中引用文件时所使用的名称。逻辑文件名必须遵守SQLServer标识符的命名规则，且对数据库必须是唯一的。2.物理文件名数据库文件在物理磁盘上的存储路径及文件名称构成数据库文件的物理名称，物理文件名必须遵从操作系统文件名的命名规则。8.1.1数据库文件例如:逻辑文件名物理文件名My_Dbfile1e:\sql_data\pri_file1.mdfMy_Dbfile2e:\sql_data\pri_file2.ndfMy_Dbfile3e:\sql_data\pri_file3.ndfMy_Logfile1e:\sql_log\log_file1.ldfMy_Logfile2e:\sql_log\log_file2.ldf8.1.2数据库文件组为了便于分配和管理，SQLServer允许将多个文件归纳为同一组，并赋予此组一个名称，这就是文件组。1.主文件组(PrimaryFileGroup)所有数据库都至少包含一个主文件组，主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其他文件。

2.次文件组(SecondaryFileGroup)数据库还可以包含用户定义的文件组，也称次文件组。8.1.2数据库文件组3.默认文件组每个数据库中都有一个文件组作为默认文件组运行。当SQLServer给创建时没有为其指定文件组的表或索引分配页时，将从默认文件组中进行分配。一次只能有一个文件组作为默认文件组。默认文件组可以由用户来指定。如果没有指定默认文件组，则主文件组是默认文件组。8.1.2数据库文件组文件组中的每个文件通常建立在不同的硬盘驱动器上，这样可减轻单个磁盘驱动器的存储负载，提高数据库的存储效率，从而提高系统性能。SQLServer2000在没有文件组时也能有效地工作，因此许多系统不需要指定次文件组。在这种情况下，所有文件都包含在主文件组中。事务日志文件是独立的，不能作为任何文件组的成员。数据库文件和文件组的关系数据库数据文件日志文件主文件组（1个）次文件组（0～多个）逻辑文件名1逻辑文件名2逻辑文件名n物理文件名1物理文件名2物理文件名n……逻辑文件名1逻辑文件名2逻辑文件名n物理文件名1物理文件名2物理文件名n……主数据文件8.2创建和管理数据库创建数据库查看数据库修改数据库删除数据库8.2.1创建数据库创建数据库的过程实际上是确定数据库的名称、设计数据库所占用的存储空间和文件的存放位置。每个新创建的数据库都包含以下数据库对象：关系图、表、视图、存储过程、用户、角色、规则、默认、用户定义的数据类型和用户定义的函数。创建数据库的途径:两个途径企业管理器Transact-SQL语句“数据库属性”对话框向导一.

使用向导创建数据库菜单：工具→向导→创建数据库向导→……工具栏:一.

使用向导创建数据库一.

使用向导创建数据库一.

使用向导创建数据库数据库文件初始大小数据库文件逻辑名称物理文件名将默认为：逻辑文件名.MDF一.

使用向导创建数据库按百分比增长：发生增长时文件大小百分比一.

使用向导创建数据库事务日志文件初始大小事务日志文件逻辑名称物理文件名将默认为：逻辑文件名.LDF一.

使用向导创建数据库一.

使用向导创建数据库二.使用“数据库属性”对话框创建数据库菜单操作:右击数据库文件夹或在其下属任一数据库图标，选择“新建数据库”选项。工具栏操作:二.

使用“数据库属性”创建数据库单击数据文件选项卡，设置其中的选项。在这里可以直接定义新的文件组二.

使用“数据库属性”创建数据库单击事务日志选项卡，设置其中的选项。三.使用T-SQL语句创建数据库格式：CREATEDATABASE数据库名称[ON[<文件说明>[,...n]][,<文件组>[,...n]]][LOGON{<文件说明>[,...n]}]参数说明：数据库名称:新数据库的名称。ON关键字:其后的参数指定用来存储数据库数据的磁盘文件（数据文件）。<文件说明>:定义主文件组的数据文件。<文件组>:定义用户文件组及其文件。LOGON:指定日志文件。其后的<文件说明>用以定义日志文件。如果没有指定LOGON，将自动创建一个日志文件，该文件使用系统生成的名称，大小为数据库中所有数据文件总大小的25%。三.使用T-SQL语句创建数据库<文件说明>和<文件组>进一步定义如下：<文件说明>::= [PRIMARY] ([NAME=逻辑文件名,] FILENAME='物理文件名' [,SIZE=初始大小] [,MAXSIZE={最大限制|UNLIMITED}] [,FILEGROWTH=增长量])[,...n] <文件组>::= FILEGROUP文件组名称<文件说明>[,...n]三.使用T-SQL语句创建数据库CREATEDATABASEdatabase_name[ON[PRIMARY] [(NAME=logical_file_name,] FILENAME=‘os_file_name' [,SIZE=size] [,MAXSIZE={max_size|UNLIMITED}] [,FILEGROWTH=growth_increment])[,…n] [,<filegroupspec>[,…n]][LOGON{(NAME=logical_file_name,] FILENAME=‘os_file_name' [,SIZE=size] [,MAXSIZE={max_size|UNLIMITED}] [,FILEGROWTH=growth_increment])[,…n]}]【例8-1】设已经在e盘建立了两个文件夹：e:\sql_data和e:\sql_log，分别用来存放数据库文件和事务日志文件，使用CREATEDATABASE语句创建一个company数据库:三.使用T-SQL语句创建数据库CREATEDATABASEcompany--创建数据库ONPRIMARY--定义在主文件组上的文件( NAME=company_data,--逻辑名称

FILENAME='e:\sql_data\company.mdf',--物理名称

SIZE=10,--初始大小为10MB MAXSIZE=unlimited,--最大限制为无限大

FILEGROWTH=10%--增长速度为10%)LOGON--定义事务日志文件( NAME=company_log,--逻辑名称

FILENAME='e:\sql_log\company.ldf',--物理名称

SIZE=1,--初始大小为1MB MAXSIZE=5,--最大限制为5MB FILEGROWTH=1--增长速度为1MB)三.使用T-SQL语句创建数据库练习8-1创建一个数据库sample。主文件为sample_dat.mdf,逻辑文件名为sample_dat，大小为5MB，最大值50MB，按10%增长。CREATEDATABASEsampleONPRIMARY(NAME=sample_dat,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\sample_data.mdf',SIZE=5,MAXSIZE=50,FILEGROWTH=10)【例8-2】创建一个雇员信息数据库，包含两个数据文件和两个事务日志文件。三.使用T-SQL语句创建数据库CREATEDATABASEemployeesONPRIMARY--定义在主文件组上的文件( NAME=employee1,--主数据文件逻辑名称

FILENAME='e:\sql_data\employee1.mdf',--物理名称

SIZE=10,--主数据文件初始大小为10MB MAXSIZE=unlimited,--最大限制为无限大

FILEGROWTH=10%--增长速度为10%),( NAME=employee2,--次数据文件逻辑名称

FILENAME='e:\sql_data\employee2.ndf',--物理名称

SIZE=20,--次数据文件初始大小为20MB MAXSIZE=100,--次数据文件最大限制为100MB FILEGROWTH=1--次数据文件增长速度为1MB)三.使用T-SQL语句创建数据库LOGON--定义事务日志文件( NAME=employeelog1,--事务日志文件逻辑名文件

FILENAME='e:\sql_log\employeelog1.ldf',--物理名称

SIZE=10,--初始大小为50MB MAXSIZE=50,--最大限制为50MB FILEGROWTH=1--增长速度为1MB),( NAME=employeelog2,--事务日志逻辑文件名

FILENAME='e:\sql_log\employeelog2.ldf',--物理名称

SIZE=10,--初始大小为10MB MAXSIZE=50,--最大限制为50MB FILEGROWTH=1--增长速度为1MB)三.使用T-SQL语句创建数据库练习8-2创建一个只包含一个数据文件和一个日志文件的数据库。该数据库名为Sales，数据文件的逻辑名为Sales_data，数据文件的操作系统名为sales_data.mdf，初始大小为10MB，最大可增加至500MB，增幅为10％；日志文件的逻辑名为Sales_log，操作系统名为sales_log.ldf，初始大小为5MB，最大值为100MB，日志文件大小以2MB增幅增加。

CREATEDATABASESales

ON

(NAME=Sales_data,

FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\sales_data.mdf',

SIZE=10MB,

MAXSIZE=500MB,

FILEGROWTH=10%)LOGON(NAME=Sales_log,

FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\sales_log.ldf',

SIZE=5MB,

MAXSIZE=100MB,

FILEGROWTH=2MB)【例8-3】创建test数据库，包含一个主文件组和两个次文件组，每个文件组包含两个文件。三.使用T-SQL语句创建数据库CREATEDATABASEtestONPRIMARY--定义在主文件组上的文件( NAME=pri_file1, FILENAME='e:\sql_data\pri_file1.mdf', SIZE=10,MAXSIZE=50,FILEGROWTH=15%),( NAME=pri_file2, FILENAME='e:\sql_data\pri_file2.ndf', SIZE=10,MAXSIZE=50,FILEGROWTH=15%),三.使用T-SQL语句创建数据库FILEGROUPGrp1--定义在次文件组Grp1上的文件( NAME=Grp1_file1, FILENAME='e:\sql_data\Grp1_file1.ndf', SIZE=10,MAXSIZE=50,FILEGROWTH=5),( NAME=Grp1_file2, FILENAME='e:\sql_data\Grp1_file2.ndf', SIZE=10,MAXSIZE=50,FILEGROWTH=5),三.使用T-SQL语句创建数据库FILEGROUPGrp2--定义在次文件组Grp2上的文件( NAME=Grp2_file1, FILENAME='e:\sql_data\Grp2_file1.ndf', SIZE=10,MAXSIZE=50,FILEGROWTH=5),( NAME=Grp2_file2, FILENAME='e:\sql_data\Grp2_file2.ndf', SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON--定义事务日志文件( NAME='test_log', FILENAME='e:\sql_log\test_log.ldf', SIZE=5,MAXSIZE=25,FILEGROWTH=5)三.使用T-SQL语句创建数据库练习2-3创建一个包含多个数据文件和日志文件的数据库。该数据库名为student，含有三个初始大小为10MB的数据文件和两个8MB的日志文件。

CREATEDATABASEstudentONPRIMARY(NAME=std_dat1,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\student1.mdf',SIZE=10MB,MAXSIZE=200MB,FILEGROWTH=20),(NAME=std_dat2,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\student2.ndf',SIZE=10MB,MAXSIZE=200MB,FILEGROWTH=20),(NAME=std_dat3,

FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\student3.ndf',

SIZE=10MB,

MAXSIZE=200MB,

FILEGROWTH=20)

LOGON

(NAME=std_log1,

FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\stdlog1.ldf',

SIZE=8MB,

MAXSIZE=100MB,

FILEGROWTH=10MB),

(NAME=std_log2,

FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\stdlog2.ldf',

SIZE=8MB,

MAXSIZE=100MB,

FILEGROWTH=10MB)练习8-4创建一个包含两个文件组的数据库。该数据库名为business，主文件组包含business_dat1和business_dat2两个数据文件。文件组business_group包含文件数据文件business_dat3。该数据库还包含一个日志文件business_log。

CREATEDATABASEbusinessONPRIMARY(NAME=business_dat1,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\businessdat1.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10),(NAME=business_dat2,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\businessdat2.ndf',SIZE=10MB,MAXSIZE=500MB,FILEGROWTH=10),FILEGROUPbusiness_group(NAME=business_dat3,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\businessdat3.ndf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%)LOGON(NAME=business_log,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\businesslog.ldf',SIZE=8MB,MAXSIZE=100MB,FILEGROWTH=10MB)创建数据库需要注意以下几点：每个数据库都有一个所有者，可以在该数据库中执行某些特殊的活动，数据库被创建之后，创建数据库的用户自动成为该数据库的所有者。默认情况下，只有系统管理员和数据库所有者可以创建数据库，也可以授权其他用户创建数据库。在每个SQLServer实例下，最多只能创建32767个数据库。所创建的数据库名称必须符合标识符的命名规则。要让日志文件能够发挥作用，通常将数据文件和日志文件存储在不同的物理磁盘上。三.使用T-SQL语句创建数据库执行CREATEDATABASE语句后，可以在企业管理器中查看创建数据库的结果。方法是:

展开数据库文件夹→右击的数据库→选择“属性”,打开数据库属性对话框，从各选项卡上可以查看所创建数据库的各种属性。8.2.2查看数据库例如,test数据库的属性对话框:8.2.2查看数据库用T-SQL语句查看数据库的属性使用系统存储过程sp_helpdb可以查看某个数据库或所有数据库的属性。格式：sp_helpdb

database_name

该存储过程显示了Sales数据库的名称、大小、所有者、创建日期以及数据文件和日志文件等属性。例8-4查看数据库Sales的属性。

sp_helpdbSales例8-5查看所有数据库的属性

sp_helpdb

8.2.3修改数据库创建数据库之后，可以在企业管理器中利用数据库属性对话框直接修改创建时的某些设置，或修改创建时无法设置的属性，也可以使用Transcat-SQL语言的ALTERDATABASE语句修改数据库。一.使用企业管理器修改数据库展开数据库文件夹→右击数据库名称→选择“属性”命令，打开数据库属性对话框。在该对话框上有多个选项卡，可以在各选项卡上查看或修改数据库文件及其他属性。一.使用企业管理器修改数据库“数据库属性”对话框各选项说明如下：限制访问:指定只有db_owner、dbcreator

或sysadmin

的成员的用户才可以访数据库。单用户:指定一次只能有一个用户访问数据库。模型:指定数据库的恢复模型类型。以确定如何备份数据以及能承受何种程度的数据丢失ANSINULL默认设置:指定将数据表列默认定义为NULL还是NOTNULL。当选择此选项时，在CREATETABLE或ALTERTABLE语句过程中，没有显式定义为NOTNULL的列都将默认为允许空值。一.使用企业管理器修改数据库递归触发器:允许触发器递归调用。SQLServer设定的触发器递归调用的层数最多为32层。自动更新统计信息:指定在优化期间自动生成查询优化所需的过时统计信息。残缺页检测:允许自动检测有损坏的页。自动关闭:当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。自动收缩:允许定期对数据库进行检查，当数据库文件或日志文件的未用空间超过其大小的25%时，系统将会自动缩减文件使其未用空间等于25%。一.使用企业管理器修改数据库自动创建统计信息:指定在优化期间自动生成优化查询所需的任何缺少的统计信息。使用被引用的标识符:指定SQLServer将强制执行关于引号的ANSI规则。选择此选项指定双引号只能用于标识符，比如列和表的名称。字符串必须包含在单引号内。格式：ALTERDATABASE数据库名称{ADDFILE<文件说明>[,...n][TOFILEGROUP文件组名称]|ADDLOGFILE<文件说明>[,...n]|REMOVEFILE逻辑文件名|ADDFILEGROUP文件组名称|REMOVEFILEGROUP文件组名称|MODIFYFILE<文件说明>|MODIFYNAME=新数据库名|MODIFYFILEGROUP文件组名称{文件组属性|NAME=新文件组名称}二.使用T-SQL语句修改数据库参数说明：数据库名称：是要更改的数据库的名称。ADDFILE：指定要添加文件。该文件由后面的<文件说明>指定。<文件说明>定义如下：

<文件说明>::=(NAME=逻辑文件名

[,NEWNAME=新逻辑文件名][,FILENAME='物理文件名'][,SIZE=大小][,MAXSIZE={最大限制|UNLIMITED}][,FILEGROWTH=增长量])二.使用T-SQL语句修改数据库TOFILEGROUP：表示要将指定的文件添加到其后指定的的文件组中。ADDLOGFILE：表示要将其后指定的日志文件添加到指定的数据库中。REMOVEFILE：从数据库系统表中删除文件描述并删除物理文件。ADDFILEGROUP：指定要添加文件组。REMOVEFILEGROUP：从数据库中删除文件组。只有当文件组为空时才能将其删除。二.使用T-SQL语句修改数据库MODIFYFILE:表示要更改指定的文件，可以更改文件名称、大小、增长情况和最大限制。一次只能更改一种属性。如果指定了SIZE，那么新的大小必须比文件当前大小还大。MODIFYNAME=新数据库名：表示要重命名数据库。MODIFYFILEGROUP文件组名称{文件组属性|NAME=新文件组名称}：指定要修改的文件组和所需的改动。如果指定“文件组名称”和“NAME=新文件组名称”，则将此文件组的名称改为新文件组名称。如果指定“文件组名称”和“文件组属性”，则表示修改文件组的属性。二.使用T-SQL语句修改数据库“文件组属性”的值有：READONLY——指定文件组为只读。不允许更新其中的对象。主文件组不能设置为只读。READWRITE——指定文件组为读写属性。允许更新文件组中的对象。只有具有排它数据库访问权限的用户才能将文件组标记为读/写。DEFAULT——将文件组指定为默认数据库文件组。只能有一个数据库文件组是默认的。二.使用T-SQL语句修改数据库二.使用T-SQL语句修改数据库【例8-6】添加一个事务日志文件到employees数据库中。ALTERDATABASEemployeesADDLOGFILE--添加日志文件(NAME=employeelog3,FILENAME='e:\sql_log\employeelog3.ldf',SIZE=1,MAXSIZE=50,FILEGROWTH=1)二.使用T-SQL语句修改数据库【例8-7】添加一个包含两个数据文件的文件组到employees数据库中。二.使用T-SQL语句修改数据库

ALTERDATABASEemployees ADDFILEGROUPdata1--添加文件组data1ALTERDATABASEemployeesADDFILE--添加数据文件

(NAME=employee3,FILENAME='e:\sql_data\employee3.ndf',SIZE=1,MAXSIZE=50,FILEGROWTH=1),

(NAME=employee4,FILENAME='e:\sql_data\employee4.ndf',SIZE=2,MAXSIZE=50,FILEGROWTH=10%)TOFILEGROUPdata1--将以上两个文件添加到data1文件组MODIFYFILEGROUPfilegroup_name{filegroup_property

|NAME=new_filegroup_name}：修改某一文件组的属性。练习8-7为数据库sample添加一个数据文件sample_dat2和一个日志文件sample_log2。ALTERDATABASEsampleADDFILE(NAME=sample_dat2,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\sample_dat2.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)ALTERDATABASEsampleADDLOGFILE(NAME=sample_log2,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\sample_log2.ldf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)练习8-8为数据库sample添加一个文件组USER1，并向该文件组添加一个二个数据文件sampuser_dat1和sampuser_dat2。

ALTERDATABASEsampleADDFILEGROUPUSER1

ALTERDATABASEsampleADDFILE(NAME=sampuser_dat1,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\sampuser_dat1.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1),(NAME=sampuser_dat2,FILENAME='d:\programfiles\microsoft

sqlserver\mssql\data\sampuser_dat2.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)TOFILEGROUPUSER1【例8-8】删除例8-4中添加到数据库employees中的一个数据文件employee4。

ALTERDATABASEemployees REMOVEFILEemployee4二.使用T-SQL语句修改数据库练习8-9从数据库sample中删除文件sampuser_dat2。ALTERDATABASEsampleREMOVEFILEsampuser_dat2练习8-10删除数据库sample中的文件组USER1。ALTERDATABASEsampleREMOVEFILEsampuser_dat1ALTERDATABASEsampleREMOVEFILEGROUPUSER1注意：在删除文件组时必须先删除文件组中所包含的文件。

【例8-9】给添加到数据库employees中的文件。employee3增加大小。

ALTERDATABASEemployees MODIFYFILE (NAME=employee3, SIZE=5MB)二.使用T-SQL语句修改数据库练习8-11修改数据库sample中数据文件sample_dat2的属性，将其初始大小改为10MB，最大容量改为80MB,增长幅度改为5MB。ALTERDATABASEsampleMODIFFILE(NAME=sample_dat2,SIZE=10,MAXSIZE=80,FILEGROWTH=5)【例8-10】将数据库文件名employees修改成MyEmployeesALTERDATABASEemployeesMODIFYNAME=MyEmployees二.使用T-SQL语句修改数据库练习8-12修改数据库business中文件组business_group的属性，将其改名为group1，并设置为DEFAULT属性（即该文件组为默认文件组）。ALTERDATABASEbusinessMODIFYFILEGROUPbusiness_groupNAME=group1ALTERDATABASEbusinessMODIFYFILEGROUPgroup1DEFAULT练习8-13将数据库student改名为stud_teacher。ALTERDATABASEstudentMODIFYNAME=stud_teacher

重命名数据库语法形式为：sp_renamedb‘old_name’,’new_name’old_name：数据库的当前名称；new_name是数据库的新名称。练习8-14将数据库business更名为company。

sp_renamedb'business','company

8.2.4删除数据库对于不再使用的数据库，可以删除它们以释放所占用的磁盘空间。可以在企业管理器中删除数据库，也可以使用DROPDATABASE语句删除数据库。一.使用企业管理器删除数据库快捷菜单操作：右击所要数据库名称→选择“删除”命令工具栏操作：单击数据库名称→使用按钮弹出对话框如下图。二.使用T-SQL语句删除数据库格式：

DROPDATABASE数据库名称[,…n]【例8-11】删除创建的数据库company。

DROPDATABASEcompany显示结果为：

正在删除数据库文件'e:\sql_log\company.ldf'。正在删除数据库文件'e:\sql_data\company.mdf'。注意：如果数据库当前正在使用，则无法删除该数据库。8.3备份数据库在数据库的使用过程中，难免会由于病毒、人为失误、机器故障等原因造成数据的丢失或损坏。数据对于一个企业、政府部门来说往往是非常重要的，一旦出现问题，造成的损失是巨大的。为了保证数据库的安全性，防止数据库中数据的意外丢失，应经常对数据库中的数据进行备份，以便在数据库出故障的时候进行及时有效的恢复。4.3.1备份概述备份内容系统数据库用户数据库事务日志备份方式(备份类型)完全数据库备份差异数据库备份事务日志备份数据库文件和文件组备份8.3.1备份概述1.完全数据库备份

备份数据库的所有表的数据及模式，以及对应的文件结构，包括对事务日志中的事务进行备份。2.差异数据库备份

只记录自上次完全数据库备份后发生更改的数据。差异备份的数据量比完全备份小而且备份速度快，因此可以更经常地备份，经常备份将减少丢失数据的危险。8.3.1备份概述3.事务日志备份

事务日志是自上次备份事务日志后对数据库执行的所有事务的一系列记录。事务日志备份只考虑在日志中所记录的变化，是基于逻辑操作的备份。可以使用事务日志备份将数据库恢复到特定的即时点或恢复到故障点。4.数据库文件和文件组备份

只备份特定的文件或文件组，常用于超大型数据库的备份。8.3.2创建和删除备份设备磁盘备份设备：指硬盘或其它磁盘存储介质上的文件，与常规操作系统文件一样。可以在本地服务器的磁盘上或远程共享磁盘上定义磁盘备份设备。磁带备份设备：磁带设备只能物理连接到运行SQLServer实例的计算机上。SQLServer不支持备份到远程磁带设备上。在进行备份以前一般要指定或创建备份设备，备份设备是用来存储数据库、事务日志或文件和文件组备份的存储介质。包括磁盘和磁带设备。8.3.2创建和删除备份设备物理设备名称：是操作系统用来标识备份设备的名称，如“D:\MyBackups\Student.bak”。逻辑设备名称：是用来标识物理备份设备的别名或公用名称，用以简化物理设备名称。例如，逻辑设备名称可以是“STDBackup”，而物理设备名称则是“D:\MyBackups\Student.bak”。

SQLServer使用物理设备名称或逻辑设备名称来标识备份设备。8.3.2创建和删除备份设备1.使用企业管理器创建设备

选择服务器→展开“管理”文件夹→右击“备份”→选择“新建备份设备”命令8.3.2创建和删除备份设备打开“备份设备属性”对话框:逻辑备份设备名称物理备份设备名称8.3.2创建和删除备份设备2.使用企业管理器删除备份设备

创建备份设备后，在企业管理器的右侧窗格中会显示该备份设备的名称、物理位置和设备类型，用鼠标右击要删除的备份设备的名称，从弹出的快捷菜单中选择“删除”命令，则可以删除相应的备份设备。8.3.2创建和删除备份设备3．使用系统存储过程创建备份设备格式:sp_addumpdevice[@devtype=]'设备类型',[@logicalname=]'逻辑备份设备名',[@physicalname=]'物理备份设备名'参数说明：[@devtype=]‘设备类型’：指定备份设备的类型，可以是:disk、pipe、tape。[@logicalname=]'逻辑备份设备名':指定逻辑备份设备名称。8.3.2创建和删除备份设备[@physicalname=]'物理备份设备名'：指定物理备份设备名。物理名称必须遵照操作系统文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。对于远程硬盘文件，可以使用格式“\\主机名\共享路径名\路径名\文件名\”表示；对于磁带设备，用“\\.\TAPEn”表示，其中n为磁带驱动器序列号。8.3.2创建和删除备份设备【例8-9】创建磁盘一个备份设备,逻辑名称为“copy1”,物理名称为“d:\Mybackup\company.bak”

EXECsp_addumpdevice

@devtype='disk',@logicalname='copy1',

@physicalname='d:\Mybackup\company.bak'也可以简化成：

EXECsp_addumpdevice

'disk','copy1','d:\Mybackup\company.bak'这里的EXEC表示执行存储过程。8.3.2创建和删除备份设备【例8-12】创建备份设备copy2，使用teacher服务器共享文件夹backup下的文件company1.bak。

EXECsp_addumpdevice

'disk','copy2','\\teacher\backup\company1.bak'【例8-13】用物理设备\\.\TAPE0创建一个磁带备份设备tapedevice。

EXECsp_addumpdevice

'tape','tapedevice','\\.\TAPE0'8.3.2创建和删除备份设备4.使用系统存储过程sp_dropdevice删除备份设备格式:

sp_dropdevice

[@logicalname=]'逻辑备份设备名'[,[@delfile=]'删除文件']参数说明:

@delfile:指是否同时删除物理备份文件。如果该参数指定为DELFILE，那么就会删除设备物理文件名指定的磁盘文件。8.3.2创建和删除备份设备【例8-14】删除例8-11创建的备份设备tapedevice，不删除相应的物理备份文件。

EXECsp_dropdevice'tapedevice'【例8-15】删除例8-9创建的备份设备copy1，并删除相应的物理文件。

EXECsp_dropdevice'copy1','DELFILE'8.3.3执行备份(1)打开备份数据库话框方法一：右击数据库→选择“所有任务”→选择“备份数据库”令方法二：展开“数据库”文件夹→展开“管理”文件夹→右击“备份”→选择“备份数据库”方法三：打开“工具”菜单→选择“备份数据库”1.在企业管理器中使用备份数据库对话框执行备份8.3.3执行备份8.3.3执行备份8.3.3执行备份2.在企业管理器中使用备份向导执行备份(1)单击服务器

→

打开“工具”菜单→选择“向导”或单击

工具栏图标

打开“选择向导”对话框：8.3.3执行备份(2)选择数据库:8.3.3执行备份(3)输入备份名称和描述信息:8.3.3执行备份(4)选择备份类型:8.3.3执行备份(5)选择备份设备和属性:8.3.3执行备份(6)备份验证和调度:8.3.3执行备份(7)完成备份:8.3.3执行备份(1)完全数据库备份格式：

BACKUPDATABASE数据库名称

TO<备份设备>[,...n][WITH[NAME=备份集名称][[,]DESCRIPTION='备份描述文本'][[,]{INIT|NOINIT}]]3.使用BACKUPDATABASE语句备份数据库8.3.3执行备份说明：<备份设备>：指定备份要使用的逻辑或物理备份设备。定义如下：

<备份设备>::={逻辑备份设备名}|{DISK|=TAPE}'物理备份设备名'INIT：指定应重写所有备份集。NOINIT：表示备份集将追加到指定的设备现有数据之后，以保留现有的备份集。完全数据库备份是制作数据库中所有内容的一个副本，备份过程花费时间相对较长，备份占用的空间大，因此不宜频繁进行。8.3.3执行备份【例8-16】将“学生管理”数据库备份到d盘的mybackup文件夹下的“学生管理.bak”文件中。

--首先先创建一个备份设备

sp_addumpdevice'disk','mycopy1','d:\mybackup\学生管理.bak'--用BACKUPDATABASE备份学生管理数据库

BACKUPDATABASE学生管理

TOmycopy1WITHNAME='学生管理备份',DESCRIPTION='完全备份'8.3.3执行备份【例8-15】将“学生管理”数据库备份到网络中的另一台主机ServerX上。

sp_addumpdevice

'disk','STDcopy','\\ServerX\backup\student.dat'BACKUPDATABASE学生管理TOSTDcopy8.3.3执行备份(2)差异数据库备份指对最近一次完全数据库备份结束以来发生改变的数据进行备份。当数据库从上次备份以来只修改了很少的数据时，适合使用差异备份。格式：

BACKUPDATABASE数据库名称

TO<备份设备>[,...n]WITH

DIFFERENTIAL[[,]NAME=备份集名称][[,]DESCRIPTION='备份描述文本'][[,]{INIT|NOINIT}]8.3.3执行备份【例8-16】假设对“学生管理”数据库进行了一些修改，现在要做一个差异备份，且将该备份添加到例8-14的现有备份之后。

BACKUPDATABASE学生管理

TOmycopy1WITHDIFFERENTIAL,NOINIT,NAME='学生管理备份',DESCRIPTION='第一次差异备份'8.3.3执行备份(3)文件或文件组备份当一个数据库很大时，对整个数据库进行备份可能会花费很多时间，这时可以采用文件或文件组备份，即对数据库中的部分文件或文件组进行备份。8.3.3执行备份格式：

BACKUPDATABASE数据库名称

<文件或文件组>[,...n]TO<备份设备>[,...n][WITHDIFFERENTIAL[[,]NAME=备份集名称][[,]DESCRIPTION='备份描述文本'][[,]{INIT|NOINIT}]]<文件或文件组>::={FILE=逻辑文件名|FILEGROUP=逻辑文件组名}8.3.3执行备份【例8-17】将test数据库的grp1_file1文件备份到文件“E:\temp\grp1_file1.dat”中。

BACKUPDATABASEtestFILE='grp1_file1'TODISK='E:\temp\grp1_file1.dat'【例8-18】将test数据库的文件组grp1备份到文件“E:\temp\group1.dat”中。

BACKUPDATABASEtestFILEGROUP='grp1'TODISK='E:\temp\group1.dat'WITHNAME='groupbackupoftest'8.3.3执行备份(4)事务日志备份事务日志是自上次备份事务日志后对数据库执行的所有事务的一系列记录，备份事务日志将对最近一次备份事务日志以来的所有事务日志进行备份。格式：

BACKUPLOG数据库名称

TO<备份设备>[,...n][WITH[[,]NAME=备份集名称][[,]DESCRIPTION='备份描述文本'][[,]{INIT|NOINIT}]]8.3.3执行备份【例8-19】将“学生管理”数据库的日志文件备份到文件“e:\temp\MyLog1.bak”中。

EXECsp_addumpdevice'disk','MyLog1','e:\temp\MyLog1.bak'BACKUPLOG学生管理TOMyLog18.3.4还原数据库数据库备份后，一旦系统发生崩溃或者执行了错误的数据库操作，就可以从备份文件中恢复（还原）数据库，让数据库回到备份时的状态。通常在以下情况下需要恢复数据库。媒体故障。用户操作错误。服务器永久丢失。将数据库从一台服务器复制到另一台服务器。恢复数据库之前，需要限制其他用户访问数据库：右击数据库→选择“属性”→选择“选项”→选择“限制访问”、单用户8.3.4还原数据库恢复内容：恢复整个数据库恢复部分数据库恢复数据库文件或文件组恢复数据库事务日志恢复途径：使用企业管理器中使用RESTOREDATABASE8.3.4还原数据库1.使用企业管理器恢复数据库单击服务器名称→选择“工具”菜单→选择“还原数据库”或右击“数据库”或数据库名称→选择“所有任务”→选择“还原数据库”命令。两种方法都会打开“还原数据库”对话框：8.3.4还原数据库“还原数据库”对话框——“常规”选项卡8.3.4还原数据库“还原数据库”对话框——“选项”选项卡8.3.4还原数据库2.使用RESTOREDATABASE语句恢复数据库(1)恢复完全备份数据库和差异备份数据库格式：

RESTOREDATABASE数据库名称

[FROM<备份设备>[,...n]][WITH[[,]FILE=文件号]

[[,]MOVE'逻辑文件名'TO'物理文件名'][,...n][[,]{NORECOVERY|RECOVERY}][[,]REPLACE]]8.3.4还原数据库说明：文件号：要还原的备份集。例如，文件号为2表示第二个备份集。NORECOVERY：指示还原操作不回滚任何未提交的事务。当还原数据库备份和多个事务日志时，或在需要使用多个RESTORE语句时（例如在完整数据库备份后进行差异数据库备份），SQLServer要求在除最后的RESTORE语句外的所有其它语句上使用WITHNORECOVERY选项。RECOVERY：指示还原操作回滚任何未提交的事务。在恢复完成后即可随时使用数据库。REPLACE：指定如果存在同名数据库，将覆盖现有的数据库。8.3.4还原数据库【例8-20】设在E盘temp文件夹下有一个职工数据库的完全备份文件“职工.bak”，恢复该数据库，将恢复后的数据库名称改为“职工信息”。如果当前服务器中存在“职工信息”数据库，则覆盖该数据库。方法一：使用备份设备。EXECsp_addumpdevice'disk','copy1','E:\temp\职工.bak'RESTOREDATABASE职工信息FROMcopy1WITHMOVE'职工_data'TO'e:\sql_data\职工信息.mdf',MOVE'职工_log'TO'e:\sql_log\职工信息.lgf',REPLACE8.3.4还原数据库方法二：直接指定磁盘文件名

RESTOREDATABASE职工信息

FROMDISK='E:\temp\职工.bak'WITHMOVE'职工_data'TO'e:\sql_data\职工信息.mdf',MOVE'职工_log'TO'e:\sql_log\职工信息.lgf',REPLACE8.3.4还原数据库【例8-21】设第一天做了一个“学生管理”数据库的完全备份,第2天做了一个“学生管理”数据库的差异备份，之后数据库出现故障，将数据库恢复到做差异备份时的状态。8.3.4还原数据库

--恢复完全备份

RESTOREDATABASE学生管理

FROMmycopy1WITHFILE=1,NORECOVERY --这时数据库无法使用，继续恢复差异备份

RESTOREDATABASE学生管理

FROMmycopy1WITHFILE=2,RECOVERY

--这时数据库可以使用8.3.4还原数据库(2)恢复事务日志格式：

RESTORELOG数据库名称

[FROM<备份设备>[,...n]][WITH[[,]FILE=文件号]

[[,]MOVE'逻辑文件名'TO'物理文件名'][,...n][[,]{NORECOVERY|RECOVERY}]]8.3.4还原数据库【例8-22】假设对“学生管理”数据库先后做了完全数据库备份、差异数据库备份和事务日志备份现在利用这三个备份来恢复数据库。

--恢复完全备份

RESTOREDATABASE学生管理

FROMmycopy1WITHFILE=1,NORECOVERY8.3.4还原数据库--这时数据库无法使用，继续恢复差异备份RESTOREDATABASE学生管理FROMmycopy1WITHFILE=2,NORECOVERY--这时数据库仍然无法使用，继续恢复事务日志备份RESTORELOG学生管理FROMMylog1WITHRECOVERY

--完成恢复，数据库可以使用利用数据库的维护计划向导可以方便地设置数据库的核心维护任务，以便于定期地执行这些任务8.4维护数据库8.5数据的导入导出导入导出数据方法概述DTS概述DTS导出向导DTS导入向导在使用SQLServer的过程中，经常需要将其他应用程序（例如Access数据库或ASCII文本文件）的数据移植到SQLServer2000数据库中，或者将SQLServer2000的数据移植到其他数据库中。这种在SQLServer2000和其他应用程序之间的数据转换，就是本章中将要介绍的导入导出数据。8.5.1导入导出数据方法概述导入/导出数据的可以有以下几种方法:使用数据转换服务（DataTransformationServices，DTS）导入/导出向导或DTS设计器创建可用于导入/导出和转换数据的DTS包。使用SQLServer复制技术在整个企业内分发数据。SQLServer中的复制技术可以制作数据复本，将这些复本移到不同位置，以及自动同步数据，从而使所有复本都具有相同的数据值。可以在同一服务器或通过LAN、WAN或Internet连接的不同服务器上的数据库之间执行复制。8.5.1导入导出数据方法概述使用bcp命令提示实用工具在SQLServer实例和数据文件之间导入和导出数据。从某个OLEDB提供程序中选择数据，并将数据从外部数据源复制到SQLServer实例。使用分布式查询从另一个数据源中选择数据并指定要插入的数据。使用INSERT语句将数据添加到现有表中。使用BULKINSERT语句将数据从数据文件导入到SQLServer实例。使用SELECTINTO语句根据现有表创建新表。1.DTS连接数据源连接：包括连接到标准数据库（如SQLServer、Access、Oracle、dBase和Paradox等）、与ODBC数据源的OLEDB连接、Excel电子表格数据、HTML源以及其他OLEDB提供程序。文件连接：DTS对文本文件提供额外的支持。指定文本文件连接时，需要指定文件的格式。例如，文本文件是分隔字段格式还是固定字段格式、文本文件是Unicode格式还是ANSI格式、行分隔符和列分隔符、文本限定符以及第一行是否包含列名等。数据链接连接：在这些连接中，SQLServer外部的中间文件存储连接字符串。2.DTS任务导入和导出数据转换数据复制数据库对象将消息发送到其他用户和包以及从其他用户和包接收消息对数据源执行一组Transact-SQL语句或ActiveX脚本。3.DTS转换DTS转换是在数据到达目的之前应用于一块数据的一个或多个函数或操作。源数据未改变。例如，可以从源数据的一列中析取一个子串并将其复制到目的表。这个特定的子串函数是映射到源列的转换。也可以使用某些特性搜索行并只对