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

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

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