1、实验16 数据备份恢复和导入导出实验十六数据备份、恢复和导入导出【实验目的与要求】1 学会使用企业管理器、备份命令创建和删除命名备份设备。2 掌握使用企业管理器、备份命令进行数据库的备份。3 掌握使用企业管理器和恢复命令进行数据库的恢复。4 掌握数据的导入导出。【实验内容与步骤】16.1 正常情况下的数据备份与恢复1、创建命名备份设备在C:Program FilesMicrosoft SQL ServerMSSQLBACKUP下创建一个名为“test1”的命名备份设备。2、数据库的备份和恢复（1）将整个CPXS数据库备份到test1，并利用该备份集恢复CPXS数据库。（2）在CPXS数据库中增
2、加表s1。create table s1(sno char(5), sname char(10)再差异备份CPXS数据库追加到test1。恢复时，先用test1备份设备上媒体集1恢复CPXS，再用test1备份设备上媒体集2 对CPXS数据库进行差异恢复。注意过程和观察结果。（3）在CPXS数据库中增加表s。create table s(sno char(5), sname char(10) on uu再将文件组uu追加备份到test1，并将日志追加备份到test1。恢复时，先用test1备份设备上媒体集1恢复CPXS，再用test1备份设备上媒体集2对CPXS数据库进行差异恢复，接着用tes
3、t1备份设备上媒体集1恢复文件组uu，然后用test1备份设备上媒体集4进行日志恢复。注意过程和观察结果。16.2 异常情况下的数据恢复1、部分数据文件损坏的修复处理请完成以下练习，理解每一个操作过程：-创建数据库CREATE DATABASE dbON PRIMARY( NAME=db_data, FILENAME= c:db_data.mdf),FILEGROUP db_fg1( NAME = db_fg1_data, FILENAME = c:db_fg1_data.ndf),FILEGROUP db_fg2( NAME = db_fg2_data, FILENAME = c:db_fg
4、2_data.ndf)LOG ON( NAME=db_log, FILENAME =c:db.ldf)GO-创建表CREATE TABLE db.dbo.ta(id int) ON PRIMARYCREATE TABLE db.dbo.tb(id int) ON db_fg1CREATE TABLE db.dbo.tc(id int) ON db_fg2INSERT db.dbo.tb SELECT id FROM sysobjectsGO-做文件组备份BACKUP DATABASE db FILEGROUP=db_fg1 TO DISK=c:db_fg1.bak WITH FORMAT-备份
5、后,再做数据处理INSERT db.dbo.ta SELECT id FROM sysobjectsGO/*-下面演示了破坏数据文件的处理，这些操作在操作系统中进行1. 停止SQL Server服务(msqlserver服务)2. 删除文件 c:db_fg1_data.ndf (模拟破坏)3. 重新SQL Server服务,此时数据库DB置疑-*/GO-下面演示了如何恢复数据-首先要备份当前日志BACKUP LOG db TO DISK=c:db_log.bak WITH FORMAT,NO_TRUNCATE-利用文件组备份恢复破坏的文件RESTORE DATABASE db FILEGROU
6、P=db_fg1 FROM DISK=c:db_fg1.bak WITH NORECOVERY-还原到日志点RESTORE LOG db FROM DISK=c:db_log.bak WITH RECOVERY-显示已经恢复的数据SELECT COUNT(*) FROM db.dbo.tbSELECT COUNT(*) FROM db.dbo.taGO-删除测试DROP DATABASE db2、仅有日志文件的数据修复处理-创建数据库CREATE DATABASE dbON PRIMARY( NAME=db_data, FILENAME= c:db_data.mdf)LOG ON( NAME=
7、db_log, FILENAME =c:db.ldf)GO-创建表CREATE TABLE db.dbo.ta(id int)INSERT db.dbo.ta SELECT id FROM sysobjectsGO-做文件组备份BACKUP DATABASE db TO DISK=c:db.bak WITH FORMAT-备份后,再做数据处理CREATE TABLE db.dbo.tb(id int)INSERT db.dbo.tb SELECT id FROM sysobjectsGO/*-下面演示了破坏数据文件的处理，这些操作在操作系统中进行1. 停止SQL Server服务(msqlse
8、rver服务)2. 删除文件 c:db_data.ndf (模拟破坏)3. 重新SQL Server服务,此时数据库DB置疑-*/GO-下面演示了如何恢复数据-首先要备份当前日志BACKUP LOG db TO DISK=c:db_log.bak WITH FORMAT,NO_TRUNCATE-利用文件组备份恢复破坏的文件RESTORE DATABASE db FROM DISK=c:db.bak WITH NORECOVERY-还原到日志点RESTORE LOG db FROM DISK=c:db_log.bak WITH RECOVERY-显示已经恢复的数据SELECT COUNT(*)
9、FROM db.dbo.taSELECT COUNT(*) FROM db.dbo.tbGO3、数据还原到指定时间点的处理-创建测试数据库CREATE DATABASE DbGO-对数据库进行备份BACKUP DATABASE Db TO DISK=c:db.bak WITH FORMATGO-创建测试表CREATE TABLE Db.dbo.TB_test(ID int)-延时1秒钟,再进行后面的操作(这是由于SQL Server的时间精度最大为百分之三秒,不延时的话,可能会导致还原到时间点的操作失败)WAITFOR DELAY 00:00:01GO-假设我们现在误操作删除了 Db.dbo.
10、TB_test 这个表DROP TABLE Db.dbo.TB_test-保存删除表的时间SELECT dt=GETDATE() INTO #GO-在删除操作后,发现不应该删除表 Db.dbo.TB_test-下面演示了如何恢复这个误删除的表 Db.dbo.TB_test-首先,备份事务日志(使用事务日志才能还原到指定的时间点)BACKUP LOG Db TO DISK=c:db_log.bak WITH FORMATGO-接下来,要先还原完全备份(还原日志必须在还原完全备份的基础上进行)RESTORE DATABASE Db FROM DISK=c:db.bak WITH REPLACE,N
11、ORECOVERYGO-将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早DECLARE dt datetimeSELECT dt=DATEADD(ms,-20,dt) FROM # -获取比表被删除的时间略早的时间RESTORE LOG Db FROM DISK=c:db_log.bak WITH RECOVERY,STOPAT=dtGO-查询一下,看表是否恢复SELECT * FROM Db.dbo.TB_test请给出测试结果：-最后删除所做的测试环境DROP DATABASE DbDROP TABLE #4、在现有数据库上还原时的数据文件处理-创建一个测试数据库
12、CREATE DATABASE dbON( NAME=db, FILENAME=c:db.mdf)LOG ON( NAME=db_log, FILENAME=c:db.ldf)-备份并删除测试数据库BACKUP DATABASE db TO DISK=c:a.bak WITH FORMATDROP DATABASE db-创建一个文件结构相同,但物理文件位置不同的数据库CREATE DATABASE dbON( NAME=db, FILENAME=d:db.mdf)LOG ON( NAME=db_log, FILENAME=d:db.ldf)-在新建的数据库上强制还原备份RESTORE DAT
13、ABASE db FROM DISK=c:a.bak WITH REPLACE-查看还原后的文件位置SELECT name,filename FROM db.dbo.sysfiles请给出测试结果：-删除测试5、备份数据库,并按日期生成备份目录/*-调用示例-备份当前数据库 exec p_backupdb -*/if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_backupdb) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_bac
14、kupdbGOcreate proc p_backupdbdbname sysname=, -要备份的数据库名称,不指定则备份当前数据库bkfname nvarchar(260)=, -备份文件名,文件名中可以用DBNAME代表数据库名,DATE代表日期,TIME代表时间bkpath nvarchar(260)=c: -备份文件的存放目录,在该目录下创建每天备份的目录as declare sql varchar(8000) if isnull(dbname,)= set dbname=db_name() if right(bkpath,1) set bkpath=bkpath+ set bkp
15、ath=bkpath+convert(varchar,getdate(),112)+ -创建目录 set sql=md +bkpath exec master.xp_cmdshell sql,no_output -备份处理 if isnull(bkfname,)= set bkfname=DBNAME_DATE.BAK set bkfname=replace(replace(replace(bkfname,DBNAME,dbname) ,DATE,convert(varchar,getdate(),112) ,TIME,replace(convert(varchar,getdate(),108
16、),:,) set sql=backup database +dbname + to disk=+bkpath+bkfname+ exec(sql)go16.3 数据导入与导出1、导入/导出 Excel 的基本方法（1）从Excel文件中导入数据到SQL数据库。如果接受数据导入的表已经存在insert into 表名 select * from OPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)如果导入数据并生成表select * into 表名 from OPENROWSET(MI
17、CROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)（2）从SQL数据库中,导出数据到Excel如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:insert into OPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)select * from 表如果Excel文件不存在,也可以用BCP来导出Excel的文件(注意大小写):EXEC master.xp_cmdshell
18、 bcp 数据库名.dbo.表名 out c:test.xls /c /S服务器名 /U用户名 -P密码导出查询的情况EXEC master.xp_cmdshell bcp SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lname queryout c:test.xls /c /S服务器名 /U用户名 -P密码说明:c:test.xls为导入/导出的Excel文件名.要求文件在SQL服务器或者服务器可以访问的共享目录上sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用.（3）将某个目录上的Excel表,导入到数
19、据库中将所有的Excel文件放到一个目录中,假设为c:test,然后用下面的方法来做create table #t(fname varchar(260),depth int,isf bit)insert into #t exec master.xp_dirtree c:test,1,1declare tb cursor for select fn=c:test+fname from #t where isf=1 and fname like %.xls -取.xls文件(EXCEL)declare fn varchar(8000)open tbfetch next from tb into f
20、nwhile fetch_status=0begin -下面是查询语句,需要根据你的情况改为插入语句 -插入已有的表用:insert into 表 selct * from . -创建表用:select * into 表 from . set fn=select * from OPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=+fn+,全部客户$) exec(fn) fetch next from tb into fnendclose tbdeallocate tbdrop table #t（4）使用DTS进行Excel数据的导入导出DTS是Sql Server中进行导入导出的图形化工具，请使用该工具根据向导完成以下实验：（1）用DTS导入向导将”梁山好汉功力排行.xls”文件导入到数据库中保存为“Rank09”表 (2) 用DTS导出向导将CP导出到C:Backup目录下（如果没有该目录，请先创建之）2、练习：其他形式的导入导出使用DTS导入导出向导完成以下练习：（1）将CPXS数据库中XSS表导出为Access文件。（2）将创建一测试数据库Test.MDB,并将该数据库表导入到Sql Server中。（3）将CPXS数据库中CP表导出为文本文件。
