《實(shí)驗(yàn)16 數(shù)據(jù)備份、恢復(fù)和導(dǎo)入導(dǎo)出(1)》由會(huì)員分享，可在線閱讀，更多相關(guān)《實(shí)驗(yàn)16 數(shù)據(jù)備份、恢復(fù)和導(dǎo)入導(dǎo)出(1)（13頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、實(shí)驗(yàn)十六 數(shù)據(jù)備份、恢復(fù)和導(dǎo)入導(dǎo)出 姓名： 學(xué) 號(hào) ： 專業(yè)： 班 級(jí) ： 同組人： 無(wú) 實(shí)驗(yàn)日期：2012-6-9 【實(shí)驗(yàn)?zāi)康呐c要求】 1. 學(xué)會(huì)使用企業(yè)管理器、備份命令創(chuàng)建和刪除命名備份設(shè)備 2. 掌握使用企業(yè)管理器、備份命令進(jìn)行數(shù)據(jù)庫(kù)的備份 3. 掌握使用企業(yè)管理器和恢復(fù)命令進(jìn)行數(shù)據(jù)庫(kù)勺恢復(fù) 4. 掌握數(shù)據(jù)勺導(dǎo)入導(dǎo)出。 【實(shí)驗(yàn)內(nèi)容與步驟】 16?1正常情況下的數(shù)據(jù)備份與恢復(fù) 1、創(chuàng)建命名備份設(shè)備 在 C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\下創(chuàng)建一個(gè)名為“testl"的命名 備份設(shè)備

2、。 + , E: I下載軟件\C#( ABO ) (1). I + , Newcpxs 1+ |J SPJ + , student + , XSBOOK + j XSStudent + 口妄全性 -口服務(wù)器對(duì)象 -口備論謨邕 23：teEtl : + 口捱崔眼寡常 + 口服務(wù)器融發(fā)器 — ■=■― lU.I 2、數(shù)據(jù)庫(kù)的備份和恢復(fù) (1)將整個(gè)CPXS數(shù)據(jù)庫(kù)備份到test1，并利用該備份集恢復(fù)CPXS數(shù)據(jù)庫(kù)。 0覆蓋現(xiàn)有數(shù)據(jù)庫(kù) □保留復(fù)制設(shè)置 □還原毎于備份之前進(jìn)行提示 □限制訪問(wèn)還原的數(shù)據(jù)庫(kù)(￡) 將數(shù)據(jù)庫(kù)文件還原為: 原始文件名 還原為

3、 CFKS_D I C: \Fr u gi_ ：=iiti C: \Fr ogi_：=iiTi FileE'iMicrDEof' Files'i.Mi cr osof ■ CFXS_L rosoft SQL Server Ianage>ent Studio Express 對(duì)數(shù)據(jù)庫(kù)艾p茫憐還原已成功完成口 (2)在CPXS數(shù)據(jù)庫(kù)中增加表si。 create table si (sno char(5), sname char(10)) 再差異備份CPXS數(shù)據(jù)庫(kù)追加到test1。恢復(fù)時(shí)，先用test1備份設(shè)備上媒體集1恢復(fù)CPXS, 再用test1備份設(shè)備上

4、媒體集2對(duì)CPXS數(shù)據(jù)庫(kù)進(jìn)行差異恢復(fù)。注意過(guò)程和觀察結(jié)果。 建表成功： + _l 據(jù)庫(kù) - □ CPXS + 口 數(shù)據(jù)庫(kù)關(guān)系閤 - 口 表 + 口 系統(tǒng)表 + dLo. CF + dLo. CFXSB + idbu. zl i + dLo.X l+l =1 第一次還原: -口 + LJ系繞數(shù)據(jù)庫(kù) _ j CFXS 〔I 口數(shù)據(jù)庫(kù)關(guān)系團(tuán) _ _|表 + _|系統(tǒng)表 + _| db o. CP + _3 dbo. CPXSB +

5、_| dbo. X + dbo. XSS + _| db o. Y + _| myTeEtNamel. t_Test 第二次還原: -LJ數(shù)據(jù)庫(kù) + 口系統(tǒng)數(shù)據(jù)庫(kù) - )CFXS + 口數(shù)據(jù)庫(kù)關(guān)系圖 T □表 + |_J系統(tǒng)表 + _| dbo. CF + dbo. CFXSB + _J + _J dbo. si + dbo. XSS + _| dbo. Y + _| myTestllamel. t_Test (3)在CPXS數(shù)據(jù)庫(kù)中增加表s。 create table s (sno char(5), sname char(10)) 稱列鍵約融索統(tǒng)

