你的位置:首页 > 数据库

[数据库]数据库的运维策略脚本篇(内附脚本,无私分享)


  数据库运维中盛传一个小段子,我误删除了数据库,改怎么办?有备份还原备份,没有备份就准备简历!听起来有趣但发生在谁身上,谁都笑不起来。接触了很多的客户发现90%客户的运维策略都不是很完善。本篇就分享一些常规的运维脚本,本篇没有涉及到的或不足的也请大家留言无私贡献深藏多年的脚本,谢谢!

邮件

  邮件主要用来监控作业是否运行成功,如果您已经配置了类似zabbix等软件请忽略。

配置邮件服务

  

--SQL Server 并没有内置邮件服务器(Mail Server),它跟我们发送邮件一样,需要用户名和密码通过 SMTP(Simple Message Transfer Protocol)去连接邮件服务器。我们想让 SQL Server 来发送邮件,首先要告诉它用户名称,密码,服务器地址,网络传送协议,邮件服务器的端口。。。等信息。--  以下脚本实现了数据库邮件的配置:----下面是具体的配置邮件步骤 ----在 sa 系统帐户下运行。 ----1. 启用 SQL Server 邮件功能。 use mastergoexec sp_configure 'show advanced options',1 go reconfigure with overridegoexec sp_configure 'Database Mail XPs',1goreconfigure with overridego--2. 在 SQL Server 中添加邮件帐户(account) exec msdb..sysmail_add_account_sp    @account_name      = '163yx'   -- 邮件帐户名称(SQL Server 使用)    ,@email_address      = 'kk_XXXX@163.com' -- 发件人邮件地址    ,@display_name      = null           -- 发件人姓名    ,@replyto_address     = null    ,@description       = null    ,@mailserver_name     = 'smtp.163.com'      -- 邮件服务器地址    ,@mailserver_type     = 'SMTP'          -- 邮件协议(SQL 2005 只支持 SMTP)    ,@port          = 25            -- 邮件服务器端口    ,@username        = 'kk_XXXX@163.com'   -- 用户名    ,@password        = 'XXXXX'   -- 密码    ,@use_default_credentials = 0    ,@enable_ssl       = 0    ,@account_id       = null--3. 在 SQL Server 中添加 profile exec msdb..sysmail_add_profile_sp @profile_name = 'dba_profile3'   -- profile 名称                  ,@description = 'dba mail profile' -- profile 描述                  ,@profile_id  = null-- 在 SQL Server 中映射 account 和 profile exec msdb..sysmail_add_profileaccount_sp @profile_name  = 'dba_profile3' -- profile 名称                      ,@account_name  = '163yx'   -- account 名称                      ,@sequence_number = 1       -- account 在 profile 中顺序 --5. 利用 SQL Server Database Mail 功能发送邮件。 exec msdb..sp_send_dbmail @profile_name = 'dba_profile3'   -- profile 名称              ,@recipients  = 'kk_XXXX@163.com;kk2_XXXX@163.com' -- 收件人邮箱              ,@subject   = 'SQL Server Mail 测试' -- 邮件标题              ,@body     = 'Hello Mail!测试'  -- 邮件内容              ,@body_format = 'TEXT'   -- 邮件格式       ,@file_attachments = 'c:\a.txt' --邮件附件--6. 查看邮件发送情况: use msdbgoselect * from sysmail_allitemsselect * from sysmail_mailitemsselect * from sysmail_event_log --如果不是以 sa 帐户发送邮件,则可能会出现错误: ----Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1--EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.----这是因为,当前 SQL Server 登陆帐户(login),在 msdb 数据库中没有发送数据库邮件的权限,需要加入 msdb 数据库用户,并通过加入 sp_addrolemember 角色赋予权限。假设该SQL Server 登陆帐户名字为 “dba” ----use msdb--go----create user dba for login dba--go----exec dbo.sp_addrolemember @rolename  = 'DatabaseMailUserRole',--             @membername = 'dba'--go----此时,再次发送数据库邮件,仍可能有错误: ----Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119--profile name is not valid----虽然,数据库用户 “dba” 已经在 msdb 中拥有发送邮件的权限了,但这还不够,他还需要有使用 profile:“dba_profile” 的权限。 ----use msdb--go----exec sysmail_add_principalprofile_sp @principal_name = 'dba'--                   ,@profile_name  = 'dba_profile'--                   ,@is_default   = 1----从上面的参数 @is_default=1 可以看出,一个数据库用户可以在多个 mail profile 拥有发送权限。--EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize', 100000000 (字节)设置邮件.note

 

 

