實驗十六 數(shù)據(jù)備份、恢復和導入導出姓名：學號：專業(yè)：班級：同組人：無實驗日期：2012-6-9【實驗目的與要求】1. 學會使用企業(yè)管理器、備份命令創(chuàng)建和刪除命名備份設備2. 掌握使用企業(yè)管理器、備份命令進行數(shù)據(jù)庫的備份3. 掌握使用企業(yè)管理器和恢復命令進行數(shù)據(jù)庫勺恢復4. 掌握數(shù)據(jù)勺導入導出?！緦嶒瀮?nèi)容與步驟】161正常情況下的數(shù)據(jù)備份與恢復1、創(chuàng)建命名備份設備在 C:Program FilesMicrosoft SQL ServerMSSQLBACKUP下創(chuàng)建一個名為“testl"的命名 備份設備。+,E: I下載軟件C#( ABO )(1). I+,Newcpxs1+|JSPJ+,student+,XSBOOK+jXSStudent+ 口妄全性-口服務器對象-口備論謨邕23：teEtl :+ 口捱崔眼寡常+ 口服務器融發(fā)器 = lU.I2、數(shù)據(jù)庫的備份和恢復(1)將整個CPXS數(shù)據(jù)庫備份到test1，并利用該備份集恢復CPXS數(shù)據(jù)庫。0覆蓋現(xiàn)有數(shù)據(jù)庫保留復制設置還原毎于備份之前進行提示限制訪問還原的數(shù)據(jù)庫(£)將數(shù)據(jù)庫文件還原為:原始文件名還原為CFKS_DI C: Fr u gi_ ：=iitiC: Fr ogi_：=iiTiFileE'iMicrDEof'Files'i.Mi cr osof CFXS_LrosoftSQLServer Ianage>ent Studio Express對數(shù)據(jù)庫艾p茫憐還原已成功完成口(2)在CPXS數(shù)據(jù)庫中增加表si。create table si(sno char(5),sname char(10)再差異備份CPXS數(shù)據(jù)庫追加到test1?；謴蜁r，先用test1備份設備上媒體集1恢復CPXS, 再用test1備份設備上媒體集2對CPXS數(shù)據(jù)庫進行差異恢復。注意過程和觀察結(jié)果。 建表成功：+_l據(jù)庫-CPXS+口數(shù)據(jù)庫關系閤-口表+口系統(tǒng)表+dLo. CF+dLo. CFXSB+idbu. zl i+dLo.Xl+l=1第一次還原:-口+ LJ系繞數(shù)據(jù)庫_ j CFXSI 口數(shù)據(jù)庫關系團_ _|表+ _|系統(tǒng)表+ _| db o. CP+ _3 dbo. CPXSB+ _| dbo. X+ dbo. XSS+ _| db o. Y+ _| myTeEtNamel. t_Test第二次還原:-LJ數(shù)據(jù)庫+ 口系統(tǒng)數(shù)據(jù)庫-)CFXS+ 口數(shù)據(jù)庫關系圖T 表+ |_J系統(tǒng)表+ _| dbo. CF+ dbo. CFXSB+ _J+ _Jdbo. si+ dbo. XSS+ _| dbo. Y+ _| myTestllamel. t_Test(3)在CPXS數(shù)據(jù)庫中增加表s。create table s(sno char(5), sname char(10)稱列鍵約融索統(tǒng)on uu再將文件組uu追加備份到test1，并將日志追加備份到test1?；謴蜁r，先用test1備份設 備上媒體集1恢復CPXS，再用test1備份設備上媒體集2對CPXS數(shù)據(jù)庫進行差異恢復， 接著用test1備份設備上媒體集1恢復文件組uu，然后用test1備份設備上媒體集4進行日志 恢復。注意過程和觀察結(jié)果。CS-FC. cpzs - - - LQuery3. sql* 摘要create table 3(sno char ；5)rsname char(10) n j. j._J消息命令已成功完戚°備份組件：數(shù)據(jù)庫O文件和文件組(G):I|備份集名稱®:護弓-爭務日志 備份Microsoft SQL Server IanageMent StudioXQ對數(shù)據(jù)庫"中浴'的備份已成功完成°確定備份到：©磁盤CllFrograrn Fi已匚 口弓口£t EQL Eef 耳丁 '副 EEQL.共 E1QLA 乃注匚. bak16.2異常情況下的數(shù)據(jù)恢復1、部分數(shù)據(jù)文件損壞的修復處理請完成以下練習，理解每一個操作過程：-創(chuàng)建數(shù)據(jù)庫CREATE DATABASE dbON PRIMARY(NAME='db_data',FILENAME= 'c:db_data.mdf),FILEGROUP db_fgl(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='db_log',FILENAME ='c:db.ldf)GO-創(chuàng)建表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-備份后，再做數(shù)據(jù)處理INSERT db.dbo.ta SELECT id FROM sysobjectsGO/*-下面演示了破壞數(shù)據(jù)文件的處理，這些操作在操作系統(tǒng)中進行1. 停止 SQL Server 服務(msqlserver 服務)2. 刪除文件c:db_fg1_data.ndf (模擬破壞)3. 重新SQL Server服務，此時數(shù)據(jù)庫DB置疑-*/GO-下面演示了如何恢復數(shù)據(jù)-首先要備份當前日志BACKUP LOG db TO DISK='c:db_log.bak' WITHFORMAT,NO_TRUNCATE-利用文件組備份恢復破壞的文件RESTORE DATABASE db FILEGROUP='db_fgl' FROM DISK='c:db_fgl.bak' WITH NORECOVERY-還原到日志點RESTORE LOG db FROM DISK='c:db_log.bak' WITH RECOVERY -顯示已經(jīng)恢復的數(shù)據(jù)GO-刪除測試DROP DATABASE db2、僅有日志文件的數(shù)據(jù)修復處理-創(chuàng)建數(shù)據(jù)庫CREATE DATABASE dbON PRIMARY(NAME='db_data',FILENAME= 'c:db_data.mdf)LOG ON(NAME='db_log',FILENAME ='c:db.ldf)GO-創(chuàng)建表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-備份后，再做數(shù)據(jù)處理CREATE TABLE db.dbo.tb(id int)INSERT db.dbo.tb SELECT id FROM sysobjectsGO/*-下面演示了破壞數(shù)據(jù)文件的處理，這些操作在操作系統(tǒng)中進行1. 停止 SQL Server 服務(msqlserver 服務)2. 刪除文件c:db_data.ndf (模擬破壞)3. 重新SQL Server服務，此時數(shù)據(jù)庫DB置疑-*/GO-下面演示了如何恢復數(shù)據(jù)-首先要備份當前日志BACKUP LOG db TO DISK='c:db_log.bak' WITHFORMAT,NO_TRUNCATE-利用文件組備份恢復破壞的文件RESTORE DATABASE db FROM DISK='c:db.bak' WITH NORECOVERY-還原到日志點RESTORE LOG db FROM DISK='c:db_log.bak' WITH RECOVERY-顯示已經(jīng)恢復的數(shù)據(jù)GO3、數(shù)據(jù)還原到指定時間點的處理-創(chuàng)建測試數(shù)據(jù)庫CREATE DATABASE DbGO-對數(shù)據(jù)庫進行備份BACKUP DATABASE Db TO DISK='c:db.bak' WITH FORMATGO-創(chuàng)建測試表CREATE TABLE Db.dbo.TB_test(ID int)-延時1秒鐘，再進行后面的操作(這是由于SQL Server的時間精度最大為百 分之三秒，不延時的話，可能會導致還原到時間點的操作失敗)WAITFOR DELAY '00:00:01'GO-假設我們現(xiàn)在誤操作刪除了 這個表-保存刪除表的時間SELECT dt=GETDATE() INTO #GO-在刪除操作后，發(fā)現(xiàn)不應該刪除表-下面演示了如何恢復這個誤刪除的表-首先,備份事務日志（使用事務日志才能還原到指定的時間點）BACKUP LOG Db TO DISK=' C:Program FilesMicrosoft SQL ServerMSSQL.lMSSQLBackuptestl.bakc:db_log.bak' WITH FORMATGO-接下來，要先還原完全備份（還原日志必須在還原完全備份的基礎上進行）RESTORE DATABASE Db FROM DISK='c:db.bak' WITHREPLACE,NORECOVERYGO-將事務日志還原到刪除操作前（這里的時間對應上面的刪除時間，并比刪 除時間略早DECLARE dt datetimeSELECT dt=DATEADD（ms,-20,dt） FROM # -獲取比表被刪除的時間略 早的時間RESTORE LOG Db FROM DISK='c:db_log.bak' WITH RECOVERYSTOPAT=dtGO-查詢一下,看表是否恢復請給出測試結(jié)果：-最后刪除所做的測試環(huán)境DROP DATABASE DbDROP TABLE #4、在現(xiàn)有數(shù)據(jù)庫上還原時的數(shù)據(jù)文件處理-創(chuàng)建一個測試數(shù)據(jù)庫CREATE DATABASE dbON(NAME=db,FILENAME='c:db.mdf)LOG ON( NAME=db_log, FILENAME='c:db.ldf)-備份并刪除測試數(shù)據(jù)庫BACKUP DATABASE db TO DISK='c:a.bak' WITH FORMATDROP DATABASE db-創(chuàng)建一個文件結(jié)構相同，但物理文件位置不同的數(shù)據(jù)庫CREATE DATABASE dbON(NAME=db,FILENAME='d:db.mdf)LOG ON(NAME=db_log,FILENAME='d:db.ldf)-在新建的數(shù)據(jù)庫上強制還原備份RESTORE DATABASE db FROM DISK='c:a.bak' WITH REPLACE-查看還原后的文件位置SELECT name,filename FROM db.dbo.sysfiles 請給出測試結(jié)果：-刪除測試5、備份數(shù)據(jù)庫，并按日期生成備份目錄/*-調(diào)用示例-備份當前數(shù)據(jù)庫exec p_backupdb-*/if exists (select * from dbo.sysobjects where id =object_id(N'dbo.p_backupdb') and OBJECTPROPERTY(id, NTsProcedure') = 1) drop procedure dbo.p_backupdbGOcreate proc p_backupdbdbname sysname='',-要備份的數(shù)據(jù)庫名稱，不指定則備份當前數(shù)據(jù)庫bkfname nvarchar(260)='',-備份文件名，文件名中可以用DBNAME代表 數(shù)據(jù)庫名,DATE代表日期,TIME代表時間bkpath nvarchar(260)='c:'-備份文件的存放目錄，在該目錄下創(chuàng)建每天備 份的目錄asdeclare sql varchar(8000)if isnull(dbname,")=" set dbname=db_name()if right(bkpath,l)v>'' set bkpath=bkpath+''set bkpath=bkpath+convert(varchar,getdate(),112)+''-創(chuàng)建目錄set sql='md '+bkpathexec master.xp_cmdshell sql,no_output-備份處理訐 isnull(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='backup database '+dbname+' to disk='H+bkpath+bkfname+HHexec(sql)go16.3數(shù)據(jù)導入與導出1、導入得出Excel的基本方法(1) 從Excel文件中導入數(shù)據(jù)到SQL數(shù)據(jù)庫。 如果接受數(shù)據(jù)導入的表已經(jīng)存在insert into 表名 select * fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$) 如果導入數(shù)據(jù)并生成表select * into 表名 fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)(2) 從SQL數(shù)據(jù)庫中，導出數(shù)據(jù)到Excel 如果Excel文件已經(jīng)存在，而且已經(jīng)按照要接收的數(shù)據(jù)創(chuàng)建好表頭，就可以簡單的用:insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'，Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheetl$)select * from 表 如果Excel文件不存在，也可以用BCP來導出Excel的文件(注意大小寫)：EXEC master.xp_cmdshell 'bcp 數(shù)據(jù)庫名.dbo.表名 out "c:test.xls" /c /S'服務器名"/U" 用戶名"-P"密碼" 導出查詢的情況EXEC master.xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs.authors ORDER BYauname" queryout "c:test.xls" /c /S"服務器名"/U"用戶名"-P"密碼"'說明： c:test.xls為導入/導出的Excel文件名要求文件在SQL服務器或者服務器可以訪問的共享目錄上 sheet1$為Excel文件的工作表名，一般要加上$才能正常使用.(3) 將某個目錄上的Excel表，導入到數(shù)據(jù)庫中將所有的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)declare fn varchar(8000)open tbfetch next from tb into fnwhile fetch_status=Obegin-下面是查詢語句，需要根據(jù)你的情況改為插入語句一插入已有的表用:insert into 表 selct * from .一倉U建表用:select * into 表 from .set fn='select * fromOPENROWSET(”MICROSOFT.JET.OLEDB.4.0”，”Excel5.0;HDR=YES;DATABASE='+fn+”'，全部客戶 $)'exec(fn)fetch next from tb into fnendclose tbdeallocate tbdrop table #t(4) 使用DTS進行Excel數(shù)據(jù)的導入導出DTS是Sql Server中進行導入導出的圖形化工具，請使用該工具根據(jù)向?qū)瓿梢韵聦嶒灒?1)用DTS導入向?qū)ⅰ绷荷胶脻h功力排行.xls”文件導入到數(shù)據(jù)庫中保存為“Rank09”表 用DTS導出向?qū)P導出到C:Backup目錄下(如果沒有該目錄，請先創(chuàng)建之)2、練習：其他形式的導入導出使用DTS導入導出向?qū)瓿山?jīng)下練習(1)將CPXS數(shù)據(jù)庫中XSS表導出為Access文件。 語句：insert into OPENROWSET ('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test.xls' ,sheet1$)select * from xss|licrosoft Excel 一 test.xls=1文件® 編輯觀團迪插入格式© 工具數(shù)據(jù)窗口® 幫心 £日品占 丄丨弩爲丄jl魚星土二虛 壬斛丨監(jiān)邇|C12辰ABCDEFG1客戶編號客戶名稱地區(qū)負責人電話27)ooooi廠電公司鎮(zhèn)江匪三)111111123?)00002家電市場免錫李四222222224?)00003由器商場上海333333335?)00004小家電商才用示趙六6666666667)00005廠電公司弟示張三)1111112277)00006小電器商才廈門李四222222228910Y -1(2)將創(chuàng)建一測試數(shù)據(jù)庫Test.MDB并將該數(shù)據(jù)庫表導入到Sql Server中。(3)將CPXS數(shù)據(jù)庫中CP表導出為文本文件。