實驗16 數(shù)據(jù)備份、恢復和導入導出(1)

《實驗16 數(shù)據(jù)備份、恢復和導入導出(1)》由會員分享，可在線閱讀，更多相關《實驗16 數(shù)據(jù)備份、恢復和導入導出(1)（13頁珍藏版）》請在裝配圖網(wǎng)上搜索。

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

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

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 對數(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備份設備上

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

5、_| 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 + _J dbo. si + dbo. XSS + _| dbo. Y + _| myTestllamel. t_Test (3)在CPXS數(shù)據(jù)庫中增加表s。 create table s (sno char(5), sname char(10)) 稱列鍵約融索統(tǒng)

6、 on uu 再將文件組uu追加備份到test1，并將日志追加備份到test1?；謴蜁r，先用test1備份設 備上媒體集1恢復CPXS，再用test1備份設備上媒體集2對CPXS數(shù)據(jù)庫進行差異恢復， 接著用test1備份設備上媒體集1恢復文件組uu，然后用test1備份設備上媒體集4進行日志 恢復。注意過程和觀察結果。 CS-FC. cpzs - - - LQuery3. sql* 摘要 create table 3 (sno char ；5)r sname char(10)) □ n j. j. _J］消息 命令已成功完戚° 備份組件： ⑥數(shù)據(jù)庫⑻

7、O文件和文件組(G): I | | 備份集 名稱?: 護弓-爭務日志 備份 Microsoft SQL Server IanageMent Studio X Q對數(shù)據(jù)庫"中浴'的備份已成功完成° 確定 備份到： ■?磁盤① CllFrograrn Fi]■已 匚^ 口弓口￡t EQL Eef 耳％丁 '副 EEQL.「共 E1QLA 乃注匚 . ~bak 16.2異常情況下的數(shù)據(jù)恢復 1、部分數(shù)據(jù)文件損壞的修復處理 請完成以下練習，理解每一個操作過程： --創(chuàng)建數(shù)據(jù)庫 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)中進行 1. 停

10、止 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' WITH FORMAT,NO_TRUNCATE --利用文件組備份恢復破壞的文件 RESTORE DATABASE db FILEGROUP='db_fgl' FROM DISK='c:\db_fgl.bak' WITH NORECOVERY --還

11、原到日志點 RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY --顯示已經(jīng)恢復的數(shù)據(jù) GO --刪除測試 DROP DATABASE db 2、僅有日志文件的數(shù)據(jù)修復處理 --創(chuàng)建數(shù)據(jù)庫 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)中進行 1. 停止 SQL Server 服務(msqlserver 服務) 2. 刪除文件c:\db_data.ndf (

13、模擬破壞) 3. 重新SQL Server服務，此時數(shù)據(jù)庫DB置疑 --*/ GO --下面演示了如何恢復數(shù)據(jù) --首先要備份當前日志 BACKUP LOG db TO DISK='c:\db_log.bak' WITH FORMAT,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ù) GO

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

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

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

17、ABLE # 4、在現(xiàn)有數(shù)據(jù)庫上還原時的數(shù)據(jù)文件處理 --創(chuàng)建一個測試數(shù)據(jù)庫 CREATE DATABASE db ON( 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 FORMAT DROP DATABASE db --創(chuàng)建一個文件結構相同，但物理文件位置不同的數(shù)據(jù)庫 CREATE DATABASE db ON( NAME=db, FILENAME='d:

18、\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 請給出測試結果： --刪除測試 5、備份數(shù)據(jù)庫，并按日期生成備份目錄 /*--調用示例 --備份當前數(shù)據(jù)庫 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ù)庫名稱，不指定則備份當前 數(shù)據(jù)庫 @bkfname nvarchar(260)='',--備份文件名，文件名中可以用\DBNAME代表 數(shù)據(jù)庫名,\DATE代表日期,\TIME\代表時間 @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ù)導入與導出 1、導入得出Excel的基本方法 (1) 從Excel文件中導入數(shù)據(jù)到SQL數(shù)據(jù)庫。 ① 如果接受數(shù)據(jù)導入的表已經(jīng)存在 insert into 表名 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$) ② 如果導入數(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ù)庫中，導出數(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

24、 '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 BYau」name" queryout "c:\test.xls" /c /S"服務器名"/U"用戶名"-P"密碼"' 說明： ① c:\test.xls為導入/導出的Excel文件名?要求文件在SQL服務器或者服務器可以訪問的共享目錄上 ② sheet1$為Excel文件的工作

25、表名，一般要加上$才能正常使用. (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，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 --下面是查詢語句，需要根據(jù)你的情況改為插入語句 一插入已有的表用:insert into 表 selct * from ... 一倉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進行Excel數(shù)據(jù)的導入導出 DTS是Sql Server中進行導入導出的圖形化工具，請使用該工具根據(jù)向導完成以下實 驗： (1)用DTS導入向導將”梁山好漢功力排行.xls”文件導入到數(shù)據(jù)庫中保存為“Rank09”表 ⑵ 用DTS導出向導將CP導出到C:\Backup目錄下(如果沒有該目錄，請先創(chuàng)建之) 2、練習：其他形式的導入導出 使用DTS導入導出向導完成經(jīng)下練習

28、(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 辰 A B C D E F G 1 客戶編號 客戶名稱 地區(qū) 負

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