配置操作员

  操作员主要是用于作业的通知对象:

  

  

  配置如下:

  

  

USE [msdb]GOEXEC msdb.dbo.sp_add_operator @name=N'mail_user2',     @enabled=1,     @pager_days=0,     @email_address=N'KK_XXXX.163.COM'GO

 

 

  注 :操作员可根据是否在作业成功或失败时通知,后续脚本均未配置操作员,如需配置可在作业属性中自行添加 

  

 

AlwaysOn相关

节点切换监控

  

declare @role VARCHAR(8000);    declare @email_conetent varchar(8000);--存放邮件正文   declare @name varchar(100);  declare @lastsend int;  set @name =(select @@servername)  set @role=(SELECT role FROM sys.dm_hadr_availability_replica_states WHERE is_local=1)  set @lastsend = select datediff(MINUTE,max(send_request_date), getdate()) from sysmail_allitems where subject = @name + 'always on 预警'   if @role >1 and @lastsend > 30    begin       set @email_conetent=(@name+'当前节点不是主节点,发生故障转移')      print(@email_conetent)      --发送邮件            --邮件正文内容      EXEC msdb.dbo.sp_send_dbmail         @profile_name = 'DB-mail',     --配置文件名称        @recipients = 'KK_XXX@163.COM', --收件email地址        @subject = 'always on 预警',         --邮件主题        @body = @email_conetent      end     --else    --begin      --set @email_conetent= (@name+'当前节点是主节点')      --print(@email_conetent)    -- end

 

切点切换作业控制

  作业可以采用手动控制或如下脚本,也可以修改作业在作业执行前增加节点判断

--------------------------判断当前节点是否为主节点 如果不是则禁用作业 -------------------节点 切换为主节点则启用JOB ------------DECLARE @ROLE tinyint DECLARE @ENABLE tinyint ----判断是否是主节点 --1 主节点 SELECT @role=role FROM sys.dm_hadr_availability_replica_states WHERE is_local=1--判断JOB状态 --0 禁用 1 启用--以syspolicy_purge_history 为 参照 --如果 禁用或删除syspolicy_purge_history请修改 @ENABLE下段查询SELECT @ENABLE = [ENABLED] FROM MSDB.[dbo].[sysjobs] WHERE NAME = 'syspolicy_purge_history'-----第一次切换 辅助节点没有创建CDC作业 job 则创建作业 [category_id] = 13 CDC LOG SCAN JOBif not exists (select 1 from msdb.dbo.sysjobs where [category_id]= 13 or [category_id]= 16 ) and @ROLE = 1begin EXEC sys.sp_cdc_add_job @job_type = 'capture';EXEC sys.sp_cdc_add_job @job_type = 'cleanup';end---primary and job disable set job enableIF @ROLE = 1 and @ENABLE = 0BEGIN----如果存在原有作业为禁用,无法确定哪些JOB需要开启....所以此处最好手动维护作业的启用和禁用EXEC msdb.dbo.sp_update_job  @job_name = N'XXXXX',  @enabled = 1 ;-----执行 CDC EXEC msdb.dbo.sp_start_job @job_name = N'cdc.XX_capture'EXEC msdb.dbo.sp_start_job @job_name = N'cdc.XX_cleanup'end---not primary and job enable set disableIF @ROLE <> 1 and @ENABLE = 1BEGIN----如果存在原有作业为禁用,无法确定哪些JOB需要开启....所以此处最好手动维护作业的启用和禁用EXEC msdb.dbo.sp_update_job  @job_name = N'XXXXX',  @enabled = 0 ;END

 

数据备份

  备份方案:每天全备份、6小时一次差异备份、一小时一次日志备份。

备份存储过程

  存储过程创建后会保留在master库中,存储过程主要控制备份逻辑,备份路径等。

  存储过程中只有一个类型参数,用于控制全备/差异/日志备份,可根据需要修改。

