定时备份数据库的存储过程

[action201]

sql server 2000中定时备份数据库的存储过程如何做

[一者仁心]

没用过 顶 都是用job

[liangCK]

看看邹建老大的blog

[一者仁心]

CREATE PROCEDURE [dbo].[back_pubs_time] AS
declare
@filename nvarchar(100),--文件名
@NowDay int --设置时间
set @filename='F:\JXXdata'+cast(Day(GetDate()) as varchar(2))+'.dat' --文件路径及文件名
Set @NowDay=Day(GetDate())
if (@NowDay>=20) or (@NowDay<=10) --这个月的20到下个月的10要备份
begin
print @filename
BACKUP DATABASE [pubs]--数据库名你也可以设参数
TO DISK = @filename
WITH INIT , NOUNLOAD , NAME = N'XX数据备份', NOSKIP , STATS = 10, NOFORMAT
end

exec back_pubs_time

[-狙击手-]

/*--备份数据库

--邹建 2003.10--*/

/*--调用示例

--备份当前数据库
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_db.bak'

--差异备份当前数据库
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_df.bak',@bktype='DF'

--备份当前数据库日志
exec p_backupdb @bkpath='c:\',@bkfname='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='',			--要备份的数据库名称,不指定则备份当前数据库
@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 +@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



/*--创建作业

--邹建 2003.10--*/

/*--调用示例

--每月执行的作业
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()

--创建作业
exec msdb..sp_add_job @job_name=@jobname

--创建作业步骤
exec msdb..sp_add_jobstep @job_name=@jobname,
	@step_name = '数据处理',
	@subsystem = 'TSQL',
	@database_name=@dbname,
	@command = @sql,
	@retry_attempts = 5, --重试次数
	@retry_interval = 5  --重试间隔

--创建调度
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

EXEC msdb..sp_add_jobschedule @job_name=@jobname, 
	@name = '时间安排',
	@freq_type=@ftype ,		 			--每天,8 每周,16 每月
	@freq_interval=1,					--重复执行次数
	@freq_subday_type=@fstype,			--是否重复执行
	@freq_subday_interval=@fsinterval, 	--重复周期
	@freq_recurrence_factor=@ffactor,
	@active_start_time=@time  			--下午17:00:00分执行

go

/*-----------------------------------------------------------------------
知识点: 备份/恢复语句的用法,作业的创建

-------------------------------------------------------------------------*/


/*--得到数据库的文件目录
	
	@dbname 指定要取得目录的数据库名
			如果指定的数据不存在,返回安装SQL时设置的默认数据目录
			如果指定NULL,则返回默认的SQL备份目录名
--邹建 2003.10--*/

/*--调用示例
	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 name=@dbname

	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

[duanzhi1984]

以上学习.不亏是大斑竹

[中国风]

自己写一个做调度...
生成文件名取系统时间作为格式名：
如test_20080111.bak
可定义自动删除和备份。。

[JL99000]

没什么难的吧
判断时间，到了你想备份的时候BACKUP就OK了

[w2jc]

建一个作业定时备份不是更简单？
而且还有运行的历史记录...

[zhujinqiang]

来学习斑竹的经验。

[zcc3180252]

顶，不亏是大班竹，学习，受益...

[cmfl3483]

对存储过程不熟啊~！学习了