如何采用脚本写这样一个数据库备份 创建一个维护计划,将sql语句导出不就行了。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 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'数据库维护计划1', 1000, N'', 0) SELECT @PlanID BEGIN TRANSACTION DECLARE @ReturnCode INT 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) EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID4 OUTPUT, @job_name = N'DB 维护计划“数据库维护计划1”的 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 37E8173A-5503-491C-A555-0FB3BE81C5D6 -VrfyBackup -BkUpMedia DISK -BkUpDB "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP" -DelBkUps 5HOURS -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 = 4, @active_start_date = 20030515, @active_start_time = 20000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @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 EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID0 OUTPUT, @job_name = N'DB 维护计划“数据库维护计划1”的优化作业。', @enabled = 1, @category_id = 3 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID0, @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 37E8173A-5503-491C-A555-0FB3BE81C5D6 -RmUnusedSpace 50 10 ''', @flags = 4 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID0, @name = N'第 1 调度', @enabled = 1, @freq_type = 4, @active_start_date = 20030515, @active_start_time = 10000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @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 = @JobID0, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback SELECT @JobID0, @JobID1, @JobID2, @JobID3, @JobID4, @JobID5, @JobID6, @JobID7COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: 编写好SQL程序,建立一个JOB,设置每隔一个小时执行一次这段SQL程序; 用维护计维DTS不好吗?你如果非要sql也可以选建一个功能一样的维护计划,然后把那个计划生成sql脚本做成一个proc 然后由作业调度?我也晕了....... 首先感谢各位的发言!1. 做这个操作一定要用脚本。(被限制住了,我也没办法)2. 对于“ firetoucher(蹈火者) ”的想法,我也试过,是一个办法。3. 我现在的方法是建立一个作业,采用“xp_sqlmaint”这个系统存储过程,也是一个办法。还有其它的方法吗? 向DateTime字段中插入数据失败 sql server能对账户对数据权限限制不?如果能,求方法 安装完SQL7.0时报错~~~ 一个sql问题 分组统计 如何修正这个通用分页任意字段排序存储过程的BUG Hr考勤计算问题-----急急急 sql server 数据库问题1个 返回值问题, 在win98,95上使用ms sql 7是否一定要有网卡? 请问用sql语句怎么得到一张表的字段名字?急,请帮忙! 有个问题请大家帮帮忙
SELECT @PlanID = NEWID()
INSERT msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows)
VALUES (@PlanID, N'数据库维护计划1', 1000, N'', 0)
SELECT @PlanID BEGIN TRANSACTION
DECLARE @ReturnCode INT
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)
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID4 OUTPUT, @job_name = N'DB 维护计划“数据库维护计划1”的 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 37E8173A-5503-491C-A555-0FB3BE81C5D6 -VrfyBackup -BkUpMedia DISK -BkUpDB "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP" -DelBkUps 5HOURS -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 = 4, @active_start_date = 20030515, @active_start_time = 20000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @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
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID0 OUTPUT, @job_name = N'DB 维护计划“数据库维护计划1”的优化作业。', @enabled = 1, @category_id = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID0, @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 37E8173A-5503-491C-A555-0FB3BE81C5D6 -RmUnusedSpace 50 10 ''', @flags = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID0, @name = N'第 1 调度', @enabled = 1, @freq_type = 4, @active_start_date = 20030515, @active_start_time = 10000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @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 = @JobID0, @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:
建立一个JOB,
设置每隔一个小时执行一次这段SQL程序;
你如果非要sql也可以选建一个功能一样的维护计划,然后把那个计划生成sql脚本
做成一个proc 然后由作业调度?
我也晕了.......
1. 做这个操作一定要用脚本。(被限制住了,我也没办法)
2. 对于“ firetoucher(蹈火者) ”的想法,我也试过,是一个办法。
3. 我现在的方法是建立一个作业,采用“xp_sqlmaint”这个系统存储过程,也是一个办法。
还有其它的方法吗?