6、 on uu 再將文件組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ù)。注意過(guò)程和觀察結(jié)果。 CS-FC. cpzs - - - LQuery3. sql* 摘要 create table 3 (sno char ；5)r sname char(10)) □ n j. j. _J］消息 命令已成功完戚° 備份組件： ⑥數(shù)據(jù)庫(kù)⑻

7、O文件和文件組(G): I | | 備份集 名稱?: 護(hù)弓-爭(zhēng)務(wù)日志 備份 Microsoft SQL Server IanageMent Studio X Q對(duì)數(shù)據(jù)庫(kù)"中浴'的備份已成功完成° 確定 備份到： ■?磁盤① CllFrograrn Fi]■已 匚^ 口弓口￡t EQL Eef 耳％丁 '副 EEQL.「共 E1QLA 乃注匚 . ~bak 16.2異常情況下的數(shù)據(jù)恢復(fù) 1、部分?jǐn)?shù)據(jù)文件損壞的修復(fù)處理 請(qǐng)完成以下練習(xí)，理解每一個(gè)操作過(guò)程： --創(chuàng)建數(shù)據(jù)庫(kù) CREATE DATABASE db ON PRIMARY( NAME='db_data', F

8、ILENAME= '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 [PRIMARY] CREATE TABLE db.d

9、bo.tb(id int) ON db_fg1 CREATE TABLE db.dbo.tc(id int) ON db_fg2 INSERT db.dbo.tb SELECT id FROM sysobjects GO --做文件組備份 BACKUP DATABASE db FILEGROUP='db_fg1' TO DISK='c:\db_fg1.bak' WITH FORMAT --備份后，再做數(shù)據(jù)處理 INSERT db.dbo.ta SELECT id FROM sysobjects GO /*--下面演示了破壞數(shù)據(jù)文件的處理，這些操作在操作系統(tǒng)中進(jìn)行 1. 停

10、止 SQL Server 服務(wù)(msqlserver 服務(wù)) 2. 刪除文件c:\db_fg1_data.ndf (模擬破壞) 3. 重新SQL Server服務(wù)，此時(shí)數(shù)據(jù)庫(kù)DB置疑 --*/ GO --下面演示了如何恢復(fù)數(shù)據(jù) --首先要備份當(dāng)前日志 BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT,NO_TRUNCATE --利用文件組備份恢復(fù)破壞的文件 RESTORE DATABASE db FILEGROUP='db_fgl' FROM DISK='c:\db_fgl.bak' WITH NORECOVERY --還

11、原到日志點(diǎn) RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY --顯示已經(jīng)恢復(fù)的數(shù)據(jù) GO --刪除測(cè)試 DROP DATABASE db 2、僅有日志文件的數(shù)據(jù)修復(fù)處理 --創(chuàng)建數(shù)據(jù)庫(kù) CREATE DATABASE db ON 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 in

12、t) INSERT db.dbo.ta SELECT id FROM sysobjects GO --做文件組備份 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 sysobjects GO /*--下面演示了破壞數(shù)據(jù)文件的處理，這些操作在操作系統(tǒng)中進(jìn)行 1. 停止 SQL Server 服務(wù)(msqlserver 服務(wù)) 2. 刪除文件c:\db_data.ndf (

13、模擬破壞) 3. 重新SQL Server服務(wù)，此時(shí)數(shù)據(jù)庫(kù)DB置疑 --*/ GO --下面演示了如何恢復(fù)數(shù)據(jù) --首先要備份當(dāng)前日志 BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT,NO_TRUNCATE --利用文件組備份恢復(fù)破壞的文件 RESTORE DATABASE db FROM DISK='c:\db.bak' WITH NORECOVERY --還原到日志點(diǎn) RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY --顯示已經(jīng)恢復(fù)的數(shù)據(jù) GO

14、 3、數(shù)據(jù)還原到指定時(shí)間點(diǎn)的處理 --創(chuàng)建測(cè)試數(shù)據(jù)庫(kù) CREATE DATABASE Db GO --對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份 BACKUP DATABASE Db TO DISK='c:\db.bak' WITH FORMAT GO --創(chuàng)建測(cè)試表 CREATE TABLE Db.dbo.TB_test(ID int) --延時(shí)1秒鐘，再進(jìn)行后面的操作(這是由于SQL Server的時(shí)間精度最大為百 分之三秒，不延時(shí)的話，可能會(huì)導(dǎo)致還原到時(shí)間點(diǎn)的操作失敗) WAITFOR DELAY '00:00:01' GO --假設(shè)我們現(xiàn)在誤操作刪除了 這個(gè)表 --保存刪除表的時(shí)

