-- =================================
-- Author:<Maco Wang>
-- CreateDate:2009-03-11 10:34:05
-- Description:<数据库定时热备份>
-- =================================
use msdb
create procedure hot_backup
as
begin
declare @sql varchar(200)
set @sql='Maco'+convert(varchar(20),getdate(),120)+'.bak'
set @sql=replace(@sql,'-','');
set @sql=replace(@sql,' ','');
set @sql=replace(@sql,':','');
select @sql
backup database maco to disk=@sql;
end
go
use msdb
exec sp_add_job @job_name = 'Data Backup',
@enabled = 1,
@description = 'Ad hoc backup of sales data',
@owner_login_name = 'sa',
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@delete_level = 0 --不删除
go
use msdb
exec sp_add_jobstep @job_name = 'Data Backup',
@step_name = 'Start To Data Backup',
@subsystem = 'tsql',
@command = 'exec hot_backup',
@retry_attempts = 5,
@retry_interval = 5
go
--每天下午1:00 执行。
use msdb
exec sp_add_jobschedule @job_name = 'Data Backup',
@name = 'ScheduledBackup',
@freq_type = 4, -- 天
@freq_interval = 1,
@active_start_date = 20090309, --开始日期
@active_start_time = 130000 --开始时间
go
use msdb
exec sp_add_jobserver @job_name = 'Data Backup',
@server_name = 'maco_wang' --数据库服务器名称
go
--EXEC sp_delete_job @job_name = 'Data Backup'