SQL Server教程第10章数据库维护.docx

1、SQL Server教程第10章数据库维护 学校教 师 教 案课 程班 级上课时间任课教师课 时审 签教 材SQL Server 2005实用教程参考材料章 节第10章 数据库维护10.1 数据库备份与还原10.2 数据库分离与附加10.3 数据导入导出10.4 数据库快照教学目标 理解数据库备份、数据库还原的概念。 掌握数据库备份和还原的方法。 掌握数据库分离与附加的方法。 掌握数据的导入和导出的方法。教学重点教学难点教学工具教 案第10章 数据库维护10.1 数据库备份与还原10.1.1 备份与还原概述1. 备份概述(1) 备份内容(2) 备份频率(3) 备份设备(4) 备份类型2. 还原

2、概述(1) 恢复模式(2) 恢复模式的设置使用ALTER DATABASE命令可以修改数据库的恢复模式属性，该命令的语法如下。ALTER DATABASE DatabaseName SET RECOVERY FULL | BULK_LOGGED | SIMPLE 例10-1 将BlueSkyDB数据库的恢复模式设置为简单恢复模式。USE masterGOALTER DATABASE BlueSkyDB SET RECOVERY SIMPLEGO10.1.2 备份及还原用户数据库1. 创建备份设备创建备份设备的命令是存储过程sp_addumpdevice，该系统存储过程的使用方法如下。sp_ad

3、dumpdevice devtype = DeviceType , logicalname = LogicalName , physicalname = PhysicalName 例10-2 首先在C盘根目录创建一个文件夹dump。创建一个名为mydiskdump的磁盘备份设备，其物理名称为C:dumpdump1.bak。USE masterGOEXEC sp_addumpdevice disk, mydiskdump, c:dumpdump1.bakGO例10-3 创建一个名为tapedump1的磁带备份设备，其物理名称为.tape0。USE masterGOEXEC sp_addumpde

4、vice tape,tapedump1,.tape0GO删除备份设备的命令是存储过程sp_dropdevice，该系统存储过程的使用方法如下。sp_dropdevice logicalname = device , delfile = delfile 例10-4 删除名为tapedump1的磁带备份设备。USE masterGOEXEC sp_dropdevice tapedump1GO2. 备份数据库(1) 备份整个数据库BACKUP DATABASE DatabaseName TO ,.n WITH DESCRIPTION = text , DIFFERENTIAL , INIT | NO

5、INIT , NAME = BackupSetName (2) 备份文件或文件组BACKUP DATABASE DatabaseName ,.f TO ,.n WITH DESCRIPTION = text , DIFFERENTIAL , INIT | NOINIT , NAME = BackupSetName (3) 备份事务日志BACKUP LOG DatabaseName TO ,.n WITH DESCRIPTION = text , INIT | NOINIT , NAME = BackupSetName , NO_TRUNCATE , NORECOVERY | STANDBY =

6、 UndoFileName ：= LogicalBackupDeviceName | DISK| TAPE = PhysicalBackupDeviceName ：= FILE = LogicalFileName | FILEGROUP = LogicalFilegroupName 例10-5 将数据库BlueSkyDB中的文件组UserGroup2和UserGroup3设为只读，然后对数据库BlueSkyDB进行完全备份，将备份存储到名为mydiskdump的备份设备上，并且覆盖所有的备份集。USE masterGOALTER DATABASE BlueSkyDBMODIFY FILEGRO

7、UP UserGroup2 READONLYGOALTER DATABASE BlueSkyDBMODIFY FILEGROUP UserGroup3 READONLYGOBACKUP DATABASE BlueSkyDB TO mydiskdump WITH NAME=BlueSkyDB完整备份,INITGO例10-6 对数据库BlueSkyDB进行差异备份，将备份存储到名为mydiskdump的备份设备上，并将本次备份追加到指定的媒体集上。将日志备份到名为磁盘C:dumpdumplog.bak文件上，并且覆盖所有的备份集。USE masterGOALTER DATABASE BlueSky

