/*--调用示例 select 数据库文件目录=dbo.f_getdbpath('tempdb') ,[默认SQL SERVER数据目录]=dbo.f_getdbpath('') ,[默认SQL SERVER备份目录]=dbo.f_getdbpath(null) --*/ ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[f_getdbpath]') and xtype in (N'FN', N'IF', N'TF')) dropfunction[dbo].[f_getdbpath] GO
createfunction f_getdbpath(@dbname sysname) returnsnvarchar(260) as begin declare@renvarchar(260) if@dbnameisnullordb_id(@dbname) isnull select@re=rtrim(reverse(filename)) from master..sysdatabases where name='master' else select@re=rtrim(reverse(filename)) from master..sysdatabases where name=@dbname
if@dbnameisnull set@re=reverse(substring(@re,charindex('\',@re)+5,260))+'BACKUP' else set@re=reverse(substring(@re,charindex('\',@re),260)) return(@re) end go
ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_backupdb]') andOBJECTPROPERTY(id, N'IsProcedure') =1) dropprocedure[dbo].[p_backupdb] GO
createproc p_backupdb @dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库 @bkpathnvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录 @bkfnamenvarchar(260)='', --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间 @bktypenvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份 @appendfilebit=1--追加/覆盖备份文件 as declare@sqlvarchar(8000) ifisnull(@dbname,'')=''set@dbname=db_name() ifisnull(@bkpath,'')=''set@bkpath=dbo.f_getdbpath(null) ifisnull(@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@bktypewhen'LOG'then'log 'else'database 'end+@dbname +' to disk='''+@bkpath+@bkfname +''' with '+case@bktypewhen'DF'then'DIFFERENTIAL,'else''end +case@appendfilewhen1then'NOINIT'else'INIT'end print@sql exec(@sql) go
ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_RestoreDb]') andOBJECTPROPERTY(id, N'IsProcedure') =1) dropprocedure[dbo].[p_RestoreDb] GO
--得到恢复后的数据库名 ifisnull(@dbname,'')='' select@sql=reverse(@bkfile) ,@sql=casewhencharindex('.',@sql)=0then@sql elsesubstring(@sql,charindex('.',@sql)+1,1000) end ,@sql=casewhencharindex('\',@sql)=0then@sql elseleft(@sql,charindex('\',@sql)-1) end ,@dbname=reverse(@sql)
--生成数据库恢复语句 set@sql='restore '+case@retypewhen'LOG'then'log 'else'database 'end+@dbname +' from disk='''+@bkfile+'''' +' with file='+cast(@filenumberasvarchar) +casewhen@overexist=1and@retypein('DB','DBNOR') then',replace'else''end +case@retypewhen'DBNOR'then',NORECOVERY'else',RECOVERY'end print@sql --添加移动逻辑文件的处理 if@retype='DB'or@retype='DBNOR' begin --从备份文件中获取逻辑文件名 declare@lfnnvarchar(128),@tpchar(1),@iint
--创建临时表,保存获取的信息 createtable #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0)) --从备份文件中获取信息 insertinto #tb exec('restore filelistonly from disk='''+@bkfile+'''') declare #f cursorforselect ln,tp from #tb open #f fetchnextfrom #f into@lfn,@tp set@i=0 while@@fetch_status=0 begin select@sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast(@iasvarchar) +case@tpwhen'D'then'.mdf'''else'.ldf'''end ,@i=@i+1 fetchnextfrom #f into@lfn,@tp end close #f deallocate #f end
--关闭用户进程处理 if@overexist=1and@killuser=1 begin declare@spidvarchar(20) declare #spid cursorfor select spid=cast(spid asvarchar(20)) from master..sysprocesses where dbid=db_id(@dbname) open #spid fetchnextfrom #spid into@spid while@@fetch_status=0 begin exec('kill '+@spid) fetchnextfrom #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
--*/ ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_createjob]') andOBJECTPROPERTY(id, N'IsProcedure') =1) dropprocedure[dbo].[p_createjob] GO
--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)+@fname
--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)+@fname
--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)+@fname