/*--調用示例 select 數據庫文件目錄=dbo.f_getdbpath('tempdb') ,[默認sql server數據目錄]=dbo.f_getdbpath('') ,[默認sql server備份目錄]=dbo.f_getdbpath(null) --*/ if exists (select * from 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 select @re=rtrim(reverse(filename)) from master..sysdatabases where name='master' else select @re=rtrim(reverse(filename)) from master..sysdatabases where [email protected]
if @dbname is null set @re=reverse(substring(@re,charindex('/',@re)+5,260))+'backup' else set @re=reverse(substring(@re,charindex('/',@re),260)) return(@re) end go
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='', --要備份的數據庫名稱,不指定則備份當前數據庫 @bkpath nvarchar(260)='', --備份文件的存放目錄,不指定則使用sql默認的備份目錄 @bkfname nvarchar(260)='', --備份文件名,文件名中可以用/dbname/代表數據庫名,/date/代表日期,/time/代表時間 @bktype nvarchar(10)='db', --備份類型:'db'備份數據庫,'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 [email protected] +' to disk='''[email protected][email protected] +''' with '+case @bktype when 'df' then 'differential,' else '' end +case @appendfile when 1 then 'noinit' else 'init' end print @sql exec(@sql) go
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
--得到恢復后的數據庫名 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)
--得到恢復后的數據庫存放目錄 if isnull(@dbpath,'')='' set @dbpath=dbo.f_getdbpath('')
--生成數據庫恢復語句 set @sql='restore '+case @retype when 'log' then 'log ' else 'database ' [email protected] +' from disk='''[email protected]+'''' +' 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
--創建臨時表,保存獲取的信息 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='''[email protected]+'''') 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 @[email protected]+',move '''[email protected]+''' to '''[email protected][email protected]+cast(@i as varchar) +case @tp when 'd' then '.mdf''' else '.ldf''' end ,@[email protected]+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 '[email protected]) fetch next from #spid into @spid end close #spid deallocate #spid end
--恢復數據庫 exec(@sql)
go
/*4.--創建作業--*/
/*--調用示例
--每月執行的作業 exec p_createjob @jobname='mm',@sql='select * from syscolumns',@freqtype='month'
--每周執行的作業 exec p_createjob @jobname='ww',@sql='select * from syscolumns',@freqtype='week'
--每日執行的作業 exec p_createjob @jobname='a',@sql='select * from syscolumns'
--每日執行的作業,每天隔4小時重復的作業 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 p_createjob @jobname varchar(100), --作業名稱 @sql varchar(8000), --要執行的命令 @dbname sysname='', --默認為當前的數據庫名 @freqtype varchar(6)='day', --時間周期,month 月,week 周,day 日 @fsinterval int=1, --相對于每日的重復次數 @time int=170000 --開始執行時間,對于重復執行的作業,將從0點到23:59分 as if isnull(@dbname,'')='' set @dbname=db_name()
--創建調度 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 @fsinterval<>1 set @time=0 set @ffactor=case @freqtype when 'day' then 0 else 1 end
--1.建立每月備份和生成月備份數據庫的作業,每月每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)[email protected]
--2.建立每周差異備份和生成周備份數據庫的作業,每周日下午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)[email protected]
--3.建立每日日志備份和生成日備份數據庫的作業,每周日下午17:15分進行: set @sql=' declare @path nvarchar(260),@fname nvarchar(100) set @fname=''produce_''+convert(varchar(10),getdate(),112)+''_l.bak'' set @path=dbo.f_getdbpath(null)[email protected]