8、DB SET RECOVERY FULLGOBACKUP DATABASE BlueSkyDB TO mydiskdump WITH DIFFERENTIAL,NAME=BlueSkyDB差异备份GOBACKUP LOG BlueSkyDB TO DISK=c:dumpdumplog.bak WITH NAME=BlueSkyDB日志备份,INITGO例10-7对数据库BlueSkyDB中的文件组UserGroup1进行备份，将备份存储到磁盘c:dumpdump2.bak文件上，并且覆盖所有的备份集。将日志备份到名为 mydiskdumpLog 的备份设备上，其物理名称为C:dumpdumpl

9、og1.bak，并且覆盖所有的备份集。USE masterGOALTER DATABASE BlueSkyDB SET RECOVERY FULLGOEXEC sp_addumpdevice disk,mydiskdumpLog,c:dumpdumplog1.bakGOBACKUP DATABASE BlueSkyDB FILEGROUP =UserGroup1 TO DISK=c:dumpdump2.bak WITH NAME=BlueSkyDB文件组备份,INITGOBACKUP LOG BlueSkyDB TO mydiskdumpLog WITH NAME=BlueSkyDB文件组日志

10、备份,INITGO3. 还原数据库(1) 还原整个数据库RESTORE DATABASE DatabaseName FROM ,.n WITH , FILE = FileNumber , MOVE LogicalFileName TO OperatingSystemFileName , RECOVERY | NORECOVERY | STANDBY = StandbyFileName , REPLACE (2) 还原部分数据库RESTORE DATABASE DatabaseName FROM ,.n WITH PARTIAL , FILE = FileNumber , MOVE Logica

11、lFileName TO OperatingSystemFileName , NORECOVERY , REPLACE (3) 还原文件或文件组RESTORE DATABASE DatabaseName ,.f FROM ,.n WITH , FILE = FileNumber , MOVE LogicalFileName TO OperatingSystemFileName , NORECOVERY , REPLACE (4) 还原事务日志RESTORE LOG DatabaseName FROM ,.n WITH , FILE = FileNumber , MOVE LogicalFile

12、Name TO OperatingSystemFileName , RECOVERY | NORECOVERY | STANDBY = StandbyFileName , REPLACE ：= LogicalBackupDeviceName | DISK| TAPE = PhysicalBackupDeviceName ：= FILE = LogicalFileName | FILEGROUP = LogicalFilegroupName 例10-8 利用备份文件c:dumpdump2.bak，恢复数据库BlueSkyDB中的文件组UserGroup1，同时利用备份设备mydiskdumpLo

13、g进行数据库BlueSkyDB的日志恢复。USE masterGORESTORE DATABASE BlueSkyDB FILEGROUP =UserGroup1 FROM DISK=c:dumpdump2.bakGORESTORE LOG BlueSkyDB FROM mydiskdumpLogGO例10-9 利用备份设备mydiskdump中的“BlueSkyDB完整备份”进行数据库完全恢复，恢复后的数据库名为BlueSkyDB。利用备份设备mydiskdump中的“BlueSkyDB差异备份”进行数据库差异恢复，恢复后的数据库名为BlueSkyDB，同时利用磁盘c:dumpdumplog

14、.bak文件进行数据库BlueSkyDB的日志恢复。USE masterGORESTORE DATABASE BlueSkyDB FROM mydiskdumpWITH FILE=1,NORECOVERY,REPLACEGORESTORE DATABASE BlueSkyDB FROM mydiskdumpWITH FILE=2,NORECOVERY,REPLACEGORESTORE LOG BlueSkyDB FROM DISK=c:dumpdumplog.bakGO例10-10 利用备份设备mydiskdump中的“BlueSkyDB完整备份”进行文件组“UserGroup1”的部分恢复，

15、还原后的数据库名为“BlueSkyDB1”。USE masterGORESTORE DATABASE BlueSkyDB1 FILEGROUP =UserGroup1FROM mydiskdumpWITH PARTIAL,REPLACE,MOVE BSGroup1File1 TO F:Group1File1Data.ndf,MOVE BSGroup1File2 TO F:Group1File2Data.ndf,MOVE BSPrimaryFile1 TO F:PrimaryFile1Data.mdf,MOVE BSLogFile1 TO F:LogFile1.ldfGO10.1.3 备份和还原

