數(shù)據(jù)庫(kù)原理與應(yīng)用實(shí)驗(yàn)16-

PAGE第34頁(yè)共34頁(yè)廈門理工學(xué)院實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)十六數(shù)據(jù)備份、恢復(fù)和導(dǎo)入導(dǎo)出姓名：計(jì)算機(jī)科學(xué)與技術(shù)學(xué)號(hào)：專業(yè)：班級(jí)：同組人：無實(shí)驗(yàn)日期：【實(shí)驗(yàn)?zāi)康呐c要求】【實(shí)驗(yàn)內(nèi)容與步驟】16.1正常情況下的數(shù)據(jù)備份與恢復(fù)1、創(chuàng)建命名備份設(shè)備在C:\ProgramFiles\MicrosoftSQLServer\MSSQL\BACKUP\下創(chuàng)建一個(gè)名為“test1”的命名備份設(shè)備。2、數(shù)據(jù)庫(kù)的備份和恢復(fù)（1）將整個(gè)CPXS數(shù)據(jù)庫(kù)備份到test1，并利用該備份集恢復(fù)CPXS數(shù)據(jù)庫(kù)。（2）在CPXS數(shù)據(jù)庫(kù)中增加表s1。createtables1(snochar(5),snamechar(10))再差異備份CPXS數(shù)據(jù)庫(kù)追加到test1?；謴?fù)時(shí)，先用test1備份設(shè)備上媒體集1恢復(fù)CPXS，再用test1備份設(shè)備上媒體集2對(duì)CPXS數(shù)據(jù)庫(kù)進(jìn)行差異恢復(fù)。注意過程和觀察結(jié)果。（3）在CPXS數(shù)據(jù)庫(kù)中增加表s。createtables(snochar(5),snamechar(10))onuu再將文件組uu追加備份到test1，并將日志追加備份到test1?；謴?fù)時(shí)，先用test1備份設(shè)備上媒體集1恢復(fù)CPXS，再用test1備份設(shè)備上媒體集2對(duì)CPXS數(shù)據(jù)庫(kù)進(jìn)行差異恢復(fù)，接著用test1備份設(shè)備上媒體集1恢復(fù)文件組uu，然后用test1備份設(shè)備上媒體集4進(jìn)行日志恢復(fù)。注意過程和觀察結(jié)果。16.2異常情況下的數(shù)據(jù)恢復(fù)1、部分?jǐn)?shù)據(jù)文件損壞的修復(fù)處理請(qǐng)完成以下練習(xí)，理解每一個(gè)操作過程：--創(chuàng)建數(shù)據(jù)庫(kù)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--創(chuàng)建表CREATETABLEdb.dbo.ta(idint)ON[PRIMARY]CREATETABLEdb.dbo.tb(idint)ONdb_fg1CREATETABLEdb.dbo.tc(idint)ONdb_fg2INSERTdb.dbo.tbSELECTidFROMsysobjectsGO--做文件組備份BACKUPDATABASEdbFILEGROUP='db_fg1'TODISK='c:\db_fg1.bak'WITHFORMAT--備份后,再做數(shù)據(jù)處理INSERTdb.dbo.taSELECTidFROMsysobjectsGO/*--下面演示了破壞數(shù)據(jù)文件的處理，這些操作在操作系統(tǒng)中進(jìn)行1.停止SQLServer服務(wù)(msqlserver服務(wù))2.刪除文件c:\db_fg1_data.ndf(模擬破壞)3.重新SQLServer服務(wù),此時(shí)數(shù)據(jù)庫(kù)DB置疑--*/GO--下面演示了如何恢復(fù)數(shù)據(jù)--首先要備份當(dāng)前日志BACKUPLOGdbTODISK='c:\db_log.bak'WITHFORMAT,NO_TRUNCATE--利用文件組備份恢復(fù)破壞的文件RESTOREDATABASEdbFILEGROUP='db_fg1'FROMDISK='c:\db_fg1.bak'WITHNORECOVERY--還原到日志點(diǎn)RESTORELOGdbFROMDISK='c:\db_log.bak'WITHRECOVERY--顯示已經(jīng)恢復(fù)的數(shù)據(jù)SELECTCOUNT(*)FROMdb.dbo.tbSELECTCOUNT(*)FROMdb.dbo.taGO--刪除測(cè)試DROPDATABASEdb2、僅有日志文件的數(shù)據(jù)修復(fù)處理--創(chuàng)建數(shù)據(jù)庫(kù)CREATEDATABASEdbONPRIMARY( NAME='db_data', FILENAME='c:\db_data.mdf')LOGON( NAME='db_log', FILENAME='c:\db.ldf')GO--創(chuàng)建表CREATETABLEdb.dbo.ta(idint)INSERTdb.dbo.taSELECTidFROMsysobjectsGO--做文件組備份BACKUPDATABASEdbTODISK='c:\db.bak'WITHFORMAT--備份后,再做數(shù)據(jù)處理CREATETABLEdb.dbo.tb(idint)INSERTdb.dbo.tbSELECTidFROMsysobjectsGO/*--下面演示了破壞數(shù)據(jù)文件的處理，這些操作在操作系統(tǒng)中進(jìn)行1.停止SQLServer服務(wù)(msqlserver服務(wù))2.刪除文件c:\db_data.ndf(模擬破壞)3.重新SQLServer服務(wù),此時(shí)數(shù)據(jù)庫(kù)DB置疑--*/GO--下面演示了如何恢復(fù)數(shù)據(jù)--首先要備份當(dāng)前日志BACKUPLOGdbTODISK='c:\db_log.bak'WITHFORMAT,NO_TRUNCATE--利用文件組備份恢復(fù)破壞的文件RESTOREDATABASEdbFROMDISK='c:\db.bak'WITHNORECOVERY--還原到日志點(diǎn)RESTORELOGdbFROMDISK='c:\db_log.bak'WITHRECOVERY--顯示已經(jīng)恢復(fù)的數(shù)據(jù)SELECTCOUNT(*)FROMdb.dbo.taSELECTCOUNT(*)FROMdb.dbo.tbGO3、數(shù)據(jù)還原到指定時(shí)間點(diǎn)的處理--創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)CREATEDATABASEDbGO--對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份BACKUPDATABASEDbTODISK='c:\db.bak'WITHFORMATGO--創(chuàng)建測(cè)試表CREATETABLEDb.dbo.TB_test(IDint)--延時(shí)1秒鐘,再進(jìn)行后面的操作(這是由于SQLServer的時(shí)間精度最大為百分之三秒,不延時(shí)的話,可能會(huì)導(dǎo)致還原到時(shí)間點(diǎn)的操作失敗)WAITFORDELAY'00:00:01'GO--假設(shè)我們現(xiàn)在誤操作刪除了Db.dbo.TB_test這個(gè)表DROPTABLEDb.dbo.TB_test--保存刪除表的時(shí)間SELECTdt=GETDATE()INTO#GO--在刪除操作后,發(fā)現(xiàn)不應(yīng)該刪除表Db.dbo.TB_test--下面演示了如何恢復(fù)這個(gè)誤刪除的表Db.dbo.TB_test--首先,備份事務(wù)日志(使用事務(wù)日志才能還原到指定的時(shí)間點(diǎn))BACKUPLOGDbTODISK='c:\db_log.bak'WITHFORMATGO--接下來,要先還原完全備份(還原日志必須在還原完全備份的基礎(chǔ)上進(jìn)行)RESTOREDATABASEDbFROMDISK='c:\db.bak'WITHREPLACE,NORECOVERYGO--將事務(wù)日志還原到刪除操作前（這里的時(shí)間對(duì)應(yīng)上面的刪除時(shí)間，并比刪除時(shí)間略早DECLARE@dtdatetimeSELECT@dt=DATEADD(ms,-20,dt)FROM#--獲取比表被刪除的時(shí)間略早的時(shí)間RESTORELOGDbFROMDISK='c:\db_log.bak'WITHRECOVERY,STOPAT=@dtGO--查詢一下,看表是否恢復(fù)SELECT*FROMDb.dbo.TB_test請(qǐng)給出測(cè)試結(jié)果：--最后刪除所做的測(cè)試環(huán)境DROPDATABASEDbDROPTABLE#4、在現(xiàn)有數(shù)據(jù)庫(kù)上還原時(shí)的數(shù)據(jù)文件處理--創(chuàng)建一個(gè)測(cè)試數(shù)據(jù)庫(kù)CREATEDATABASEdbON( NAME=db, FILENAME='c:\db.mdf')LOGON( NAME=db_log, FILENAME='c:\db.ldf')--備份并刪除測(cè)試數(shù)據(jù)庫(kù)BACKUPDATABASEdbTODISK='c:\a.bak'WITHFORMATDROPDATABASEdb--創(chuàng)建一個(gè)文件結(jié)構(gòu)相同,但物理文件位置不同的數(shù)據(jù)庫(kù)CREATEDATABASEdbON( NAME=db, FILENAME='d:\db.mdf')LOGON( NAME=db_log, FILENAME='d:\db.ldf')--在新建的數(shù)據(jù)庫(kù)上強(qiáng)制還原備份RESTOREDATABASEdbFROMDISK='c:\a.bak'WITHREPLACE--查看還原后的文件位置SELECTname,filenameFROMdb.dbo.sysfiles請(qǐng)給出測(cè)試結(jié)果：--刪除測(cè)試5、備份數(shù)據(jù)庫(kù),并按日期生成備份目錄/*--調(diào)用示例--備份當(dāng)前數(shù)據(jù)庫(kù) execp_backupdb--*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_backupdb]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropprocedure[dbo].[p_backupdb]GOcreateprocp_backupdb@dbnamesysname='', --要備份的數(shù)據(jù)庫(kù)名稱,不指定則備份當(dāng)前數(shù)據(jù)庫(kù)@bkfnamenvarchar(260)='', --備份文件名,文件名中可以用\DBNAME\代表數(shù)據(jù)庫(kù)名,\DATE\代表日期,\TIME\代表時(shí)間@bkpathnvarchar(260)='c:\' --備份文件的存放目錄,在該目錄下創(chuàng)建每天備份的目錄as declare@sqlvarchar(8000) ifisnull(@dbname,'')=''set@dbname=db_name() ifright(@bkpath,1)<>'\'set@bkpath=@bkpath+'\' set@bkpath=@bkpath+convert(varchar,getdate(),112)+'\' --創(chuàng)建目錄 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)go16.3數(shù)據(jù)導(dǎo)入與導(dǎo)出1、導(dǎo)入/導(dǎo)出Excel的基本方法（1）從Excel文件中導(dǎo)入數(shù)據(jù)到SQL數(shù)據(jù)庫(kù)。=1\*GB3①如果接受數(shù)據(jù)導(dǎo)入的表已經(jīng)存在insertinto表名select*fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)需要先使用這段語句execsp_configure'showadvancedoptions',1reconfigureexecsp_configure'AdHocDistributedQueries',1reconfigure=2\*GB3②如果導(dǎo)入數(shù)據(jù)并生成表select*into表名fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)（2）從SQL數(shù)據(jù)庫(kù)中,導(dǎo)出數(shù)據(jù)到Excel=1\*GB3①如果Excel文件已經(jīng)存在,而且已經(jīng)按照要接收的數(shù)據(jù)創(chuàng)建好表頭,就可以簡(jiǎn)單的用:insertintoOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)select*from表=2\*GB3②如果Excel文件不存在,也可以用BCP來導(dǎo)出Excel的文件(注意大小寫):EXECmaster..xp_cmdshell'bcp數(shù)據(jù)庫(kù)名.dbo.表名out"c:\test.xls"/c/S"服務(wù)器名"/U"用戶名"-P"密碼"'=3\*GB3③導(dǎo)出查詢的情況EXECmaster..xp_cmdshell'bcp"SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_lname"queryout"c:\test.xls"/c/S"服務(wù)器名"/U"用戶名"-P"密碼"'說明:=1\*GB3①c:\test.xls為導(dǎo)入/導(dǎo)出的Excel文件名.要求文件在SQL服務(wù)器或者服務(wù)器可以訪問的共享目錄上=2\*GB3②sheet1$為Excel文件的工作表名,一般要加上$才能正常使用.（3）將某個(gè)目錄上的Excel表,導(dǎo)入到數(shù)據(jù)庫(kù)中將所有的Excel文件放到一個(gè)目錄中,假設(shè)為c:\test\,然后用下面的方法來做createtable#t(fnamevarchar(260),depthint,isfbit)insertinto#texecmaster..xp_dirtree'c:\test',1,1declaretbcursorforselectfn='c:\test'+fnamefrom#t whereisf=1andfnamelike'%.x