--写一存储过程来备份,建一job来执行该存储过程。
1:
use A
go
create proc Test
As
BACKUP ..... go2:
EXEC sp_add_job @job_name = '作业名字'
EXEC sp_add_jobstep @job_name = '作业名字',
@step_name = '步骤名子',
@subsystem = 'TSQL',
@command = 'EXEC 库名..过程名',
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔EXEC sp_add_jobschedule @job_name = '作业名字',
@name = '作业调度名字',
@freq_type = 4, -- 每天
@freq_interval = 26, --间隔
@active_start_time = 10000 --开始时间
1:
use A
go
create proc Test
As
BACKUP ..... go2:
EXEC sp_add_job @job_name = '作业名字'
EXEC sp_add_jobstep @job_name = '作业名字',
@step_name = '步骤名子',
@subsystem = 'TSQL',
@command = 'EXEC 库名..过程名',
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔EXEC sp_add_jobschedule @job_name = '作业名字',
@name = '作业调度名字',
@freq_type = 4, -- 每天
@freq_interval = 26, --间隔
@active_start_time = 10000 --开始时间
sp_add_job
sp_add_jobschedule
sp_add_jobserver
sp_add_jobstep
DECLARE @work_name sysname
SELECT @work_name = 'ztone_dbbackup'
if EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @work_name)
EXEC sp_delete_job @job_name = @work_nameEXEC sp_add_job @job_name = @work_name
DECLARE @job_step_name sysname
DECLARE @path_name varchar(100)
SET @path_name = 'F:\Data\MyDB1.dat'
SELECT @job_step_name = 'ztone_jobstep'
EXEC sp_add_jobstep @job_name = @work_name,
@step_name = @job_step_name,
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE Helper TO DISK = @path_name',
@retry_attempts = 5,
@retry_interval = 5,
@flags=4
EXEC sp_add_jobschedule @job_name = @work_name,
@name = 'helper_backup',
@enabled = 1,
@freq_type = 4, --每天
@active_start_date = 20030921,--开始日期
@active_start_time = 130500, --开始时刻
@freq_interval = 1, --子周期
@freq_subday_type = 8, --单位是小时
@freq_subday_interval = 1, --10小时
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 140500
backup database sys to disk='c:\目录\a.bak' with init
--还原
use master
RESTORE DATABASE TestDB FROM DISK = 'c:\2002.dat'
--------------------------------------------------你这样:
EXEC sp_add_job @job_name = '作业名字'
EXEC sp_add_jobstep @job_name = '作业名字',
@step_name = '步骤名子',
@subsystem = 'TSQL',
@command = 'backup database sys to disk=''c:\目录\a.bak'' with init',
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔EXEC sp_add_jobschedule @job_name = '作业名字',
@name = '作业调度名字',
@freq_type = 4, -- 每天
@freq_interval = 26, --间隔
@active_start_time = 10000 --开始时间
backup database sys to disk='c:\目录\a.bak' with init
--还原
use master
RESTORE DATABASE TestDB FROM DISK = 'c:\2002.dat'
--------------------------------------------------你这样:
EXEC sp_add_job @job_name = '作业名字'
EXEC sp_add_jobstep @job_name = '作业名字',
@step_name = '步骤名子',
@subsystem = 'TSQL',
@command = 'backup database sys to disk=''c:\目录\a.bak'' with init',
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔EXEC sp_add_jobschedule @job_name = '作业名字',
@name = '作业调度名字',
@freq_type = 4, -- 每天
@freq_interval = 26, --间隔
@active_start_time = 10000 --开始时间
1.你可以在企业管理器种看看JOB是否产生,验证教本是否正确执行;
2.启动MSSQLSERVER AGENT 服务。
3.改变MSSQLSERVER SERVICE的启动帐户,将LOCALSYSTEM 改为一个本地的帐户,一定要有对'F:\Data\MyDB1.dat'有写的权限。very important!!