写一个数据维护计划！！！！

最新推荐文章于 2024-02-07 22:09:49 发布
wisdomone
最新推荐文章于 2024-02-07 22:09:49 发布
阅读量1.4k
点赞数
文章标签：作业 database command output action tsql
本文链接：https://blog.csdn.net/wisdomone/article/details/1223618
版权
源代码
/*****************************************************************************
 * 用途：截断并收缩事务日志，并且建立包含完全备份和事务日志备份的简单维护计划
 * 使用环境：Microsoft SQL Server 2000 ，并且要处理的库只包含一个日志文件
 * 注意事项： 1、使用前请仔细阅读本注意事项，并注意修改第17行和第25行的数据
 *                 库名称，第18行的事务日志预计要收缩的大小
 *            2、可以对本代码进行复制，但请注意复制时不得修改原文，复制内容
 *                 须包含所有内容
 *            3、本代码可以用于商业及其它用途，但不得以赢利为目的对本代码进
 *                 行传播，尤其是在传统媒体上刊登
 *            4、本人不对此代码可能引起的对系统的破坏或者数据的损失承担任何
 *                 责任，执行此代码意味着你愿意承担任何风险
 *            5、本人不对此代码提供任何技术支持，阅读注释并查阅Books Online，
 *                 你可以得到你所需要的全部信息
 *            6、对本代码有任何建议或修改意见，请到开发者俱乐部
 *                 *****************************************************************************/
DECLARE @DBName SYSNAME, @LogFileID SMALLINT, @TagLogSize SMALLINT
DECLARE @PlanID UNIQUEIDENTIFIER, @iResult INT
DECLARE @PlanName VARCHAR(128)
DECLARE @JobID1 BINARY(16), @JobID2 BINARY(16)
DECLARE @JobName SYSNAME, @JobCommand NVARCHAR(3200)
SET @DBName = 'devclub'
SET @TagLogSize = 256	--单位为兆字节

/*****************************************************************************
 *截断并收缩日志
******************************************************************************/

--请注意修改下一行的数据库名称
USE devclub

--截断日志
BACKUP LOG @DBName WITH NO_LOG
--得到日志文件的FileID
IF (SELECT COUNT(*) FROM sysfiles WHERE status & 0x40 = 0x40) <> 1
BEGIN
	PRINT '日志文件的数目不对'
	RETURN
END
ELSE
	SELECT @LogFileID = fileid FROM sysfiles WHERE status & 0x40 = 0x40
--收缩日志文件到指定大小
EXECUTE ('DBCC SHRINKFILE (' + @LogFileID + ', ' + @TagLogSize + ')')


USE msdb

SET @PlanName = @DBName + ' Maintenance Plan'

/*****************************************************************************
 *建立维护计划
******************************************************************************/

--建立维护计划
EXECUTE @iResult = sp_add_maintenance_plan @PlanName, @PlanID OUTPUT
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能建立维护计划'
	RETURN
END

--将数据库关联到维护计划
EXECUTE @iResult = sp_add_maintenance_plan_db @PlanID, @DBName
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能把数据库关联到维护计划'
	RETURN
END

/*****************************************************************************
 *建立完全备份作业
******************************************************************************/
SET @JobName = @PlanName + N'的完全备份作业'
/*	Command的意义
 *	备份日志到磁盘的默认备份目录下并校验，删除早于1周的备份文件，扩展名为BAK
 *	若欲修改，请仔细阅读Books Online中关于sqlmaint 实用工具的语法说明*/
SET @JobCommand = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + CONVERT(VARCHAR(40), @PlanID) + 
	'  -VrfyBackup -BkUpMedia DISK -BkUpDB  -UseDefDir  -DelBkUps 1WEEKS -BkExt "BAK"'''

-- 添加作业
EXECUTE @iResult = sp_add_job @job_id = @JobID1 OUTPUT , 
	@job_name = @JobName, 
	@owner_login_name = N'sa', 
	@description = N'No description available.', 
	@category_name = NULL, 
	@enabled = 1, 
	@notify_level_email = 0, 
	@notify_level_page = 0, 
	@notify_level_netsend = 0, 
	@notify_level_eventlog = 2, 
	@delete_level= 0
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能建立完全备份的作业'
	RETURN
END

-- 添加作业步骤
EXECUTE @iResult = sp_add_jobstep @job_id = @JobID1, 
	@step_id = 1, 
	@step_name = N'第 1 步', 
	@command = @JobCommand,
	@database_name = N'master', 
	@server = N'', 
	@database_user_name = N'', 
	@subsystem = N'TSQL', 
	@cmdexec_success_code = 0, 
	@flags = 4, 
	@retry_attempts = 0, 
	@retry_interval = 0, 
	@output_file_name = N'', 
	@on_success_step_id = 0, 
	@on_success_action = 1, 
	@on_fail_step_id = 0, 
	@on_fail_action = 2
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能建立完全备份的作业步骤'
	RETURN