16、系统数据库1. 备份和还原master数据库2. 备份和还原model和msdb数据库10.2 数据库分离与附加例10-11 将数据库BlueSkyDB从当前的数据库服务器中分离出去。步骤1 启动SSMS，使用“Windows身份验证”使“对象资源管理器”连接到本地服务器。步骤2 在【对象资源管理器】中展开【数据库】节点，右键单击BlueSkyDB数据库节点，在弹出的快捷菜单中选择【任务】命令，在弹出的下一级菜单中选择【分离】命令。步骤3 弹出【分离数据库】对话框，【更新统计信息】复选按钮选择与否，是用来指定是否设置更新统计信息，单击【确定】按钮关闭【分离数据库】对话框，在SSMS的【对象资源

17、管理器】窗口中已经看不到数据库BlueSkyDB了。例10-12 将数据库BlueSkyDB附加到当前的数据库服务器中。步骤1 启动SSMS，使用“Windows身份验证”使“对象资源管理器”连接到本地服务器。步骤2 在【对象资源管理器】中，右键单击【数据库】节点，在弹出的快捷菜单中选择【附加】命令。步骤3 弹出【附加数据库】对话框，在【要附加的数据库】区域单击【添加】按钮，在弹出的【定位数据库文件】对话框中选择数据库的主文件，单击【确定】按钮返回【附加数据库】对话框，如图10-7所示，单击【确定】按钮，完成数据库附加操作。如果如图10-8所示，则表示有文件在原有路径中无法找到，需要用户重新定

18、位。在【“BlueSkyDB”数据库详细信息】区域中单击【当前文件路径】中D:BlueSkyGroup1File1Data.ndf项右侧的按钮，在弹出的【定位数据库文件】对话框中选择文件的正确位置，单击【确定】按钮返回【附加数据库】对话框，单击【确定】按钮，完成数据库附加操作。图10-7附加数据库对话框设置1图10-8 附加数据库对话框设置210.3 数据导入导出例10-13 将数据库BlueSkyDB中的数据表Categories导出到EXCEL文件D:Categories.xls中。步骤1 启动SSMS，使用“Windows身份验证”使“对象资源管理器”连接到本地服务器。步骤2 在【对象资

19、源管理器】中展开【数据库】节点，右键单击BlueSkyDB数据库节点，在弹出的快捷菜单中选择【任务】命令，在弹出的子菜单中选择【导出数据】命令，弹出【SQL SERVER导入和导出向导】对话框。步骤3 向导第一页，在该页进行数据源选择：在【数据源】下拉列表中选择【SQL Native Client】，在【服务器名称】下拉列表中选择本地服务器名称，例如【YVONNE】，在【身份验证】中选择【使用Windows身份验证】，【数据库】中输入BlueSkyDB，如图10-9所示。图10-9 SQL Server导入和导出向导之选择数据源步骤4 单击【下一步】按钮，进入向导第二页，在该页进行目标选择。在

20、【目标】下拉列表中选择【Microsoft Excel】，单击【浏览】按钮，弹出【打开】对话框，【查找范围】中选择【D：】，【文件名】中输入Categories.xls，单击【打开】按钮，返回向导，如图10-10所示。图10-10 SQL Server导入和导出向导之选择目标步骤5 单击【下一步】按钮，进入向导第三页，在该页进行表复制或查询的选择。单击选中【复制一个或多个表或视图的数据】选项，如图10-11所示。图10-11 SQL Server导入和导出向导之指定表复制或查询步骤6 单击【下一步】按钮，进入向导第四页，在该页进行源表的选择。在【源】列中选择Categories表所在行的复选按

21、钮，如图10-12所示。图10-12 SQL Server导入和导出向导之选择源表和源视图步骤7 单击【下一步】按钮，进入向导第五页，在该页进行包执行设定。选中【立即执行】的复选按钮，单击【下一步】按钮，进入向导第六页，该页显示出用户在前面的向导页中进行的设置，如果有问题，用户可能通过单击【上一步】按钮进行修改，如果没问题则单击【完成】按钮，进入向导第七页，系统立即按用户的设置进行数据导出，完成后显示【执行成功】，如图10-13所示。单击【关闭】按钮。图10-13 SQL Server导入和导出向导之执行成功例10-14 将EXCEL文件D:Categories.xls中的记录导入数据库Blu

