T-SQL語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)庫(kù)備份與還原

1、T-SQL語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)庫(kù)備份與還原 2007-12-04 09:43 來(lái)源：博客網(wǎng) 【簡(jiǎn) 介】這種方式感覺(jué)實(shí)現(xiàn)起來(lái)也不錯(cuò)，我是使用這種方式來(lái)完成數(shù)據(jù)庫(kù)備份還原的功能的。需要指出下面這些存儲(chǔ)過(guò)程是在網(wǎng)上搜索到的。謝謝代碼的提供者。在此轉(zhuǎn)載 加入收藏 設(shè)為首頁(yè) 這種方式感覺(jué)實(shí)現(xiàn)起來(lái)也不錯(cuò)，我是使用這種方式來(lái)完成數(shù)據(jù)庫(kù)備份還原的功能的。 需要指出下面這些存儲(chǔ)過(guò)程是在網(wǎng)上搜索到的。謝謝代碼的提供者。在此轉(zhuǎn)載 利用T-SQL語(yǔ)句,實(shí)現(xiàn)數(shù)據(jù)庫(kù)的備份與還原的功能 體現(xiàn)了SQL Server中的四個(gè)知識(shí)點(diǎn)： 1 獲取SQL Server服務(wù)器上的默認(rèn)目錄 2 備份SQL語(yǔ)句的使用 3 恢復(fù)SQL語(yǔ)句的使用，同