15、間 SELECT dt=GETDATE() INTO # GO --在刪除操作后，發(fā)現(xiàn)不應(yīng)該刪除表 --下面演示了如何恢復(fù)這個(gè)誤刪除的表 --首先,備份事務(wù)日志（使用事務(wù)日志才能還原到指定的時(shí)間點(diǎn)） BACKUP LOG Db TO DISK=' C:\Program Files\Microsoft SQL Server\MSSQL.l\MSSQL\Backup\testl.bakc:\db_log.bak' WITH FORMAT GO --接下來(lái)，要先還原完全備份（還原日志必須在還原完全備份的基礎(chǔ)上進(jìn)行） RESTORE DATABASE Db FROM DISK='c:

16、\db.bak' WITH REPLACE,NORECOVERY GO --將事務(wù)日志還原到刪除操作前（這里的時(shí)間對(duì)應(yīng)上面的刪除時(shí)間，并比刪 除時(shí)間略早 DECLARE @dt datetime SELECT @dt=DATEADD（ms,-20,dt） FROM # --獲取比表被刪除的時(shí)間略 早的時(shí)間 RESTORE LOG Db FROM DISK='c:\db_log.bak' WITH RECOVERYSTOPAT=@dt GO --查詢一下,看表是否恢復(fù) 請(qǐng)給出測(cè)試結(jié)果： --最后刪除所做的測(cè)試環(huán)境 DROP DATABASE Db DROP T

17、ABLE # 4、在現(xiàn)有數(shù)據(jù)庫(kù)上還原時(shí)的數(shù)據(jù)文件處理 --創(chuàng)建一個(gè)測(cè)試數(shù)據(jù)庫(kù) CREATE DATABASE db ON( NAME=db, FILENAME='c:\db.mdf) LOG ON( NAME=db_log, FILENAME='c:\db.ldf) --備份并刪除測(cè)試數(shù)據(jù)庫(kù) BACKUP DATABASE db TO DISK='c:\a.bak' WITH FORMAT DROP DATABASE db --創(chuàng)建一個(gè)文件結(jié)構(gòu)相同，但物理文件位置不同的數(shù)據(jù)庫(kù) CREATE DATABASE db ON( NAME=db, FILENAME='d:

18、\db.mdf) LOG ON( NAME=db_log, FILENAME='d:\db.ldf) --在新建的數(shù)據(jù)庫(kù)上強(qiáng)制還原備份 RESTORE DATABASE db FROM DISK='c:\a.bak' WITH REPLACE --查看還原后的文件位置 SELECT name,filename FROM db.dbo.sysfiles 請(qǐng)給出測(cè)試結(jié)果： --刪除測(cè)試 5、備份數(shù)據(jù)庫(kù)，并按日期生成備份目錄 /*--調(diào)用示例 --備份當(dāng)前數(shù)據(jù)庫(kù) exec p_backupdb --*/ if exists (select * from dbo.s

19、ysobjects where id = object_id(N'[dbo].[p_backupdb]') and OBJECTPROPERTY(id, NTsProcedure') = 1) drop procedure [dbo].[p_backupdb] GO create proc p_backupdb @dbname sysname='', --要備份的數(shù)據(jù)庫(kù)名稱，不指定則備份當(dāng)前 數(shù)據(jù)庫(kù) @bkfname nvarchar(260)='',--備份文件名，文件名中可以用\DBNAME代表 數(shù)據(jù)庫(kù)名,\DATE代表日期,\TIME\代表時(shí)間 @bkpath nvarch

20、ar(260)='c:\'--備份文件的存放目錄，在該目錄下創(chuàng)建每天備 份的目錄 as declare @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 '+@bkpath exec master..xp_cmdshell @sql,no_output -

21、-備份處理 訐 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+@bkfnam

22、e+HH exec(@sql) go 16.3數(shù)據(jù)導(dǎo)入與導(dǎo)出 1、導(dǎo)入得出Excel的基本方法 (1) 從Excel文件中導(dǎo)入數(shù)據(jù)到SQL數(shù)據(jù)庫(kù)。 ① 如果接受數(shù)據(jù)導(dǎo)入的表已經(jīng)存在 insert into 表名 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$) ② 如果導(dǎo)入數(shù)據(jù)并生成表 select * into 表名 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel

23、 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$) (2) 從SQL數(shù)據(jù)庫(kù)中，導(dǎo)出數(shù)據(jù)到Excel ① 如果Excel文件已經(jīng)存在，而且已經(jīng)按照要接收的數(shù)據(jù)創(chuàng)建好表頭，就可以簡(jiǎn)單的用: insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ，‘Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheetl$) select * from 表 ② 如果Excel文件不存在，也可以用BCP來(lái)導(dǎo)出Excel的文件(注意大小寫)： EXEC master..xp_cmdshell

