博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
数据库的运维策略脚本篇(内附脚本,无私分享)
阅读量:5840 次
发布时间:2019-06-18

本文共 37438 字,大约阅读时间需要 124 分钟。

 数据库运维中盛传一个小段子,我误删除了数据库,改怎么办?有备份还原备份,没有备份就准备简历!听起来有趣但发生在谁身上,谁都笑不起来。接触了很多的客户发现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;declare @subject_str varchar(100);    set @name =(select @@servername)set @subject_str = @name + 'always on 预警'    set @role=(SELECT role FROM sys.dm_hadr_availability_replica_states WHERE is_local=1)    set @lastsend = (select isnull(datediff(MINUTE,max(send_request_date), getdate()),6000) from [msdb].[dbo].[sysmail_mailitems] where subject = @subject_str)     if @role >1 and @lastsend > 30  ----30分钟发送一次        begin             set  @email_conetent=(@name+'当前节点不是主节点,发生故障转移')            print(@email_conetent)print(@lastsend)--if @lastsend > 1            --发送邮件                        --邮件正文内容            EXEC msdb.dbo.sp_send_dbmail                 @profile_name = 'DB-mail',         --配置文件名称                @recipients = 'KK_XXX@163.COM',  --收件email地址                @subject = @subject_str,                 --邮件主题                @body = @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

转载于:https://www.cnblogs.com/accumulater/p/6152848.html

你可能感兴趣的文章
《写给大忙人看的java se 8》笔记
查看>>
倒计时:计算时间差
查看>>
Linux/windows P2V VMWare ESXi
查看>>
Windows XP倒计时到底意味着什么?
查看>>
运维工程师在干什么学些什么?【致菜鸟】
查看>>
Linux中iptables详解
查看>>
java中回调函数以及关于包装类的Demo
查看>>
maven异常:missing artifact jdk.tools:jar:1.6
查看>>
终端安全求生指南(五)-——日志管理
查看>>
Nginx 使用 openssl 的自签名证书
查看>>
创业维艰、守成不易
查看>>
PHP环境安装套件:快速安装LAMP环境
查看>>
CSS3
查看>>
ul下的li浮动,如何是ul有li的高度
查看>>
C++ primer plus
查看>>
python mysqlDB
查看>>
UVALive 3942 Remember the Word Tire+DP
查看>>
从微软的DBML文件中我们能学到什么(它告诉了我们什么是微软的重中之重)~目录...
查看>>
被需求搞的一塌糊涂,怎么办?
查看>>
c_数据结构_队的实现
查看>>