/*--调用示例 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]