22、eSkyDB中的数据表Categories1中。步骤1 启动SSMS，使用“Windows身份验证”使“对象资源管理器”连接到本地服务器。步骤2 在【对象资源管理器】中展开【数据库】节点，右键单击BlueSkyDB数据库节点，在弹出的快捷菜单中选择【任务】命令，在弹出的子菜单中选择【导出数据】命令，弹出【SQL SERVER导入和导出向导】对话框。步骤3 向导第一页，在该页进行数据源选择。在【数据源】下拉列表中选择【Micorosoft Excel】，单击【浏览】按钮，弹出【打开】对话框，【查找范围】中选择【D：】，【文件名】中输入Categories.xls，单击【打开】按钮，返回向导。步骤

23、4 单击【下一步】按钮，进入向导第二页，在该页进行目标选择。在【目标】下拉列表中选择【SQL Native Client】，在【服务器名称】下拉列表中选择【YVONNE】，在【身份验证】中选择【使用Windows身份验证】，【数据库】中输入BlueSkyDB。步骤5 单击【下一步】按钮，进入向导第三页，在该页进行表复制或查询的选择。单击选中【复制一个或多个表或视图的数据】选项。步骤6 单击【下一步】按钮，进入向导第四页，在该页进行源表的选择。在【源】列中选择Categories所在行的复选按钮，修改其对应的【目标】中的数据表名为Categories1，如图10-14所示。图10-14 SQL

24、Server导入和导出向导之数据导入步骤7 单击【下一步】按钮，进入向导第五页，在该页进行包执行设定。选中【立即执行】的复选按钮，单击【下一步】按钮，进入向导第六页，该页显示出用户在前面的向导页中进行的设置，如果有问题，用户可能通过单击【上一步】按钮进行修改，如果没问题则单击【完成】按钮，进入向导第七页，系统立即按用户的设置进行数据导出，完成后显示【执行成功】，单击【关闭】按钮。10.4 数据库快照10.4.1 数据库快照概述1. 数据库快照的工作原理2. 数据库快照的优点3. 数据库快照的限制10.4.2 数据库快照的使用1. 创建数据库快照用CREATE DATABASE命令创建数据库快照

25、，该命令的语法如下所示。CREATE DATABASE DatabaseSnapshotName ON (NAME = LogicalFileName, FILENAME = OsFileName ) ,.n AS SNAPSHOT OF SourceDatabaseName例10-15对 BlueSkyDB数据库创建数据库快照BlueSkyDB_snapshot。CREATE DATABASE BlueSkyDB_snapshot ON(NAME=BSPrimaryFile1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDa

26、taBSPrimaryFile1.ss),(NAME=BSGroup1File1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataBSGroup1File1.ss),(NAME=BSGroup1File2,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataBSGroup1File2.ss),(NAME=BSGroup2File1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataBSG

27、roup2File1.ss),(NAME=BSGroup3File1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataBSGroup3File1.ss)AS SNAPSHOT OF BlueSkyDBGO2. 查看数据库快照3. 删除数据库快照用DROP DATABASE命令删除数据库快照，该命令的语法如下所示。DROP DATABASE DatabaseSnapshotName例10-31 删除名为BlueSkyDB_snapshot的数据库快照，而不影响源数据库。USE masterGODROP DATABASE BlueSkyDB_snapshotGO10.4.3 利用数据库快照进行数据库恢复用RESTORE DATABASE命令恢复数据库，该命令的语法如下所示。RESTORE DATABASE FROM DATABASE_SNAPSHOT = 例10-32 从数据库快照BlueSkyDB_snapshot中恢复数据库。USE masterRESTORE DATABASE BlueSkyDB FROM DATABASE_SNAPSHOT = BlueSkyDB_snapshotGO