2、時(shí)考慮了強(qiáng)制恢復(fù)時(shí)關(guān)閉其他用戶進(jìn)程的處理 4 作業(yè)創(chuàng)建SQL語(yǔ)句的使用 /*1.-得到數(shù)據(jù)庫(kù)的文件目錄 dbname 指定要取得目錄的數(shù)據(jù)庫(kù)名 如果指定的數(shù)據(jù)不存在,返回安裝SQL時(shí)設(shè)置的默認(rèn)數(shù)據(jù)目錄 如果指定NULL,則返回默認(rèn)的SQL備份目錄名 */ /*-調(diào)用示例 select 數(shù)據(jù)庫(kù)文件目錄=dbo.f_getdbpath('tempdb') ,默認(rèn)SQL SERVER數(shù)據(jù)目錄=dbo.f_getdbpath('') ,默認(rèn)SQL SERVER備份目錄=dbo.f_getdbpath(null) -*/ if exists (select * from

3、dbo.sysobjects where id = object_id(N'dbo.f_getdbpath') and xtype in (N'FN', N'IF', N'TF') drop function dbo.f_getdbpath GO create function f_getdbpath(dbname sysname) returns nvarchar(260) as begin declare re nvarchar(260) if dbname is null or db_id(dbname) is null s

4、elect re=rtrim(reverse(filename) from master.sysdatabases where name='master' else select re=rtrim(reverse(filename) from master.sysdatabases where name=dbname if dbname is null set re=reverse(substring(re,charindex('',re)+5,260)+'BACKUP' else set re=reverse(substring(re,char

5、index('',re),260) return(re) end go /*2.-備份數(shù)據(jù)庫(kù) */ /*-調(diào)用示例 -備份當(dāng)前數(shù)據(jù)庫(kù) exec p_backupdb bkpath='c:',bkfname='db_DATE_db.bak' -差異備份當(dāng)前數(shù)據(jù)庫(kù) exec p_backupdb bkpath='c:',bkfname='db_DATE_df.bak',bktype='DF' -備份當(dāng)前數(shù)據(jù)庫(kù)日志 exec p_backupdb bkpath='c:',bkfname=&

6、#39;db_DATE_log.bak',bktype='LOG' -*/ if exists (select * from dbo.sysobjects where id = object_id(N'dbo.p_backupdb') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.p_backupdb GO create proc p_backupdb dbname sysname='', -要備份的數(shù)據(jù)庫(kù)名稱,不指定則備份當(dāng)前數(shù)據(jù)庫(kù) bkpa

7、th nvarchar(260)='', -備份文件的存放目錄,不指定則使用SQL默認(rèn)的備份目錄 bkfname nvarchar(260)='', -備份文件名,文件名中可以用DBNAME代表數(shù)據(jù)庫(kù)名,DATE代表日期,TIME代表時(shí)間 bktype nvarchar(10)='DB', -備份類(lèi)型:'DB'備份數(shù)據(jù)庫(kù),'DF' 差異備份,'LOG' 日志備份 appendfile bit=1 -追加/覆蓋備份文件 as declare sql varchar(8000) if isnull(db

8、name,'')='' 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(v

9、archar,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

10、 when 'DF' then 'DIFFERENTIAL,' else '' end +case appendfile when 1 then 'NOINIT' else 'INIT' end print sql exec(sql) go /*3.-恢復(fù)數(shù)據(jù)庫(kù) */ /*-調(diào)用示例 -完整恢復(fù)數(shù)據(jù)庫(kù) exec p_RestoreDb bkfile='c:db_20031015_db.bak',dbname='db' -差異備份恢復(fù) exec p_RestoreDb bkfile=&

11、#39;c:db_20031015_db.bak',dbname='db',retype='DBNOR' exec p_backupdb bkfile='c:db_20031015_df.bak',dbname='db',retype='DF' -日志備份恢復(fù) exec p_RestoreDb bkfile='c:db_20031015_db.bak',dbname='db',retype='DBNOR' exec p_backupdb bkfile='

12、;c:db_20031015_log.bak',dbname='db',retype='LOG' -*/ if exists (select * from dbo.sysobjects where id = object_id(N'dbo.p_RestoreDb') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.p_RestoreDb GO create proc p_RestoreDb bkfile nvarchar(1000), -定義要恢

13、復(fù)的備份文件名 dbname sysname='', -定義恢復(fù)后的數(shù)據(jù)庫(kù)名,默認(rèn)為備份的文件名 dbpath nvarchar(260)='', -恢復(fù)后的數(shù)據(jù)庫(kù)存放目錄,不指定則為SQL的默認(rèn)數(shù)據(jù)目錄 retype nvarchar(10)='DB', -恢復(fù)類(lèi)型:'DB'完事恢復(fù)數(shù)據(jù)庫(kù),'DBNOR' 為差異恢復(fù),日志恢復(fù)進(jìn)行完整恢復(fù),'DF' 差異備份的恢復(fù),'LOG' 日志恢復(fù) filenumber int=1, -恢復(fù)的文件號(hào) overexist bit=1, -是否覆蓋

14、已經(jīng)存在的數(shù)據(jù)庫(kù),僅retype為 killuser bit=1 -是否關(guān)閉用戶使用進(jìn)程,僅overexist=1時(shí)有效 as declare sql varchar(8000) -得到恢復(fù)后的數(shù)據(jù)庫(kù)名 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 chari

15、ndex('',sql)=0 then sql else left(sql,charindex('',sql)-1) end ,dbname=reverse(sql) -得到恢復(fù)后的數(shù)據(jù)庫(kù)存放目錄 if isnull(dbpath,'')='' set dbpath=dbo.f_getdbpath('') -生成數(shù)據(jù)庫(kù)恢復(fù)語(yǔ)句 set sql='restore '+case retype when 'LOG' then 'log ' else 'databas

16、e ' 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

17、9; else ',RECOVERY' end print sql -添加移動(dòng)邏輯文件的處理 if retype='DB' or retype='DBNOR' begin -從備份文件中獲取邏輯文件名 declare lfn nvarchar(128),tp char(1),i int -創(chuàng)建臨時(shí)表,保存獲取的信息 create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0) -從

18、備份文件中獲取信息 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 '

19、''+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 -關(guān)閉用戶進(jìn)程處理 if overexist=1 and killuser=1 begin declare spid varchar(20) declare #spid cursor for s

20、elect 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 -恢復(fù)數(shù)據(jù)庫(kù) exec(sql) go /*4.-創(chuàng)建作業(yè) */ /*-調(diào)用示例 -每月執(zhí)行的作

21、業(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' -每日?qǐng)?zhí)行的作業(yè) exec p_createjob jobname='a',sql='select * from syscolumns' -

22、每日?qǐng)?zhí)行的作業(yè),每天隔4小時(shí)重復(fù)的作業(yè) exec p_createjob jobname='b',sql='select * from syscolumns',fsinterval=4 -*/ if exists (select * from dbo.sysobjects where id = object_id(N'dbo.p_createjob') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.p_createjob GO create proc

23、p_createjob jobname varchar(100), -作業(yè)名稱 sql varchar(8000), -要執(zhí)行的命令 dbname sysname='', -默認(rèn)為當(dāng)前的數(shù)據(jù)庫(kù)名 freqtype varchar(6)='day', -時(shí)間周期,month 月,week 周,day 日 fsinterval int=1, -相對(duì)于每日的重復(fù)次數(shù) time int=170000 -開(kāi)始執(zhí)行時(shí)間,對(duì)于重復(fù)執(zhí)行的作業(yè),將從0點(diǎn)到23:59分 as if isnull(dbname,'')='' set dbname=db

24、_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)建調(diào)度 declare ftype int,fstype int,ffactor int select

25、 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 fsinterval<>1 set time=0 set ffactor=case freqtype when 'day' then 0 else 1 end EXEC msdb.sp_add_jobschedule job_name=jobname,

26、name = '時(shí)間安排', freq_type=ftype , -每天,8 每周,16 每月 freq_interval=1, -重復(fù)執(zhí)行次數(shù) freq_subday_type=fstype, -是否重復(fù)執(zhí)行 freq_subday_interval=fsinterval, -重復(fù)周期 freq_recurrence_factor=ffactor, active_start_time=time -下午17:00:00分執(zhí)行 go /*-應(yīng)用案例-備份方案: 完整備份（每個(gè)星期天一次）+差異備份（每天備份一次）+日志備份（每2小時(shí)備份一次） 調(diào)用上面的存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn) -*/ d

27、eclare sql varchar(8000) -完整備份（每個(gè)星期天一次） set sql='exec p_backupdb dbname=''要備份的數(shù)據(jù)庫(kù)名''' exec p_createjob jobname='每周備份',sql,freqtype='week' -差異備份（每天備份一次） set sql='exec p_backupdb dbname=''要備份的數(shù)據(jù)庫(kù)名'',bktype='DF'' exec p_createjob j

28、obname='每天差異備份',sql,freqtype='day' -日志備份（每2小時(shí)備份一次） set sql='exec p_backupdb dbname=''要備份的數(shù)據(jù)庫(kù)名'',bktype='LOG'' exec p_createjob jobname='每2小時(shí)日志備份',sql,freqtype='day',fsinterval=2 /*-應(yīng)用案例2 生產(chǎn)數(shù)據(jù)核心庫(kù)：PRODUCE 備份方案如下： 1.設(shè)置三個(gè)作業(yè),分別對(duì)PRODUCE庫(kù)進(jìn)行每日備

29、份,每周備份,每月備份 2.新建三個(gè)新庫(kù),分別命名為:每日備份,每周備份,每月備份 3.建立三個(gè)作業(yè)，分別把三個(gè)備份庫(kù)還原到以上的三個(gè)新庫(kù)。 目的:當(dāng)用戶在produce庫(kù)中有任何的數(shù)據(jù)丟失時(shí),均可以從上面的三個(gè)備份庫(kù)中導(dǎo)入相應(yīng)的TABLE數(shù)據(jù)。 -*/ declare sql varchar(8000) -1.建立每月備份和生成月備份數(shù)據(jù)庫(kù)的作業(yè),每月每1天下午16:40分進(jìn)行: set sql=' declare path nvarchar(260),fname nvarchar(100) set fname=''PRODUCE_''+convert

30、(varchar(10),getdate(),112)+''_m.bak'' set path=dbo.f_getdbpath(null)+fname -備份 exec p_backupdb dbname=''PRODUCE'',bkfname=fname -根據(jù)備份生成每月新庫(kù) exec p_RestoreDb bkfile=path,dbname=''PRODUCE_月'' -為周數(shù)據(jù)庫(kù)恢復(fù)準(zhǔn)備基礎(chǔ)數(shù)據(jù)庫(kù) exec p_RestoreDb bkfile=path,dbname=''PRODUCE_周'',retype=''DBNOR'' -為日數(shù)據(jù)庫(kù)恢復(fù)準(zhǔn)備基礎(chǔ)數(shù)據(jù)庫(kù) exec p_RestoreDb bkfile=path,dbname=''PRODUCE_日'',retype=''DBNOR'' ' exec p_createjob jobname='每月備份',sql,freqtype='month',time=164000 -2.建立每周差異備份和生成周備份數(shù)據(jù)庫(kù)的作業(yè),每周日下午17: