实验16 数据备份恢复和导入导出1.docx
《实验16 数据备份恢复和导入导出1.docx》由会员分享，可在线阅读，更多相关《实验16 数据备份恢复和导入导出1.docx（17页珍藏版）》请在冰豆网上搜索。
实验16数据备份恢复和导入导出1
实验十六 数据备份、恢复和导入导出
姓名：
学号：
专业：
班级：
同组人：
无
实验日期：
2012-6-9
【实验目的与要求】
1．学会使用企业管理器、备份命令创建和删除命名备份设备。
2．掌握使用企业管理器、备份命令进行数据库的备份。
3．掌握使用企业管理器和恢复命令进行数据库的恢复。
4．掌握数据的导入导出。
【实验内容与步骤】
16.1正常情况下的数据备份与恢复
1、创建命名备份设备
在C:
\ProgramFiles\MicrosoftSQLServer\MSSQL\BACKUP\下创建一个名为“test1”的命名备份设备。
2、数据库的备份和恢复
（1）将整个CPXS数据库备份到test1，并利用该备份集恢复CPXS数据库。
（2）在CPXS数据库中增加表s1。
createtables1
（snochar（5）,
snamechar（10））
再差异备份CPXS数据库追加到test1。
恢复时，先用test1备份设备上媒体集1恢复CPXS，再用test1备份设备上媒体集2对CPXS数据库进行差异恢复。
注意过程和观察结果。
建表成功：
第一次还原：
第二次还原：
（3）在CPXS数据库中增加表s。
createtables
（snochar（5）,
snamechar（10））
onuu
再将文件组uu追加备份到test1，并将日志追加备份到test1。
恢复时，先用test1备份设备上媒体集1恢复CPXS，再用test1备份设备上媒体集2对CPXS数据库进行差异恢复，接着用test1备份设备上媒体集1恢复文件组uu，然后用test1备份设备上媒体集4进行日志恢复。
注意过程和观察结果。
备份日志：
16.2异常情况下的数据恢复
1、部分数据文件损坏的修复处理
请完成以下练习，理解每一个操作过程：
--创建数据库
CREATEDATABASEdb
ONPRIMARY（
NAME='db_data',
FILENAME='c:
\db_data.mdf'）,
FILEGROUPdb_fg1（
NAME='db_fg1_data',
FILENAME='c:
\db_fg1_data.ndf'）,
FILEGROUPdb_fg2（
NAME='db_fg2_data',
FILENAME='c:
\db_fg2_data.ndf'）
LOGON（
NAME='db_log',
FILENAME='c:
\db.ldf'）
GO
--创建表
CREATETABLEdb.dbo.ta（idint）ON[PRIMARY]
CREATETABLEdb.dbo.tb（idint）ONdb_fg1
CREATETABLEdb.dbo.tc（idint）ONdb_fg2
INSERTdb.dbo.tbSELECTidFROMsysobjects
GO
--做文件组备份
BACKUPDATABASEdbFILEGROUP='db_fg1'TODISK='c:
\db_fg1.bak'WITHFORMAT
--备份后,再做数据处理
INSERTdb.dbo.taSELECTidFROMsysobjects
GO
/*--下面演示了破坏数据文件的处理，这些操作在操作系统中进行
1.停止SQLServer服务（msqlserver服务）
2.删除文件c:
\db_fg1_data.ndf（模拟破坏）
3.重新SQLServer服务,此时数据库DB置疑
--*/
GO
--下面演示了如何恢复数据
--首先要备份当前日志
BACKUPLOGdbTODISK='c:
\db_log.bak'WITHFORMAT,NO_TRUNCATE
--利用文件组备份恢复破坏的文件
RESTOREDATABASEdbFILEGROUP='db_fg1'FROMDISK='c:
\db_fg1.bak'WITHNORECOVERY
--还原到日志点
RESTORELOGdbFROMDISK='c:
\db_log.bak'WITHRECOVERY
--显示已经恢复的数据
SELECTCOUNT（*）FROMdb.dbo.tb
SELECTCOUNT（*）FROMdb.dbo.ta
GO
--删除测试
DROPDATABASEdb
2、仅有日志文件的数据修复处理
--创建数据库
CREATEDATABASEdb
ONPRIMARY（
NAME='db_data',
FILENAME='c:
\db_data.mdf'）
LOGON（
NAME='db_log',
FILENAME='c:
\db.ldf'）
GO
--创建表
CREATETABLEdb.dbo.ta（idint）
INSERTdb.dbo.taSELECTidFROMsysobjects
GO
--做文件组备份
BACKUPDATABASEdbTODISK='c:
\db.bak'WITHFORMAT
--备份后,再做数据处理
CREATETABLEdb.dbo.tb（idint）
INSERTdb.dbo.tbSELECTidFROMsysobjects
GO
/*--下面演示了破坏数据文件的处理，这些操作在操作系统中进行
1.停止SQLServer服务（msqlserver服务）
2.删除文件c:
\db_data.ndf（模拟破坏）
3.重新SQLServer服务,此时数据库DB置疑
--*/
GO
--下面演示了如何恢复数据
--首先要备份当前日志
BACKUPLOGdbTODISK='c:
\db_log.bak'WITHFORMAT,NO_TRUNCATE
--利用文件组备份恢复破坏的文件
RESTOREDATABASEdbFROMDISK='c:
\db.bak'WITHNORECOVERY
--还原到日志点
RESTORELOGdbFROMDISK='c:
\db_log.bak'WITHRECOVERY
--显示已经恢复的数据
SELECTCOUNT（*）FROMdb.dbo.ta
SELECTCOUNT（*）FROMdb.dbo.tb
GO
3、数据还原到指定时间点的处理
--创建测试数据库
CREATEDATABASEDb
GO
--对数据库进行备份
BACKUPDATABASEDbTODISK='c:
\db.bak'WITHFORMAT
GO
--创建测试表
CREATETABLEDb.dbo.TB_test（IDint）
--延时1秒钟,再进行后面的操作（这是由于SQLServer的时间精度最大为百分之三秒,不延时的话,可能会导致还原到时间点的操作失败）
WAITFORDELAY'00:
00:
01'
GO
--假设我们现在误操作删除了Db.dbo.TB_test这个表
DROPTABLEDb.dbo.TB_test
--保存删除表的时间
SELECTdt=GETDATE（）INTO#
GO
--在删除操作后,发现不应该删除表Db.dbo.TB_test
--下面演示了如何恢复这个误删除的表Db.dbo.TB_test
--首先,备份事务日志（使用事务日志才能还原到指定的时间点）
BACKUPLOGDbTODISK='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Backup\test1.bakc:
\db_log.bak'WITHFORMAT
GO
--接下来,要先还原完全备份（还原日志必须在还原完全备份的基础上进行）
RESTOREDATABASEDbFROMDISK='c:
\db.bak'WITHREPLACE,NORECOVERY
GO
--将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早
DECLARE@dtdatetime
SELECT@dt=DATEADD（ms,-20,dt）FROM#--获取比表被删除的时间略早的时间
RESTORELOGDbFROMDISK='c:
\db_log.bak'WITHRECOVERY,STOPAT=@dt
GO
--查询一下,看表是否恢复
SELECT*FROMDb.dbo.TB_test
请给出测试结果：
--最后删除所做的测试环境
DROPDATABASEDb
DROPTABLE#
4、在现有数据库上还原时的数据文件处理
--创建一个测试数据库
CREATEDATABASEdb
ON（
NAME=db,
FILENAME='c:
\db.mdf'）
LOGON（
NAME=db_log,
FILENAME='c:
\db.ldf'）
--备份并删除测试数据库
BACKUPDATABASEdbTODISK='c:
\a.bak'WITHFORMAT
DROPDATABASEdb
--创建一个文件结构相同,但物理文件位置不同的数据库
CREATEDATABASEdb
ON（
NAME=db,
FILENAME='d:
\db.mdf'）
LOGON（
NAME=db_log,
FILENAME='d:
\db.ldf'）
--在新建的数据库上强制还原备份
RESTOREDATABASEdbFROMDISK='c:
\a.bak'WITHREPLACE
--查看还原后的文件位置
SELECTname,filenameFROMdb.dbo.sysfiles
请给出测试结果：
--删除测试
5、备份数据库,并按日期生成备份目录
/*--调用示例
--备份当前数据库
execp_backupdb
--*/
ifexists（select*fromdbo.sysobjectswhereid=object_id（N'[dbo].[p_backupdb]'）andOBJECTPROPERTY（id,N'IsProcedure'）=1）
dropprocedure[dbo].[p_backupdb]
GO
createprocp_backupdb
@dbnamesysname='',--要备份的数据库名称,不指定则备份当前数据库
@bkfnamenvarchar（260）='',--备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
@bkpathnvarchar（260）='c:
\'--备份文件的存放目录,在该目录下创建每天备份的目录
as
declare@sqlvarchar（8000）
ifisnull（@dbname,''）=''set@dbname=db_name（）
ifright（@bkpath,1）<>'\'set@bkpath=@bkpath+'\'
set@bkpath=@bkpath+convert（varchar,getdate（）,112）+'\'
--创建目录
set@sql='md'+@bkpath
execmaster..xp_cmdshell@sql,no_output
--备份处理
ifisnull（@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）,':
',''））
set@sql='backupdatabase'+@dbname
+'todisk='''+@bkpath+@bkfname+''''
exec（@sql）
go
16.3数据导入与导出
1、导入/导出Excel的基本方法
（1）从Excel文件中导入数据到SQL数据库。
如果接受数据导入的表已经存在
insertinto表名select*from
OPENROWSET（'MICROSOFT.JET.OLEDB.4.0'
'Excel5.0;HDR=YES;DATABASE=c:
\test.xls',sheet1$）
如果导入数据并生成表
select*into表名from
OPENROWSET（'MICROSOFT.JET.OLEDB.4.0'
'Excel5.0;HDR=YES;DATABASE=c:
\test.xls',sheet1$）
（2）从SQL数据库中,导出数据到Excel
如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
insertintoOPENROWSET（'MICROSOFT.JET.OLEDB.4.0'
'Excel5.0;HDR=YES;DATABASE=c:
\test.xls',sheet1$）
select*from表
如果Excel文件不存在,也可以用BCP来导出Excel的文件（注意大小写）:
EXECmaster..xp_cmdshell'bcp数据库名.dbo.表名out"c:
\test.xls"/c/S"服务器名"/U"用户名"-P"密码"'
导出查询的情况
EXECmaster..xp_cmdshell'bcp"SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_lname"queryout"c:
\test.xls"/c/S"服务器名"/U"用户名"-P"密码"'
说明:
c:
\test.xls为导入/导出的Excel文件名.要求文件在SQL服务器或者服务器可以访问的共享目录上
sheet1$为Excel文件的工作表名,一般要加上$才能正常使用.
（3）将某个目录上的Excel表,导入到数据库中
将所有的Excel文件放到一个目录中,假设为c:
\test\,然后用下面的方法来做
createtable#t（fnamevarchar（260）,depthint,isfbit）
insertinto#texecmaster..xp_dirtree'c:
\test',1,1
declaretbcursorforselectfn='c:
\test'+fnamefrom#t
whereisf=1andfnamelike'%.xls'--取.xls文件（EXCEL）
declare@fnvarchar（8000）
opentb
fetchnextfromtbinto@fn
while@@fetch_status=0
begin
--下面是查询语句,需要根据你的情况改为插入语句
--插入已有的表用:
insertinto表selct*from...
--创建表用:
select*into表from...
set@fn='select*from
OPENROWSET（''MICROSOFT.JET.OLEDB.4.0'',''Excel5.0;HDR=YES;DATABASE='+@fn+''',全部客户$）'
exec（@fn）
fetchnextfromtbinto@fn
end
closetb
deallocatetb
droptable#t
（4）使用DTS进行Excel数据的导入导出
DTS是SqlServer中进行导入导出的图形化工具，请使用该工具根据向导完成以下实验：
（1）用DTS导入向导将”梁山好汉功力排行.xls”文件导入到数据库中保存为“Rank09”表
（2）用DTS导出向导将CP导出到C:
\Backup目录下（如果没有该目录，请先创建之）
2、练习：
其他形式的导入导出
使用DTS导入导出向导完成经下练习
（1）将CPXS数据库中XSS表导出为Access文件。
语句：
insertintoOPENROWSET（'MICROSOFT.JET.OLEDB.4.0'
'Excel5.0;HDR=YES;DATABASE=c:
\test.xls',sheet1$）
select*fromxss
（2）将创建一测试数据库Test.MDB,并将该数据库表导入到SqlServer中。
（3）将CPXS数据库中CP表导出为文本文件。
升级会员 