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

第1页共21页实验十六数据备份、恢复和导入导出【实验目的与要求】1．学会使用企业管理器、备份命令创建和删除命名备份设备。2．掌握使用企业管理器、备份命令进行数据库的备份。3．掌握使用企业管理器和恢复命令进行数据库的恢复。4．掌握数据的导入导出。【实验内容与步骤】16.1正常情况下的数据备份与恢复1、创建命名备份设备在C:\ProgramFiles\MicrosoftSQLServer\MSSQL\BACKUP\下创建一个名为“test1”的命名备份设备。2、数据库的备份和恢复（1）将整个CPXS数据库备份到test1，并利用该备份集恢复CPXS数据库。第2页共21页（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异常情况下的数据恢复第3页共21页1、部分数据文件损坏的修复处理请完成以下练习，理解每一个操作过程：--创建数据库CREATEDATABASEdbONPRIMARY(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_fg1CREATETABLEdb.dbo.tc(idint)ONdb_fg2INSERTdb.dbo.tbSELECTidFROMsysobjectsGO第4页共21页--做文件组备份BACKUPDATABASEdbFILEGROUP='db_fg1'TODISK='c:\db_fg1.bak'WITHFORMAT--备份后,再做数据处理INSERTdb.dbo.taSELECTidFROMsysobjectsGO第5页共21页/*--下面演示了破坏数据文件的处理，这些操作在操作系统中进行1.停止SQLServer服务(msqlserver服务)2.删除文件c:\db_fg1_data.ndf(模拟破坏)3.重新SQLServer服务,此时数据库DB置疑--*/GO--下面演示了如何恢复数据--首先要备份当前日志BACKUPLOGdbTODISK='c:\db_log.bak'WITHFORMAT,NO_TRUNCATE--利用文件组备份恢复破坏的文件第6页共21页RESTOREDATABASEdbFILEGROUP='db_fg1'FROMDISK='c:\db_fg1.bak'WITHNORECOVERY--还原到日志点RESTORELOGdbFROMDISK='c:\db_log.bak'WITHRECOVERY--显示已经恢复的数据SELECTCOUNT(*)FROMdb.dbo.tbSELECTCOUNT(*)FROMdb.dbo.taGO第7页共21页--删除测试DROPDATABASEdb2、仅有日志文件的数据修复处理--创建数据库CREATEDATABASEdbONPRIMARY(第8页共21页NAME='db_data',FILENAME='c:\db_data.mdf')LOGON(NAME='db_log',FILENAME='c:\db.ldf')GO--创建表CREATETABLEdb.dbo.ta(idint)INSERTdb.dbo.taSELECTidFROMsysobjectsGO--做文件组备份BACKUPDATABASEdbTODISK='c:\db.bak'WITHFORMAT第9页共21页--备份后,再做数据处理CREATETABLEdb.dbo.tb(idint)INSERTdb.dbo.tbSELECTidFROMsysobjectsGO/*--下面演示了破坏数据文件的处理，这些操作在操作系统中进行1.停止SQLServer服务(msqlserver服务)2.删除文件c:\db_data.ndf(模拟破坏)3.重新SQLServer服务,此时数据库DB置疑--*/GO--下面演示了如何恢复数据第10页共21页--首先要备份当前日志BACKUPLOGdbTODISK='c:\db_log.bak'WITHFORMAT,NO_TRUNCATE--利用文件组备份恢复破坏的文件RESTOREDATABASEdbFROMDISK='c:\db.bak'WITHNORECOVERY--还原到日志点RESTORELOGdbFROMDISK='c:\db_log.bak'WITHRECOVERY第11页共21页--显示已经恢复的数据SELECTCOUNT(*)FROMdb.dbo.taSELECTCOUNT(*)FROMdb.dbo.tbGO3、数据还原到指定时间点的处理--创建测试数据库CREATEDATABASEDb第12页共21页GO--对数据库进行备份BACKUPDATABASEDbTODISK='c:\db.bak'WITHFORMATGO--创建测试表CREATETABLEDb.dbo.TB_test(IDint)第13页共21页--延时1秒钟,再进行后面的操作(这是由于SQLServer的时间精度最大为百分之三秒,不延时的话,可能会导致还原到时间点的操作失败)WAITFORDELAY'00:00:01'GO--假设我们现在误操作删除了Db.dbo.TB_test这个表DROPTABLEDb.dbo.TB_test第14页共21页--保存删除表的时间SELECTdt=GETDATE()INTO#GO--在删除操作后,发现不应该删除表Db.dbo.TB_test--下面演示了如何恢复这个误删除的表Db.dbo.TB_test--首先,备份事务日志(使用事务日志才能还原到指定的时间点)BACKUPLOGDbTODISK='c:\db_log.bak'WITHFORMATGO第15页共21页--接下来,要先还原完全备份(还原日志必须在还原完全备份的基础上进行)RESTOREDATABASEDbFROMDISK='c:\db.bak'WITHREPLACE,NORECOVERYGO--将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早DECLARE@dtdatetimeSELECT@dt=DATEADD(ms,-20,dt)FROM#--获取比表被删除的时间略早的时间RESTORELOGDbFROMDISK='c:\db_log.bak'WITHRECOVERY,STOPAT=@dt第16页共21页GO--查询一下,看表是否恢复SELECT*FROMDb.dbo.TB_test请给出测试结果：--最后删除所做的测试环境DROPDATABASEDb第17页共21页DROPTABLE#4、在现有数据库上还原时的数据文件处理--创建一个测试数据库CREATEDATABASEdbON(NAME=db,FILENAME='c:\db.mdf')LOGON(NAME=db_log,FILENAME='c:\db.ldf')--备份并删除测试数据库BACKUPDATABASEdbTODISK='c:\a.bak'WITHFORMATDROPDATABASEdb第18页共21页--创建一个文件结构相同,但物理文件位置不同的数据库CREATEDATABASEdbON(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)第19页共21页dropprocedure[dbo].[p_backupdb]GOcreateprocp_backupdb@dbnamesysname='',--要备份的数据库名称,不指定则备份当前数据库@bkfnamenvarchar(260)='',--备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间@bkpathnvarchar(260)='c:\'--备份文件的存放目录,在该目录下创建每天备份的目录asdeclare@sqlvarchar(8000)ifisnull(@dbname,'')=''set@dbname=db_name()ifright(@bkpath,1)'\'set@bkpath=@bkpath+'\'set@bkpath=@bkpath+convert(varchar,getdate(),112)+'\'--创建目录set@sql='md'+@bkpathexecmaster..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)go16.3数据导入与导出1、导入/导出Excel的基本方法（1）从Excel文件中导入数据到SQL数据库。①如果接受数据导入的表已经存在insertinto表名select*fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)第20页共21页②如果导入数据并生成表select*into表名fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)（2）从SQL数据库中,导出数据到Excel①如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的