实验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_

4、data',FILENAME = 'c:db_fg2_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

5、 db FILEGROUP='db_fg1' TO DISK='c:db_fg1.bak' WITH FORMAT-备份后,再做数据处理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=

6、9;c:db_log.bak' WITH FORMAT,NO_TRUNCATE-利用文件组备份恢复破坏的文件RESTORE DATABASE db FILEGROUP='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-删除测试DR

7、OP DATABASE db2、仅有日志文件的数据修复处理-创建数据库CREATE DATABASE dbON PRIMARY(NAME='db_data',FILENAME= 'c:db_data.mdf')LOG ON(NAME='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:

8、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 (模拟破坏)3. 重新SQL Server服务,此时数据库DB置疑-*/GO-下面演示了如何恢复数据-首先要备份当前日志BACKUP LOG db TO DISK='c:db_log.bak' WITH

9、 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(*) FROM db.dbo.tbGO3、数据还原到指定时间点的处理-创建测试数据库CREATE DATABASE DbGO-对数据库进行备份BACKUP DAT

10、ABASE Db TO DISK='c:db.bak' WITH FORMATGO-创建测试表CREATE TABLE Db.dbo.TB_test(ID int)-延时1秒钟,再进行后面的操作(这是由于SQL Server的时间精度最大为百分之三秒,不延时的话,可能会导致还原到时间点的操作失败)WAITFOR DELAY '00:00:01'GO-假设我们现在误操作删除了 Db.dbo.TB_test 这个表DROP TABLE Db.dbo.TB_test-保存删除表的时间SELECT dt=GETDATE() INTO #GO-在删除操作后,发现不应该删除

11、表 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-将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早DECLARE dt datetime

12、SELECT 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,FILENAME='c:db.mdf')L

13、OG 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 DI

14、SK='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(N'dbo.p_backupdb') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop proced

15、ure dbo.p_backupdbGOcreate proc p_backupdbdbname sysname='',-要备份的数据库名称,不指定则备份当前数据库bkfname nvarchar(260)='',-备份文件名,文件名中可以用DBNAME代表数据库名,DATE代表日期,TIME代表时间bkpath nvarchar(260)='c:'-备份文件的存放目录,在该目录下创建每天备份的目录asdeclare sql varchar(8000)if isnull(dbname,'')='' set dbna

16、me=db_name()if right(bkpath,1)<>'' set bkpath=bkpath+''set bkpath=bkpath+convert(varchar,getdate(),112)+''-创建目录set sql='md '+bkpathexec master.xp_cmdshell sql,no_output-备份处理if isnull(bkfname,'')='' set bkfname='DBNAME_DATE.BAK'set bkfname

17、=replace(replace(replace(bkfname,'DBNAME',dbname),'DATE',convert(varchar,getdate(),112),'TIME',replace(convert(varchar,getdate(),108),':','')set sql='backup database '+dbname+' to disk='''+bkpath+bkfname+''''exec(sql)go1

18、6.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;DA

19、TABASE=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 'bcp 数据库名.db

20、o.表名 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"密码"'

21、说明: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' -取.