END

EXECUTE @iResult = sp_update_job @job_id = @JobID1, @start_step_id = 1 
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能添加完全备份的作业步骤'
	RETURN
END

-- 添加作业调度，每天一次，2:17分开始，懒得写成变量了，如果要改自己改@active_start_time
-- 如果要改周期，请仔细阅读Books Online中sp_add_jobschedule的说明后修改@freq开头的几个参数
EXECUTE @iResult = sp_add_jobschedule @job_id = @JobID1, 
	@name = N'第 1 调度', 
	@enabled = 1, @freq_type = 4, 
	@active_start_date = 20000101, 
	@active_start_time = 21700, @freq_interval = 1, 
	@freq_subday_type = 1, @freq_subday_interval = 0, 
	@freq_relative_interval = 0, @freq_recurrence_factor = 0, 
	@active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能添加完全备份的作业调度'
	RETURN
END

-- 添加目标服务器
EXECUTE @iResult = sp_add_jobserver @job_id = @JobID1, @server_name = N'(local)' 
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能添加完全备份的目标服务器'
	RETURN
END

--把作业关联到维护计划
EXECUTE @iResult = sp_add_maintenance_plan_job @PlanID, @JobID1
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能把完全备份的作业关联到维护计划'
	RETURN
END

/****************************************************************************
 *建立事务日志备份作业
*****************************************************************************/
SET @JobName = @PlanName + N'的事务日志备份作业'
/*	Command的意义
 *	备份日志到磁盘的默认备份目录下与库同名的目录并校验，删除早于4天的备份文件，扩展名为TRN
 *	若欲修改，请仔细阅读Books Online中关于sqlmaint 实用工具的语法说明*/
SET @JobCommand = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + CONVERT(VARCHAR(40), @PlanID) + 
	' -VrfyBackup -BkUpMedia DISK -BkUpLog  -UseDefDir  -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'''

-- 添加作业
EXECUTE @iResult = sp_add_job @job_id = @JobID2 OUTPUT , 
	@job_name = @JobName, 
	@owner_login_name = N'sa', 
	@description = N'No description available.', 
	@category_name = NULL, 
	@enabled = 1, 
	@notify_level_email = 0, 
	@notify_level_page = 0, 
	@notify_level_netsend = 0, 
	@notify_level_eventlog = 2, 
	@delete_level= 0
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能建立事务日志备份的作业'
	RETURN
END

-- 添加作业步骤
EXECUTE @iResult = sp_add_jobstep @job_id = @JobID2, 
	@step_id = 1, 
	@step_name = N'第 1 步', 
	@command = @JobCommand,
	@database_name = N'master', 
	@server = N'', 
	@database_user_name = N'', 
	@subsystem = N'TSQL', 
	@cmdexec_success_code = 0, 
	@flags = 4, 
	@retry_attempts = 0, 
	@retry_interval = 0, 
	@output_file_name = N'', 
	@on_success_step_id = 0, 
	@on_success_action = 1, 
	@on_fail_step_id = 0, 
	@on_fail_action = 2
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能建立事务日志备份的作业步骤'
	RETURN
END

EXECUTE @iResult = sp_update_job @job_id = @JobID2, @start_step_id = 1 
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能添加事务日志备份的作业步骤'
	RETURN
END

-- 添加作业调度，每小时一次，32分开始，懒得写成变量了，如果要改自己改@active_start_time
-- 如果要改周期，请仔细阅读Books Online中sp_add_jobschedule的说明后修改@freq开头的几个参数
EXECUTE @iResult = sp_add_jobschedule @job_id = @JobID2, 
	@name = N'第 1 调度', @enabled = 1, @freq_type = 4, 
	@active_start_date = 20000101, @active_start_time = 3200, 
	@freq_interval = 1, @freq_subday_type = 8, 
	@freq_subday_interval = 1, @freq_relative_interval = 0, 
	@freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能添加事务日志备份的作业调度'
	RETURN
END

-- 添加目标服务器
EXECUTE @iResult = sp_add_jobserver @job_id = @JobID2, @server_name = N'(local)' 
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能添加事务日志备份的目标服务器'
	RETURN
END

--把作业关联到维护计划
EXECUTE @iResult = sp_add_maintenance_plan_job @PlanID, @JobID2
IF (@@ERROR <> 0 OR @iResult <> 0)
BEGIN
	PRINT '不能把事务日志备份的作业关联到维护计划'
	RETURN
END

PRINT '代码已成功执行，感谢您使用本代码'