Oracle 中如何實現(xiàn)數(shù)據(jù)庫的備份和還原.doc

-每月執(zhí)行的作業(yè) exec p_createjob jobname=mm,sql=select * from syscolumns,freqtype=month -每周執(zhí)行的作業(yè) exec p_createjob jobname=ww,sql=select * from syscolumns,freqtype=week -每日執(zhí)行的作業(yè) exec p_createjob jobname=a,sql=select * from syscolumns -每日執(zhí)行的作業(yè),每天隔4小時重復的作業(yè) exec p_createjob jobname=b,sql=select * from syscolumns,fsinterval=4 if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_createjob) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure dbo.p_createjob GO create proc p_createjob jobname varchar(100), -作業(yè)名稱 sql varchar(8000), -要執(zhí)行的命令 dbname sysname=, -默認為當前的數(shù)據(jù)庫名 freqtype varchar(6)=day, -時間周期,month 月,week 周,day 日 fsinterval int=1, -相對于每日的重復次數(shù) time int=170000 -開始執(zhí)行時間,對于重復執(zhí)行的作業(yè),將從0點到23:59分 as if isnull(dbname,)= set dbname=db_name() -創(chuàng)建作業(yè) exec msdb.sp_add_job job_name=jobname -創(chuàng)建作業(yè)步驟 exec msdb.sp_add_jobstep job_name=jobname, step_name = 數(shù)據(jù)處理, subsystem = TSQL, database_name=dbname, command = sql, retry_attempts = 5, -重試次數(shù) retry_interval = 5 -重試間隔 -創(chuàng)建調度 declare ftype int,fstype int,ffactor int select ftype=case freqtype when day then 4 when week then 8 when month then 16 end ,fstype=case fsinterval when 1 then 0 else 8 end if fsinterval1 set time=0 set ffactor=case freqtype when day then 0 else 1 end EXEC msdb.sp_add_jobschedule job_name=jobname, name = 時間安排, freq_type=ftype , -每天,8 每周,16 每月 freq_interval=1, -重復執(zhí)行次數(shù) freq_subday_type=fstype, -是否重復執(zhí)行 freq_subday_interval=fsinterval, -重復周期 freq_recurrence_factor=ffactor, active_start_time=time -下午17:00:00分執(zhí)行 go 應用案例-備份方案: 完整備份(每個星期天一次)+差異備份(每天備份一次)+日志備份(每2小時備份一次) 調用上面的存儲過程來實現(xiàn) declare sql varchar(8000) -完整備份(每個星期天一次) set sql=exec p_backupdb dbname=要備份的數(shù)據(jù)庫名 exec p_createjob jobname=每周備份,sql,freqtype=week -差異備份(每天備份一次) set sql=exec p_backupdb dbname=要備份的數(shù)據(jù)庫名,bktype=DF exec p_createjob jobname=每天差異備份,sql,freqtype=day -日志備份(每2小時備份一次) set sql=exec p_backupdb dbname=要備份的數(shù)據(jù)庫名,bktype=LOG exec p_createjob jobname=每2小時日志備份,sql,freqtype=day,fsinterval=2-備份當前數(shù)據(jù)庫 exec p_backupdb bkpath=c:,bkfname=db_DATE_db.bak -差異備份當前數(shù)據(jù)庫 exec p_backupdb bkpath=c:,bkfname=db_DATE_df.bak,bktype=DF -備份當前數(shù)據(jù)庫日志 exec p_backupdb bkpath=c:,bkfname=db_DATE_log.bak,bktype=LOG if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_backupdb) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure dbo.p_backupdb GO create proc p_backupdb dbname sysname=, -要備份的數(shù)據(jù)庫名稱,不指定則備份當前數(shù)據(jù)庫 bkpath nvarchar(260)=, -備份文件的存放目錄,不指定則使用SQL默認的備份目錄 bkfname nvarchar(260)=, -備份文件名,文件名中可以用DBNAME代表數(shù)據(jù)庫名,DATE代表日期,TIME代表時間 bktype nvarchar(10)=DB, -備份類型:DB備份數(shù)據(jù)庫,DF 差異備份,LOG 日志備份 appendfile bit=1 -追加/覆蓋備份文件 as declare sql varchar(8000) if isnull(dbname,)= set dbname=db_name() if isnull(bkpath,)= set bkpath=dbo.f_getdbpath(null) if isnull(bkfname,)= set bkfname=DBNAME_DATE_TIME.BAK set bkfname=replace(replace(replace(bkfname,DBNAME,dbname) ,DATE,convert(varchar,getdate(),112) ,TIME,replace(convert(varchar,getdate(),108),:,) set sql=backup +case bktype when LOG then log else database end +dbname + to disk=+bkpath+bkfname + with +case bktype when DF then DIFFERENTIAL, else end +case appendfile when 1 then NOINIT else INIT end print sql exec(sql) go 3.-恢復數(shù)據(jù)庫 調用示例 -完整恢復數(shù)據(jù)庫 exec p_RestoreDb bkfile=c:db_20031015_db.bak,dbname=db -差異備份恢復 exec p_RestoreDb bkfile=c:db_20031015_db.bak,dbname=db,retype=DBNOR exec p_backupdb bkfile=c:db_20031015_df.bak,dbname=db,retype=DF -日志備份恢復 exec p_RestoreDb bkfile=c:db_20031015_db.bak,dbname=db,retype=DBNOR exec p_backupdb bkfile=c:db_20031015_log.bak,dbname=db,retype=LOG if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_RestoreDb) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure dbo.p_RestoreDb GO create proc p_RestoreDb bkfile nvarchar(1000), -定義要恢復的備份文件名 dbname sysname=, -定義恢復后的數(shù)據(jù)庫名,默認為備份的文件名 dbpath nvarchar(260)=, -恢復后的數(shù)據(jù)庫存放目錄,不指定則為SQL的默認數(shù)據(jù)目錄 retype nvarchar(10)=DB, -恢復類型:DB完事恢復數(shù)據(jù)庫,DBNOR 為差異恢復,日志恢復進行完整恢復,DF 差異備份的恢復,LOG 日志恢復 filenumber int=1, -恢復的文件號 overexist bit=1, -是否覆蓋已經存在的數(shù)據(jù)庫,僅retype為 killuser bit=1 -是否關閉用戶使用進程,僅overexist=1時有效 as declare sql varchar(8000) -得到恢復后的數(shù)據(jù)庫名 if isnull(dbname,)= select sql=reverse(bkfile) ,sql=case when charindex(.,sql)=0 then sql else substring(sql,charindex(.,sql)+1,1000) end ,sql=case when charindex(,sql)=0 then sql else left(sql,charindex(,sql)-1) end ,dbname=reverse(sql) -得到恢復后的數(shù)據(jù)庫存放目錄 if isnull(dbpath,)= set dbpath=dbo.f_getdbpath() -生成數(shù)據(jù)庫恢復語句 set sql=restore +case retype when LOG then log else database end+dbname + from disk=+bkfile+ + with file=+cast(filenumber as varchar) +case when overexist=1 and retype in(DB,DBNOR) then ,replace else end +case retype when DBNOR then ,NORECOVERY else ,RECOVERY end print sql -添加移動邏輯文件的處理 if retype=DB or retype=DBNOR begin -從備份文件中獲取邏輯文件名 declare lfn nvarchar(128),tp char(1),i int -創(chuàng)建臨時表,保存獲取的信息 create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0) -從備份文件中獲取信息 insert into #tb exec(restore filelistonly from disk=+bkfile+) declare #f cursor for select ln,tp from #tb open #f fetch next from #f into lfn,tp set i=0 while fetch_status=0 begin select sql=sql+,move +lfn+ to +dbpath+dbname+cast(i as varchar) +case tp when D then .mdf else .ldf end ,i=i+1 fetch next from #f into lfn,tp end close #f deallocate #f end -關閉用戶進程處理 if overexist=1 and killuser=1 begin declare spid varchar(20) declare #spid cursor for select spid=cast(spid as varchar(20) from master.sysprocesses where dbid=db_id(dbname) open #spid fetch next from #spid into spid while fetch_status=0 begin exec(kill +spid) fetch next from #spid into spid end close #spid deallocate #spid end -恢復數(shù)據(jù)庫 exec(sql) go 應用案例2 生產數(shù)據(jù)核心庫：PRODUCE 備份方案如下： 1.設置三個作業(yè),分別對PRODUCE庫進行每日備份,每周備份,每月備份 2.新建三個新庫,分別命名為:每日備份,每周備份,每月備份 3.建立三個作業(yè)，分別把三個備份庫還原到以上的三個新庫。 目的:當用戶在produce庫中有任何的數(shù)據(jù)丟失時,均可以從上面的三個備份庫中導入相應的TABLE數(shù)據(jù)。 declare sql varchar(8000) -1.建立每月備份和生成月備份數(shù)據(jù)庫的作業(yè),每月每1天下午16:40分進行: set sql= declare path nvarchar(260),fname nvarchar(100) set fname=PRODUCE_+convert(varchar(10),getdate(),112)+_m.bak set path=dbo.f_getdbpath(null)+fname -備份 exec p_backupdb dbname=PRODUCE,bkfname=fname -根據(jù)備份生成每月新庫 exec p_RestoreDb bkfile=path,dbname=PRODUCE_月 -為周數(shù)據(jù)庫恢復準備基礎數(shù)據(jù)庫 exec p_RestoreDb bkfile=path,dbname=PRODUCE_周,retype=DBNOR -為日數(shù)據(jù)庫恢復準備基礎數(shù)據(jù)庫 exec p_RestoreDb bkfile=path,dbname=PRODUCE_日,retype=DBNOR exec p_createjob jobname=每月備份,sql,freqtype=month,time=164000 -2.建立每周差異備份和生成周備份數(shù)據(jù)庫的作業(yè),每周日下午17:00分進行: set sql= declare path nvarchar(260),fname nvarchar(100) set fname=PRODUCE_+convert(varchar(10),getdate(),112)+_w.bak set path=dbo.f_getdbpath(null)+fname -差異備份 exec p_backupdb dbname=PRODUCE,bkfname=fname,bktype=DF -差異恢復周數(shù)據(jù)庫 exec p_backupdb bkfile=path,dbname=PRODUCE_周,retype=DF exec p_createjob jobname=每周差異備份,sql,freqtype=week,t