實(shí)驗(yàn)16數(shù)據(jù)備份、恢復(fù)和導(dǎo)入導(dǎo)出

1、實(shí)驗(yàn)十六數(shù)據(jù)備份、恢復(fù)和導(dǎo)入導(dǎo)出【實(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 FilesMicrosoft SQL ServerMSSQLBACKUP下創(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。create tabl

2、e s1(sno char(5), sname char(10)再差異備份CPXS數(shù)據(jù)庫(kù)追加到test1?；謴?fù)時(shí)，先用test1備份設(shè)備上媒體集1恢復(fù)CPXS，再用test1備份設(shè)備上媒體集2 對(duì)CPXS數(shù)據(jù)庫(kù)進(jìn)行差異恢復(fù)。注意過(guò)程和觀察結(jié)果。（3）在CPXS數(shù)據(jù)庫(kù)中增加表s。create table s(sno char(5), sname char(10) 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ù)文件組

3、uu，然后用test1備份設(shè)備上媒體集4進(jìn)行日志恢復(fù)。注意過(guò)程和觀察結(jié)果。16.2 異常情況下的數(shù)據(jù)恢復(fù)1、部分?jǐn)?shù)據(jù)文件損壞的修復(fù)處理請(qǐng)完成以下練習(xí)，理解每一個(gè)操作過(guò)程：-創(chuàng)建數(shù)據(jù)庫(kù)CREATE DATABASE dbON PRIMARY(NAME='db_data',FILENAME= 'c:db_data.mdf'),FILEGROUP db_fg1(NAME = 'db_fg1_data',FILENAME = 'c:db_fg1_data.ndf'),FILEGROUP db_fg2(NAME = 'db_fg2_

4、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

5、 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)中進(jìn)行1. 停止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=

6、9;c:db_log.bak' WITH FORMAT,NO_TRUNCATE-利用文件組備份恢復(fù)破壞的文件RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:db_fg1.bak' WITH NORECOVERY-還原到日志點(diǎn)RESTORE LOG db FROM DISK='c:db_log.bak' WITH RECOVERY-顯示已經(jīng)恢復(fù)的數(shù)據(jù)SELECT COUNT(*) FROM db.dbo.tbSELECT COUNT(*) FROM db.dbo.taGO-刪除測(cè)試DR

7、OP DATABASE db2、僅有日志文件的數(shù)據(jù)修復(fù)處理-創(chuàng)建數(shù)據(jù)庫(kù)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:

8、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)中進(jìn)行1. 停止SQL Server服務(wù)(msqlserver服務(wù))2. 刪除文件 c:db_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

9、 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ù)SELECT COUNT(*) FROM db.dbo.taSELECT COUNT(*) FROM db.dbo.tbGO3、數(shù)據(jù)還原到指定時(shí)間點(diǎn)的處理-創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)CREATE DATABASE DbGO-對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份BACKUP DAT

10、ABASE Db TO DISK='c:db.bak' WITH FORMATGO-創(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)在誤操作刪除了 Db.dbo.TB_test 這個(gè)表DROP TABLE Db.dbo.TB_test-保存刪除表的時(shí)間SELECT dt=GETDATE() INTO #GO-在刪除操作后,發(fā)現(xiàn)不應(yīng)該刪除

11、表 Db.dbo.TB_test-下面演示了如何恢復(fù)這個(gè)誤刪除的表 Db.dbo.TB_test-首先,備份事務(wù)日志(使用事務(wù)日志才能還原到指定的時(shí)間點(diǎn))BACKUP LOG Db TO DISK='c:db_log.bak' WITH FORMATGO-接下來(lái),要先還原完全備份(還原日志必須在還原完全備份的基礎(chǔ)上進(jìn)行)RESTORE DATABASE Db FROM DISK='c:db.bak' WITH REPLACE,NORECOVERYGO-將事務(wù)日志還原到刪除操作前（這里的時(shí)間對(duì)應(yīng)上面的刪除時(shí)間，并比刪除時(shí)間略早DECLARE dt datetime

12、SELECT dt=DATEADD(ms,-20,dt) FROM # -獲取比表被刪除的時(shí)間略早的時(shí)間RESTORE LOG Db FROM DISK='c:db_log.bak' WITH RECOVERY,STOPAT=dtGO-查詢一下,看表是否恢復(fù)SELECT * FROM Db.dbo.TB_test請(qǐng)給出測(cè)試結(jié)果：-最后刪除所做的測(cè)試環(huán)境DROP DATABASE DbDROP TABLE #4、在現(xiàn)有數(shù)據(jù)庫(kù)上還原時(shí)的數(shù)據(jù)文件處理-創(chuàng)建一個(gè)測(cè)試數(shù)據(jù)庫(kù)CREATE DATABASE dbON(NAME=db,FILENAME='c:db.mdf')L

13、OG ON(NAME=db_log,FILENAME='c:db.ldf')-備份并刪除測(cè)試數(shù)據(jù)庫(kù)BACKUP DATABASE db TO DISK='c:a.bak' WITH FORMATDROP DATABASE db-創(chuàng)建一個(gè)文件結(jié)構(gòu)相同,但物理文件位置不同的數(shù)據(jù)庫(kù)CREATE DATABASE dbON(NAME=db,FILENAME='d:db.mdf')LOG ON(NAME=db_log,FILENAME='d:db.ldf')-在新建的數(shù)據(jù)庫(kù)上強(qiáng)制還原備份RESTORE DATABASE db FROM DI

14、SK='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.sysobjects where id = object_id(N'dbo.p_backupdb') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop proced

15、ure dbo.p_backupdbGOcreate proc p_backupdbdbname sysname='',-要備份的數(shù)據(jù)庫(kù)名稱,不指定則備份當(dāng)前數(shù)據(jù)庫(kù)bkfname nvarchar(260)='',-備份文件名,文件名中可以用DBNAME代表數(shù)據(jù)庫(kù)名,DATE代表日期,TIME代表時(shí)間bkpath nvarchar(260)='c:'-備份文件的存放目錄,在該目錄下創(chuàng)建每天備份的目錄asdeclare sql varchar(8000)if isnull(dbname,'')='' set dbna

16、me=db_name()if right(bkpath,1)<>'' set bkpath=bkpath+''set bkpath=bkpath+convert(varchar,getdate(),112)+''-創(chuàng)建目錄set sql='md '+bkpathexec master.xp_cmdshell sql,no_output-備份處理if isnull(bkfname,'')='' set bkfname='DBNAME_DATE.BAK'set bkfname

17、=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)go1

18、6.3 數(shù)據(jù)導(dǎo)入與導(dǎo)出1、導(dǎo)入/導(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 5.0;HDR=YES;DA

19、TABASE=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',sheet1$)select * from 表如果Excel文件不存在,也可以用BCP來(lái)導(dǎo)出Excel的文件(注意大小寫(xiě)):EXEC master.xp_cmdshell 'bcp 數(shù)據(jù)庫(kù)名.db

20、o.表名 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 BY au_lname" queryout "c:test.xls" /c /S"服務(wù)器名" /U"用戶名" -P"密碼"'

21、說(shuō)明:c:test.xls為導(dǎo)入/導(dǎo)出的Excel文件名.要求文件在SQL服務(wù)器或者服務(wù)器可以訪問(wèn)的共享目錄上sheet1$ 為Excel文件的工作表名,一般要加上$才能正常使用.（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,1declare tb cursor for select fn='c:test'+fname from #twhere isf=1 and fname like '%.xls' -取.