对数据库进行定时热备份 --【叶子】

-- =================================

-- 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'