USE [master]GO/****** Object: StoredProcedure [dbo].[sp_BackupDatabase]  Script Date: 01/22/2015 13:52:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Author: KK-- Create date: 2016-09-27-- Description: 备份数据库,备份路径F:\KK_BackUp\ 可自行修改-- http://www.cnblogs.com/double-K/-- Parameter1: 备份类型 F=全部, D=差异, L=日志alter PROCEDURE [dbo].[sp_BackupDatabase]@backupType CHAR(1)ASBEGINSET NOCOUNT ON;declare @filepath_backup varchar(100)declare @dateTime varchar(30),@del_time_stamp varchar(50)DECLARE @sqlCommand NVARCHAR(1000)---创建数据库对应文件夹EXECUTE master.dbo.xp_create_subdir N'F:\KK_BackUp\Full\'EXECUTE master.dbo.xp_create_subdir N'F:\KK_BackUp\Difference\'EXECUTE master.dbo.xp_create_subdir N'F:\KK_BackUp\Log_Bak\'IF @backupType = 'F'set @filepath_backup='F:\KK_BackUp\Full\'IF @backupType = 'D'set @filepath_backup='F:\KK_BackUp\Difference\'IF @backupType = 'L'set @filepath_backup='F:\KK_BackUp\Log_Bak\'SET ANSI_WARNINGS OFFSET @dateTime = replace(convert(varchar,current_timestamp, 112)+'_'+convert(varchar,current_timestamp, 108),':','')----删除超过3天的备份文件DECLARE @delete_time datetimeset @delete_time = getdate() - 3EXECUTE master.dbo.xp_delete_file 0,N'F:\kk_backup',N'trn',@delete_time,1EXECUTE master.dbo.xp_delete_file 0,N'F:\kk_backup',N'bak',@delete_time,1SELECT @dateTime = replace(convert(varchar,current_timestamp, 112)+'_'+convert(varchar,current_timestamp, 108),':','')declare db_info cursor for  SELECT NAME,recovery_model FROM MASTER.SYS.databases where state = 0 ---只处理online的数据库 and name not in ('tempdb','ReportServerTempDB','ReportServer') ----填写不需要备份的数据库declare @databaseName nvarchar(128) declare @recovery_model intOPEN db_info  fetch next from db_info into @databaseName,@recovery_model while @@fetch_status=0  Begin  ---recovery_model 1 : FULL 2 : BULK_LOGGED 3:SIMPLE---系统数据库只全备IF @backupType = 'F' SET @sqlCommand = 'BACKUP DATABASE '+ @databaseName +' TO DISK = '''+ @filepath_backup + ''+ @databaseName +'_Full_'+@dateTime+'.BAK'' with STATS = 10, INIT, COMPRESSION, CHECKSUM 'IF @backupType = 'D' and @databaseName not in ('master','msdb','model')SET @sqlCommand = 'BACKUP DATABASE '+ @databaseName +' TO DISK = '''+ @filepath_backup + ''+ @databaseName + '_Diff_' + @dateTime + '.BAK '' WITH DIFFERENTIAL, STATS = 10, INIT, COMPRESSION'IF @backupType = 'L' and @recovery_model = 3 and @databaseName not in ('master','msdb','model')SET @sqlCommand = 'BACKUP LOG '+ @databaseName +' TO DISK = '''+ @filepath_backup + '' + @databaseName +'_Log_' + @dateTime + '.TRN'' with STATS = 10, INIT, COMPRESSION'print @sqlCommandEXECUTE sp_executesql @sqlCommand fetch next from db_info into @databaseName,@recovery_model End  close db_info  deallocate db_info  PRINT '-- Backup completed successfully at '+convert(varchar, getdate(), 120)  SET ANSI_WARNINGS ONENDGO

 

备份作业

  备份作业很简单,就是调用存储过程用计划控制备份频率

  

-- Author: KK-- Create date: 2016-09-27-- Description: 备份数据库,全备份每天一次 0点执行,差异备份6小时一次,日志备份1小时一次-- http://www.cnblogs.com/double-K/--需要备份的数据库未使用参数传递,而是选择在存储过程中指定,当添加新库时不需要修改任何脚本-- Parameter1: 备份类型 F=全部, D=差异, L=日志-------------------完整备份作业-----------------USE [msdb]GO/****** Object: Job [FULL_BACKUP]  Script Date: 2016/9/30 12:13:12 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)]]]  Script Date: 2016/9/30 12:13:12 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'FULL_BACKUP',     @enabled=1,     @notify_level_eventlog=0,     @notify_level_email=0,     @notify_level_netsend=0,     @notify_level_page=0,     @delete_level=0,     @description=N'系统全备份',     @category_name=N'[Uncategorized (Local)]',     @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [FULL_STEP1]  Script Date: 2016/9/30 12:13:12 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'FULL_STEP1',     @step_id=1,     @cmdexec_success_code=0,     @on_success_action=1,     @on_success_step_id=0,     @on_fail_action=2,     @on_fail_step_id=0,     @retry_attempts=0,     @retry_interval=0,     @os_run_priority=0, @subsystem=N'TSQL',     @command=N'[dbo].[sp_BackupDatabase] ''F''',     @database_name=N'master',     @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EVERY_1d_zero',     @enabled=1,     @freq_type=4,     @freq_interval=1,     @freq_subday_type=1,     @freq_subday_interval=0,     @freq_relative_interval=0,     @freq_recurrence_factor=0,     @active_start_date=20160930,     @active_end_date=99991231,     @active_start_time=0,     @active_end_time=235959,     @schedule_uid=N'813653e1-4128-4f47-b378-5a26b49085d0'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO-------------------日志备份作业------------------USE [msdb]GO/****** Object: Job [LOG_BACKUP]  Script Date: 2016/9/30 12:13:25 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)]]]  Script Date: 2016/9/30 12:13:25 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'LOG_BACKUP',     @enabled=1,     @notify_level_eventlog=0,     @notify_level_email=0,     @notify_level_netsend=0,     @notify_level_page=0,     @delete_level=0,     @description=N'系统日志备份',     @category_name=N'[Uncategorized (Local)]',     @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [LOG_STEP1]  Script Date: 2016/9/30 12:13:25 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'LOG_STEP1',     @step_id=1,     @cmdexec_success_code=0,     @on_success_action=1,     @on_success_step_id=0,     @on_fail_action=2,     @on_fail_step_id=0,     @retry_attempts=0,     @retry_interval=0,     @os_run_priority=0, @subsystem=N'TSQL',     @command=N'[dbo].[sp_BackupDatabase] ''L''',     @database_name=N'master',     @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EVERY_1h',     @enabled=1,     @freq_type=4,     @freq_interval=1,     @freq_subday_type=8,     @freq_subday_interval=1,     @freq_relative_interval=0,     @freq_recurrence_factor=0,     @active_start_date=20160930,     @active_end_date=99991231,     @active_start_time=0,     @active_end_time=235959,     @schedule_uid=N'3d5ad87e-4f1d-46ef-9a24-e0f99c7d5c20'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO----------------------差异备份作业USE [msdb]GO/****** Object: Job [DIFF_BACKUP]  Script Date: 2016/9/30 12:13:19 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)]]]  Script Date: 2016/9/30 12:13:19 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DIFF_BACKUP',     @enabled=1,     @notify_level_eventlog=0,     @notify_level_email=0,     @notify_level_netsend=0,     @notify_level_page=0,     @delete_level=0,     @description=N'无描述。',     @category_name=N'[Uncategorized (Local)]',     @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [DIFF_STEP1]  Script Date: 2016/9/30 12:13:19 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DIFF_STEP1',     @step_id=1,     @cmdexec_success_code=0,     @on_success_action=1,     @on_success_step_id=0,     @on_fail_action=2,     @on_fail_step_id=0,     @retry_attempts=0,     @retry_interval=0,     @os_run_priority=0, @subsystem=N'TSQL',     @command=N'[dbo].[sp_BackupDatabase] ''D''',     @database_name=N'master',     @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EXERY_6h',     @enabled=1,     @freq_type=4,     @freq_interval=1,     @freq_subday_type=8,     @freq_subday_interval=6,     @freq_relative_interval=0,     @freq_recurrence_factor=0,     @active_start_date=20160930,     @active_end_date=99991231,     @active_start_time=0,     @active_end_time=235959,     @schedule_uid=N'f7514c1b-128f-4ae4-8361-9dbcbbff66c6'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO

 

数据一致性检查

 

-- Author: KK-- Create date: 2016-09-27-- Description: 数据库一致性检查,每周运行及时发现数据库损坏

-- 本脚本针对于中小型数据库,当数据库达到一定规模超过T级或有大表使用计算列等,可适当拆分或调整,以免checkdb时间超过维护时间窗口而影响业务-- E:\checkdb_report.txt , 输出文件的路径,检查出的错误信息或被记录进去,或直接通过作业记录查看-- http://www.cnblogs.com/double-K/--需要备份的数据库未使用参数传递,而是选择在存储过程中指定,当添加新库时不需要修改任何脚本--脚本针对中小数据库,如果数据库超过1T甚至更大,CHECKDB也是必要操作,但需要拆分文件组或更精细化检查以降低每次检查的时间,保证在指定的维护窗口完成任务。USE [msdb]GO/****** Object: Job [CHECKDB] Script Date: 09/30/2016 15:16:01 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/30/2016 15:16:01 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CHECKDB', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'数据库一致性检查,可以发现数据库是否有损坏。', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [CHECKDB] Script Date: 09/30/2016 15:16:01 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CHECKDB', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'declare db_info cursor for SELECT NAME FROM MASTER.SYS.databases where state = 0 ---只处理online的数据库 and name not in (''tempdb'',''ReportServerTempDB'',''ReportServer'') ----填写不需要检查的数据库declare @databaseName nvarchar(128) declare @recovery_model intDECLARE @sqlCommand NVARCHAR(1000)OPEN db_info fetch next from db_info into @databaseName while @@fetch_status=0 Begin SET @sqlCommand = ''DBCC CHECKDB(N''''''+ @databaseName + '''''') WITH NO_INFOMSGS''print @sqlCommandEXECUTE sp_executesql @sqlCommand fetch next from db_info into @databaseName End close db_info deallocate db_info ', @database_name=N'master', @output_file_name=N'E:\checkdb_report.txt', --输出文件的路径,检查出的错误信息或被记录进去,或直接通过作业记录查看 @flags=4IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'sunday_2am', @enabled=1, @freq_type=8, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20160930, @active_end_date=99991231, @active_start_time=20000, @active_end_time=235959, @schedule_uid=N'3ade533f-5ce1-434f-98ff-b4509b2ca582'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO

 

Agent作业备份

  备份作业可以通过备份MSDB完成,但是保留一份脚本还是不错的,脚本为存储过程,建议一个周或一个月备份一次,可使用JOB 调用存储过程。

  

USE [master]GO/****** Object: StoredProcedure [dbo].[DumpJobsql]  Script Date: 02/07/2014 11:38:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOalter PROCEDURE [dbo].[usp_DumpJobsql]ASBEGIN-- Author: KK-- Create date: 2016-09-27-- Description: 备份JOB,目前不支持邮件-- 生成数据一份保留在master的zzz_temp_JOB_bcp表中,另外会在目标位置生成一个 job_日期.sql-- http://www.cnblogs.com/double-K/  SET NOCOUNT ON  DECLARE @SV nvarchar(4)  DECLARE @i_enabled TINYINT  DECLARE @sql VARCHAR(max)  DECLARE @i_job_name          VARCHAR(1000)  DECLARE @i_notify_level_eventlog  INT  DECLARE @i_notify_level_email    INT  DECLARE @i_notify_level_netsend    INT  DECLARE @i_notify_level_page    INT  DECLARE @i_delete_level        INT  DECLARE @i_description        VARCHAR(1000)  DECLARE @i_category_name      VARCHAR(1000)  DECLARE @i_owner_login_name      VARCHAR(1000)  DECLARE @i_category_class      INT  DECLARE @i_start_step_id       INT                  DECLARE @i_step_name         VARCHAR(1000)     DECLARE @i_step_id           INT          DECLARE @i_cmdexec_success_code    INT         DECLARE @i_on_success_action     INT          DECLARE @i_on_success_step_id     INT          DECLARE @i_on_fail_action       INT          DECLARE @i_on_fail_step_id       INT          DECLARE @i_retry_attempts       BIGINT        DECLARE @i_retry_interval       INT          DECLARE @i_os_run_priority      INT          DECLARE @i_subsystem         VARCHAR(1000)     DECLARE @i_command          VARCHAR(8000)  DECLARE @i_database_name      VARCHAR(100)         DECLARE @i_flags          INT     DECLARE @i_class VARCHAR(10) ,@i_type VARCHAR(10)  DECLARE @c_jobid UNIQUEIDENTIFIER ,@c_categoryid INT  DECLARE @loop_stepid        INT  DECLARE @m_stepid          INT      DECLARE @loop_scheduleid      INT  DECLARE @m_scheduleid        INT  DECLARE @i_schedule_enabled      TINYINT  DECLARE @i_freq_type        INT  DECLARE @i_schedule_name      VARCHAR(1000)    DECLARE @i_freq_interval      INT    DECLARE @i_freq_subday_type      INT  DECLARE @i_freq_subday_interval    INT  DECLARE @i_freq_relative_interval  INT  DECLARE @i_freq_recurrence_factor  INT  DECLARE @i_active_start_date    BIGINT    DECLARE @i_active_end_date      BIGINT    DECLARE @i_active_start_time    BIGINT    DECLARE @i_active_end_time      BIGINT    DECLARE @i_schedule_uid        VARCHAR(1000)  SET @i_class  =  'JOB'  SET @i_type    =  'LOCAL'    if exists (select 1 from sys.objects where name = 'zzz_temp_JOB_bcp')    begin      delete from master..zzz_temp_JOB_bcp    end    else    begin      create table zzz_temp_JOB_bcp(name nvarchar(100),text nvarchar(max),sv nvarchar(4),Bak_date nvarchar(10))    end  DECLARE job CURSOR FOR     SELECT a.job_id ,a.category_id,'服务器XX' as SV     FROM msdb.dbo.sysjobs a , msdb.dbo.syscategories c    WHERE  a.category_id = c.category_id           AND c.name NOT LIKE '%Database Maintenance%'           AND c.name NOT LIKE '%REPL%'          AND c.name <> 'Log Shipping'          AND a.name <> 'syspolicy_purge_history'    ----如果需要可多服务器备份    --union all    --select a.job_id ,a.category_id,'服务器XXX'     --from     --opendatasource('SQLOLEDB','Data Source=XX.XX.XX.XX;User ID=XX;Password=XX').msdb.dbo.sysjobs a,    --opendatasource('SQLOLEDB','Data Source=XX.XX.XX.XX;User ID=XX;Password=XX').msdb.dbo.syscategories c    --WHERE  a.category_id = c.category_id     --    AND c.name NOT LIKE '%Database Maintenance%'     --    AND c.name NOT LIKE '%REPL%'    --    AND c.name <> 'Log Shipping'    --    AND a.name <> 'syspolicy_purge_history'  OPEN job  FETCH job INTO @c_jobid ,@c_categoryid,@SV  WHILE @@FETCH_STATUS = 0  BEGIN    SET @sql = ''    SELECT  @i_job_name         = a.name ,        @i_enabled       = [enabled] ,        @i_notify_level_eventlog = notify_level_eventlog ,        @i_notify_level_email   = notify_level_email ,        @i_notify_level_netsend   = notify_level_netsend ,        @i_notify_level_page   = notify_level_page ,        @i_delete_level       = delete_level ,        @i_description       = [description] ,        @i_category_name     = c.name ,        @i_owner_login_name     = ISNULL(SUSER_SNAME(a.owner_sid), N'''') ,        @i_category_class     = category_class         FROM msdb.dbo.sysjobs a ,msdb.dbo.syscategories c        WHERE a.category_id=c.category_id AND a.job_id=@c_jobid AND a.category_id = @c_categoryid    SET @sql=@sql+CHAR(13)+CHAR(10) + 'USE [msdb]'    SET @sql=@sql+CHAR(13)+CHAR(10) + 'GO'    SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: Job ['+ @i_job_name +']  Script Date: '+CONVERT(VARCHAR,GETDATE(),22)+' ******/'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'BEGIN TRANSACTION'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'DECLARE @ReturnCode INT'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'SELECT @ReturnCode = 0'    SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: JobCategory ['+ @i_category_name +']  Script Date: 08/20/2016 12:35:16 ******/'    SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'''+ @i_category_name +''' AND category_class='+ CAST(@i_category_class AS VARCHAR) +' )'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'BEGIN'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'''+ @i_class +''', @type=N'''+ @i_type +''', @name=N'''+ @i_category_name +''''     SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'    SET @sql=@sql+CHAR(13)+CHAR(10) + ''     SET @sql=@sql+CHAR(13)+CHAR(10) + 'END'    SET @sql=@sql+CHAR(13)+CHAR(10) + ''    SET @sql=@sql+CHAR(13)+CHAR(10) + 'DECLARE @jobId BINARY(16)'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'''+ @i_job_name +''','     SET @sql=@sql+CHAR(13)+CHAR(10) + '    @enabled='+ CAST(@i_enabled AS VARCHAR) +','     SET @sql=@sql+CHAR(13)+CHAR(10) + '    @notify_level_eventlog='+ CAST(@i_notify_level_eventlog AS VARCHAR) +','    SET @sql=@sql+CHAR(13)+CHAR(10) + '    @notify_level_email='+ CAST(@i_notify_level_email AS VARCHAR) +','     SET @sql=@sql+CHAR(13)+CHAR(10) + '    @notify_level_netsend='+ CAST(@i_notify_level_netsend AS VARCHAR) +','     SET @sql=@sql+CHAR(13)+CHAR(10) + '    @notify_level_page='+ CAST(@i_notify_level_page AS VARCHAR) +','     SET @sql=@sql+CHAR(13)+CHAR(10) + '    @delete_level='+ CAST(@i_delete_level AS VARCHAR) +','     SET @sql=@sql+CHAR(13)+CHAR(10) + '    @description=N'''+ @i_description +''','     SET @sql=@sql+CHAR(13)+CHAR(10) + '    @category_name=N'''+ @i_category_name +''','     SET @sql=@sql+CHAR(13)+CHAR(10) + '    @owner_login_name=N'''+ @i_owner_login_name +''', @job_id = @jobId OUTPUT'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'     IF EXISTS ( SELECT TOP 1 1 FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid )    BEGIN      SELECT @loop_stepid = MIN(step_id) ,@m_stepid = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid       WHILE (@loop_stepid < = @m_stepid)       BEGIN           SELECT  @i_start_step_id    = start_step_id,            @i_step_name      = step_name ,            @i_step_id        = step_id,            @i_cmdexec_success_code = cmdexec_success_code ,            @i_on_success_action  = on_success_action ,            @i_on_success_step_id  = on_success_step_id ,            @i_on_fail_action    = on_fail_action ,            @i_on_fail_step_id    = on_fail_step_id ,            @i_retry_attempts    = retry_attempts ,            @i_retry_interval    = retry_interval ,            @i_os_run_priority    = os_run_priority ,            @i_subsystem      = subsystem ,            @i_command        = command ,            @i_database_name    = database_name ,            @i_flags        = flags            FROM msdb.dbo.sysjobs a ,msdb.dbo.sysjobsteps b             WHERE a.job_id = b.job_id AND step_id = @loop_stepid AND a.job_id = @c_jobid         SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: Step ['+ @i_step_name +']  Script Date: '+CONVERT(VARCHAR,GETDATE(),22)+' ******/'         SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'''+ @i_step_name +''','        SET @sql=@sql+CHAR(13)+CHAR(10) + '    @step_id='+ CAST(@i_step_id AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @cmdexec_success_code='+ CAST(@i_cmdexec_success_code AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @on_success_action='+ CAST(@i_on_success_action AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @on_success_step_id='+ CAST(@i_on_success_step_id AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @on_fail_action='+ CAST(@i_on_fail_action AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @on_fail_step_id='+ CAST(@i_on_fail_step_id AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @retry_attempts='+ CAST(@i_retry_attempts AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @retry_interval='+ CAST(@i_retry_interval AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @os_run_priority='+ CAST(@i_os_run_priority AS VARCHAR) +', @subsystem=N'''+ @i_subsystem +''','         SET @sql=@sql+CHAR(13)+CHAR(10) + ISNULL('    @command=N''' + REPLACE(@i_command ,'''' ,'''''') + ''',' ,'')         SET @sql=@sql+CHAR(13)+CHAR(10) + ISNULL('    @database_name=N'''+ @i_database_name +''',' ,'')         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @flags='+ CAST(@i_flags AS VARCHAR)         SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'         SET @loop_stepid = ( SELECT TOP 1 step_id FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid AND step_id > @loop_stepid ORDER BY step_id )      END    END    SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = '+ CAST(@i_start_step_id AS VARCHAR)     SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'     IF EXISTS ( SELECT TOP 1 1 FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid )    BEGIN      SELECT @loop_scheduleid= MIN(c.schedule_id) ,@m_scheduleid = MAX(c.schedule_id)         FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d        WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid       WHILE ( @loop_scheduleid <= @m_scheduleid )       BEGIN        SELECT  @i_schedule_enabled      = [enabled] ,            @i_freq_type        = freq_type ,            @i_schedule_name      = name,            @i_freq_interval      = freq_interval ,            @i_freq_subday_type      = freq_subday_type ,            @i_freq_subday_interval    = freq_subday_interval ,            @i_freq_relative_interval  = freq_relative_interval ,            @i_freq_recurrence_factor  = freq_recurrence_factor ,            @i_active_start_date    = active_start_date ,            @i_active_end_date      = active_end_date ,            @i_active_start_time    = active_start_time ,            @i_active_end_time      = active_end_time ,            @i_schedule_uid        = schedule_uid             FROM msdb.dbo.sysschedules c LEFT JOIN msdb.dbo.sysjobschedules d               ON c.schedule_id = d.schedule_id             WHERE d.job_id = @c_jobid AND c.schedule_id = @loop_scheduleid         SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'''+ @i_schedule_name +''','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @enabled='+ CAST(@i_schedule_enabled AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @freq_type='+ CAST(@i_freq_type AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @freq_interval='+ CAST(@i_freq_interval AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @freq_subday_type='+ CAST(@i_freq_subday_type AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @freq_subday_interval='+ CAST(@i_freq_subday_interval AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @freq_relative_interval='+ CAST(@i_freq_relative_interval AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @freq_recurrence_factor='+ CAST(@i_freq_recurrence_factor AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @active_start_date='+ CAST(@i_active_start_date AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @active_end_date='+ CAST(@i_active_end_date AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @active_start_time='+ CAST(@i_active_start_time AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @active_end_time='+ CAST(@i_active_end_time AS VARCHAR) +','         SET @sql=@sql+CHAR(13)+CHAR(10) + '    @schedule_uid=N'''+ @i_schedule_uid +''''         SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'         SET @loop_scheduleid = ( SELECT TOP 1 c.schedule_id FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d                        WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid AND c.schedule_id > @loop_scheduleid )       END    END    SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)'''     SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'COMMIT TRANSACTION'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'GOTO EndSave'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'QuitWithRollback:'     SET @sql=@sql+CHAR(13)+CHAR(10) + '  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'     SET @sql=@sql+CHAR(13)+CHAR(10) + 'EndSave:'     SET @sql=@sql+CHAR(13)+CHAR(10) + ''     SET @sql=@sql+CHAR(13)+CHAR(10) + 'GO'    PRINT @sql    insert into master..zzz_temp_JOB_bcp    SELECT @i_job_name,@sql,@SV,CONVERT(nvarchar(10),getdate(),112)    FETCH NEXT FROM job INTO @c_jobid ,@c_categoryid ,@SV  END  CLOSE job  DEALLOCATE job  declare @a nvarchar(17),@c nvarchar(1000),@name nvarchar(100),@d nvarchar(100)  set @a = CONVERT (nvarchar(17),GETDATE(),112)  set @name = 'F:\kk_backup\job_'+@a+'.sql'  set @d = 'del ' + @name   set @c = 'bcp "select text from master..zzz_temp_JOB_bcp where bak_date = CONVERT(nvarchar(10),getdate(),112)" queryout "'+ @name +'" -c -S"服务名称" -U"sa" -P"sa123456" '   print @d  print @c  exec sp_configure 'show advanced options',1  reconfigure with override  exec sp_configure 'xp_cmdshell',1  reconfigure with override   EXEC master..xp_cmdshell @d   EXEC master..xp_cmdshell @c  exec sp_configure 'xp_cmdshell',0  reconfigure with override  exec sp_configure 'show advanced options',0  reconfigure with override  endGO

 

--------------博客地址---------------------------------------------------------------------------------------

Expert 诊断优化系列 http://www.cnblogs.com/double-K/

 

 

-----------------------------------------------------------------------------------------------------

 

  总结 : 文章中大部分脚本针对于中小数据库,由于工作性质涉及很多客户部署维护作业,所以力求总结出一套比较完善的脚本,一键部署。

     本文脚本目前还不完善,后续会不断补充。另外也请各位看官们贡献下自己深藏的脚本,方便大众,我也取长补短!

     再次感谢!

 ----------------------------------------------------------------------------------------------------

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!