2023年7月30日发(作者：)

实验16数据备份、恢复和导⼊导出实验⼗六数据备份、恢复和导⼊导出【实验⽬的与要求】1．学会使⽤企业管理器、备份命令创建和删除命名备份设备。2．掌握使⽤企业管理器、备份命令进⾏数据库的备份。3．掌握使⽤企业管理器和恢复命令进⾏数据库的恢复。4．掌握数据的导⼊导出。【实验内容与步骤】16.1 正常情况下的数据备份与恢复1、创建命名备份设备在C:Program FilesMicrosoft SQL ServerMSSQLBACKUP下创建⼀个名为“test1”的命名备份设备。2、数据库的备份和恢复（1）将整个CPXS数据库备份到test1，并利⽤该备份集恢复CPXS数据库。（2）在CPXS数据库中增加表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数据库进⾏差异恢复，接着⽤test1备份设备上媒体集1恢复⽂件组uu，然后⽤test1备份设备上媒体集4进⾏⽇志恢复。注意过程和观察结果。16.2 异常情况下的数据恢复1、部分数据⽂件损坏的修复处理请完成以下练习，理解每⼀个操作过程：--创建数据库CREATE DATABASE dbON PRIMARY(NAME='db_data',FILENAME= 'c:db_'),FILEGROUP db_fg1(NAME = 'db_fg1_data',FILENAME = 'c:db_fg1_'),FILEGROUP db_fg2(NAME = 'db_fg2_data',FILENAME = 'c:db_fg2_')LOG ON(NAME='db_log',FILENAME ='c:')GO--创建表CREATE TABLE (id int) ON [PRIMARY] CREATE TABLE (id int) ON db_fg1CREATE TABLE (id int) ON db_fg2INSERT SELECT id FROM sysobjectsGO--做⽂件组备份BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:db_' WITH FORMAT--备份后,再做数据处理INSERT SELECT id FROM sysobjectsGO/*--下⾯演⽰了破坏数据⽂件的处理，这些操作在操作系统中进⾏1. 停⽌SQL Server服务(msqlserver服务)2. 删除⽂件c:db_fg1_ (模拟破坏)3. 重新SQL Server服务,此时数据库DB置疑--*/GO--下⾯演⽰了如何恢复数据--⾸先要备份当前⽇志BACKUP LOG db TO DISK='c:db_' WITHFORMAT,NO_TRUNCATE--利⽤⽂件组备份恢复破坏的⽂件RESTORE DATABASE db FILEGROUP='db_fg1' FROMDISK='c:db_' WITH NORECOVERY--还原到⽇志点RESTORE LOG db FROM DISK='c:db_' WITH RECOVERY--显⽰已经恢复的数据SELECT COUNT(*) FROM CT COUNT(*) FROM --删除测试DROP DATABASE db2、仅有⽇志⽂件的数据修复处理--创建数据库CREATE DATABASE dbON PRIMARY(NAME='db_data',FILENAME= 'c:db_')LOG ON(NAME='db_log',FILENAME ='c:')GO--创建表CREATE TABLE (id int)INSERT SELECT id FROM sysobjectsGO--做⽂件组备份BACKUP DATABASE db TO DISK='c:' WITH FORMAT--备份后,再做数据处理CREATE TABLE (id int)INSERT SELECT id FROM sysobjectsGO/*--下⾯演⽰了破坏数据⽂件的处理，这些操作在操作系统中进⾏1. 停⽌SQL Server服务(msqlserver服务)2. 删除⽂件c:db_ (模拟破坏)3. 重新SQL Server服务,此时数据库DB置疑--*/GO--下⾯演⽰了如何恢复数据--⾸先要备份当前⽇志BACKUP LOG db TO DISK='c:db_' WITHFORMAT,NO_TRUNCATE--利⽤⽂件组备份恢复破坏的⽂件RESTORE DATABASE db FROM DISK='c:' WITH NORECOVERY--还原到⽇志点RESTORE LOG db FROM DISK='c:db_' WITH RECOVERY--显⽰已经恢复的数据SELECT COUNT(*) FROM SELECT COUNT(*) FROM GO3、数据还原到指定时间点的处理--创建测试数据库CREATE DATABASE DbGO--对数据库进⾏备份BACKUP DATABASE Db TO DISK='c:' WITH FORMAT GO--创建测试表CREATE TABLE _test(ID int)--延时1秒钟,再进⾏后⾯的操作(这是由于SQL Server的时间精度最⼤为百分之三秒,不延时的话,可能会导致还原到时间点的操作失败)WAITFOR DELAY '00:00:01'GO--假设我们现在误操作删除了_test 这个表DROP TABLE _test--保存删除表的时间SELECT dt=GETDATE() INTO #GO--在删除操作后,发现不应该删除表_test--下⾯演⽰了如何恢复这个误删除的表_test--⾸先,备份事务⽇志(使⽤事务⽇志才能还原到指定的时间点) BACKUP LOG Db TO DISK='c:db_' WITH FORMATGO--接下来,要先还原完全备份(还原⽇志必须在还原完全备份的基础上进⾏) RESTORE DATABASE Db FROM DISK='c:'WITHREPLACE,NORECOVERYGO--将事务⽇志还原到删除操作前（这⾥的时间对应上⾯的删除时间，并⽐删除时间略早DECLARE @dt datetimeSELECT @dt=DATEADD(ms,-20,dt) FROM # --获取⽐表被删除的时间略早的时间RESTORE LOG Db FROM DISK='c:db_' WITHRECOVERY,STOPAT=@dtGO--查询⼀下,看表是否恢复SELECT * FROM _test 请给出测试结果：--最后删除所做的测试环境DROP DATABASE DbDROP TABLE #4、在现有数据库上还原时的数据⽂件处理--创建⼀个测试数据库CREATE DATABASE dbON(NAME=db,FILENAME='c:')LOG ON(NAME=db_log,FILENAME='c:')--备份并删除测试数据库BACKUP DATABASE db TO DISK='c:' WITH FORMAT DROP DATABASE db--创建⼀个⽂件结构相同,但物理⽂件位置不同的数据库CREATE DATABASE dbON(NAME=db,FILENAME='d:')LOG ON(NAME=db_log,FILENAME='d:')--在新建的数据库上强制还原备份RESTORE DATABASE db FROM DISK='c:' WITH REPLACE--查看还原后的⽂件位置SELECT name,filename FROM es请给出测试结果：--删除测试5、备份数据库,并按⽇期⽣成备份⽬录/*--调⽤⽰例--备份当前数据库exec p_backupdb--*/if exists (select * from ects where id =object_id(N'[dbo].[p_backupdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_backupdb]GOcreate proc p_backupdb@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库@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)+''--创建⽬录set @sql='md '+@p_cmdshell @sql,no_output--备份处理if isnull(@bkfname,'')='' set @bkfname='DBNAME_'set @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='''+@bkpath+@bkfname+''''exec(@sql)go16.3 数据导⼊与导出1、导⼊/导出Excel 的基本⽅法（1）从Excel⽂件中导⼊数据到SQL数据库。①如果接受数据导⼊的表已经存在insert into 表名select * fromOPENROWSET('.4.0','Excel 5.0;HDR=YES;DA TABASE=c:',sheet1$)