实验16数据备份恢复和导入导出

1、实验十六数据备份、恢复和导入导出【实验目的与要求】1 学会使用企业管理器、备份命令创建和删除命名备份设备。2 掌握使用企业管理器、备份命令进行数据库的备份。3 掌握使用企业管理器和恢复命令进行数据库的恢复。4 掌握数据的导入导出。【实验内容与步骤】16.1 正常情况下的数据备份与恢复1、创建命名备份设备在C:Program FilesMicrosoft SQL ServerMSSQLBACKUP下创建一个名为“test1”的命名备份设备。2、数据库的备份和恢复（1）将整个CPXS数据库备份到test1，并利用该备份集恢复CPXS数据库。（2）在CPXS数据库中增加表s1。create tabl

2、e 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数据库进行差异恢复，接着用test1备份设备上媒体集1恢复文件组

3、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_fg2_data.ndf)LOG ON(NAME

4、=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-备份后,再做数据处理INSERT db.dbo.ta

5、 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 FILEGROUP=db_fg1 FROM DISK=c:db_

6、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=db_log,FILENAME =c:db.ldf)G

7、O-创建表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服务(msqlserver服务)2. 删除文件 c:db_data.ndf

8、 (模拟破坏)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(*) FROM db.dbo.taSELECT COUNT(*

9、) 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.TB_test 这个表DROP TABLE Db.dbo

10、.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,NORECOVERYGO-将事务日志还原到删除操作前（这里

11、的时间对应上面的删除时间，并比删除时间略早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、在现有数据库上还原时的数据文件处理-创建一个测试数据库CREATE DATABASE dbON(NAME=db

12、,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 DATABASE db FROM DISK=c:a.bak WITH REPL

13、ACE-查看还原后的文件位置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_backupdbGOcreate proc p_backupdbdbname s

14、ysname=,-要备份的数据库名称,不指定则备份当前数据库bkfname nvarchar(260)=,-备份文件名,文件名中可以用DBNAME代表数据库名,DATE代表日期,TIME代表时间bkpath nvarchar(260)=c:-备份文件的存放目录,在该目录下创建每天备份的目录asdeclare sql varchar(8000)if isnull(dbname,)= set dbname=db_name()if right(bkpath,1) set bkpath=bkpath+set bkpath=bkpath+convert(varchar,getdate(),112)+-创

15、建目录set sql=md +bkpathexec master.xp_cmdshell sql,no_output-备份处理if isnull(bkfname,)= set bkfname=DBNAME_DATE.BAKset bkfname=replace(replace(replace(bkfname,DBNAME,dbname),DATE,convert(varchar,getdate(),112),TIME,replace(convert(varchar,getdate(),108),:,)set sql=backup database +dbname+ to disk=+bkpat

16、h+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(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=c:test

17、.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 bcp 数据库名.dbo.表名 out c:test.xls /c /S服务器名 /U用户名 -P密码导出查

18、询的情况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表,导入到数据库中将所有的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 #twhere isf=1 and fname like %.xls -取.xls文件(EXCEL)de