如何通过程序让Sql Server2000的数据库实现定时自动备份？ -- by 萧月禾(300分)

SQL Server有DMO可用的，再不行可以利用 Namespace 把企业管理器的界面嵌入
让用户使用就可以了，用户就会以为是你程序里的东西了。

我希望是直接通过程序操作sql server，实现定时备份的操作
应该可以用sql的命令来设置罢？
比如已经可以通过backup等语句实现对数据库的手工备份和还原
那定时呢。。。。

萧月禾
用YZHSHI说的方法
有用，
昨晚我试了

yzhshi说的确实是一个办法，只是好象比较复杂？
打开“事件探察器”，然后在“企业管理器”里新建一个定时备份的任务
再看“事件探察器”执行的东西，找出规律，再以这样的格式自己生成并执行
应该可行

天真: 能不能把你的执行语句贴出来看看？

跟踪得到如下语句，但在我机上不能再次执行了，可能是由于唯一性约束的原因
你看看在你那边能不能执行后生成一个维护计划出来，如果可以，那这个思路是没有问题的：
应该也可以简化一下它：
use [COMPANY]
go
SELECT DATABASEPROPERTYEX(N'COMPANY', N'RECOVERY')
go
use [master]
go
EXECUTE master.dbo.xp_get_tape_devices
go
set noexec off set parseonly off
go
xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE/Microsoft/MSSQLServer/MSSQLServer', N'BackupDirectory'
go
EXECUTE master.dbo.xp_fileexist N'X:/BACKUP'
go
set noexec off set parseonly off
go
DECLARE @retval nvarchar(255) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE/Microsoft/MSSQLServer/SQLServerAgent', N'ErrorLogFile', @param = @retval OUTPUT SELECT @retval
go
set noexec off set parseonly off
go
SELECT plan_name FROM msdb.dbo.sysdbmaintplans WHERE plan_name LIKE N'数据库维护计划%'
go
set noexec off set parseonly off
go
SELECT plan_name FROM msdb.dbo.sysdbmaintplans WHERE plan_name = N'我的维护计划'
go
set noexec off set parseonly off
go
DECLARE @PlanID nchar(36)
SELECT @PlanID = NEWID()
INSERT msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows)
VALUES (@PlanID, N'我的维护计划', 1000, N'', 0)
SELECT @PlanID

go
set noexec off set parseonly off
go
DECLARE @PlanID char(36)
SELECT @PlanID = '146395E2-5C7C-484C-B69B-5E53B37E54E8'

SELECT plan_name
FROM msdb.dbo.sysdbmaintplans
WHERE plan_id = @PlanID

SELECT database_name
FROM msdb.dbo.sysdbmaintplan_databases
WHERE plan_id = @PlanID

SELECT sj.command
FROM msdb.dbo.sysjobsteps sj,
msdb.dbo.sysdbmaintplan_jobs sp
WHERE sp.plan_id = @PlanID AND
sj.job_id = sp.job_id

SELECT DISTINCT sj.server_id, st.server_name
FROM msdb.dbo.sysdbmaintplan_jobs sp,
msdb.dbo.sysjobservers sj
LEFT OUTER JOIN
msdb.dbo.systargetservers st
ON sj.server_id = st.server_id
WHERE sp.plan_id = @PlanID AND
sj.job_id = sp.job_id

go
set noexec off set parseonly off
go
BEGIN TRANSACTION
DECLARE @ReturnCode INT
DECLARE @PlanID nchar(36)
DECLARE @JobID0 nchar(36)
DECLARE @JobID1 nchar(36)
DECLARE @JobID2 nchar(36)
DECLARE @JobID3 nchar(36)
DECLARE @JobID4 nchar(36)
DECLARE @JobID5 nchar(36)
DECLARE @JobID6 nchar(36)
DECLARE @JobID7 nchar(36)
DECLARE @JobIDD nchar(36)
SELECT @PlanID = N'146395E2-5C7C-484C-B69B-5E53B37E54E8'
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID4 OUTPUT, @job_name = N'DB 维护计划“我的维护计划”的 DB 备份作业。', @enabled = 1, @category_id = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID4, @step_id = 1, @step_name = N'第 1 步', @subsystem = N'TSQL', @on_success_action = 1, @on_fail_action = 2, @command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID 146395E2-5C7C-484C-B69B-5E53B37E54E8 -VrfyBackup -BkUpMedia DISK -BkUpDB "X:/BACKUP" -CrBkSubDir -BkExt "BAK"''', @flags = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID4, @name = N'第 1 调度', @enabled = 1, @freq_type = 8, @active_start_date = 0, @active_start_time = 20000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID4, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SELECT @JobID0, @JobID1, @JobID2, @JobID3, @JobID4, @JobID5, @JobID6, @JobID7
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

go
set noexec off set parseonly off
go
DECLARE @PlanID nchar(36)
SELECT @PlanID = N'146395E2-5C7C-484C-B69B-5E53B37E54E8'
DECLARE @ReturnCode INT
BEGIN TRANSACTION
IF (SELECT COUNT(*) FROM msdb.dbo.sysdbmaintplan_databases WHERE plan_id = @PlanID AND database_name = N'COMPANY') < 1
INSERT msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) VALUES (@PlanID, N'COMPANY')
DELETE FROM msdb.dbo.sysdbmaintplan_jobs WHERE plan_id = @PlanID
INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, N'59270252-EA2E-4428-80B7-A7CD66C70E81')
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

go
set noexec off set parseonly off
go
exec msdb..sp_help_job
go
DECLARE @PlanID char(36)
SELECT @PlanID = '146395E2-5C7C-484C-B69B-5E53B37E54E8'

SELECT plan_name
FROM msdb.dbo.sysdbmaintplans
WHERE plan_id = @PlanID

SELECT database_name
FROM msdb.dbo.sysdbmaintplan_databases
WHERE plan_id = @PlanID

SELECT sj.command
FROM msdb.dbo.sysjobsteps sj,
msdb.dbo.sysdbmaintplan_jobs sp
WHERE sp.plan_id = @PlanID AND
sj.job_id = sp.job_id

SELECT DISTINCT sj.server_id, st.server_name
FROM msdb.dbo.sysdbmaintplan_jobs sp,
msdb.dbo.sysjobservers sj
LEFT OUTER JOIN
msdb.dbo.systargetservers st
ON sj.server_id = st.server_id
WHERE sp.plan_id = @PlanID AND
sj.job_id = sp.job_id

go
set noexec off set parseonly off
go

按 windbell 的方法，查询帮助
通过操作作业的方法是可以做到的

就简单的就是先建立一个定时备份的作业
然后通过程序调用sp_update_job来修改相关的参数以进行设置

不过因为没做过，所以有点麻烦，我再试试

大富翁的订阅邮件有问题，up一下订个邮件！

我想应该用当前时间与设定时间对比触发备份就OK了啊，我的经销存盘存就是这样作的

多人接受答案了。