24、 'bcp 數(shù)據(jù)庫(kù)名.dbo.表名 out "c:\test.xls" /c /S'服務(wù)器名"/U" 用戶名"-P"密碼"’ ③ 導(dǎo)出查詢的情況 EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BYau」name" queryout "c:\test.xls" /c /S"服務(wù)器名"/U"用戶名"-P"密碼"' 說(shuō)明： ① c:\test.xls為導(dǎo)入/導(dǎo)出的Excel文件名?要求文件在SQL服務(wù)器或者服務(wù)器可以訪問(wèn)的共享目錄上 ② sheet1$為Excel文件的工作

25、表名，一般要加上$才能正常使用. (3) 將某個(gè)目錄上的Excel表，導(dǎo)入到數(shù)據(jù)庫(kù)中 將所有的Excel文件放到一個(gè)目錄中，假設(shè)為c:\test\，然后用下面的方法來(lái)做 create table #t(fname varchar(260)，depth int，isf bit) insert into #t exec master..xp_dirtree 'c:\test'，1，1 declare tb cursor for select fn='c:\test'+fname from #t where isf=1 and fname like '%.xls'--取.xls 文件(E

26、XCEL) declare @fn varchar(8000) open tb fetch next from tb into @fn while @@fetch_status=O begin --下面是查詢語(yǔ)句，需要根據(jù)你的情況改為插入語(yǔ)句 一插入已有的表用:insert into 表 selct * from ... 一倉(cāng)U建表用:select * into 表 from ... set @fn='select * from OPENROWSET(”MICROSOFT.JET.OLEDB.4.0”，”Excel 5.0;HDR=YES;DATABASE='+@fn+”

27、'，全部客戶 $)' exec(@fn) fetch next from tb into @fn end close tb deallocate tb drop table #t (4) 使用DTS進(jìn)行Excel數(shù)據(jù)的導(dǎo)入導(dǎo)出 DTS是Sql Server中進(jìn)行導(dǎo)入導(dǎo)出的圖形化工具，請(qǐng)使用該工具根據(jù)向?qū)瓿梢韵聦?shí) 驗(yàn)： (1)用DTS導(dǎo)入向?qū)ⅰ绷荷胶脻h功力排行.xls”文件導(dǎo)入到數(shù)據(jù)庫(kù)中保存為“Rank09”表 ⑵ 用DTS導(dǎo)出向?qū)P導(dǎo)出到C:\Backup目錄下(如果沒(méi)有該目錄，請(qǐng)先創(chuàng)建之) 2、練習(xí)：其他形式的導(dǎo)入導(dǎo)出 使用DTS導(dǎo)入導(dǎo)出向?qū)瓿山?jīng)下練習(xí)

28、(1)將CPXS數(shù)據(jù)庫(kù)中XSS表導(dǎo)出為Access文件。 語(yǔ)句： 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^文件? 編輯⑥觀團(tuán)迪插入①格式? 工具⑴數(shù)據(jù)?窗口? 幫 心 ￡日品』占 丄丨弩爲(wèi)丄jl魚星土二虛 壬■斛丨監(jiān)邇| C12 辰 A B C D E F G 1 客戶編號(hào) 客戶名稱 地區(qū) 負(fù)

29、責(zé)人 電話 2 7)ooooi 廠電公司 鎮(zhèn)江 匪三 )11111112 3 ?)00002 家電市場(chǎng) 免錫 李四 ^22222222 4 ?)00003 由器商場(chǎng) 上海 ^33333333 5 ?)00004 小家電商才 用示 趙六 ^66666666 6 7)00005 廠電公司 弟示 張三 )11111122 7 7)00006 小電器商才 廈門 李四 ^22222222 8 9 10 Y -1 (2)將創(chuàng)建一測(cè)試數(shù)據(jù)庫(kù)Test.MDB并將該數(shù)據(jù)庫(kù)表導(dǎo)入到Sql Server中。 (3)將CPXS數(shù)據(jù)庫(kù)中CP表導(dǎo)出為文本文件。