您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 【威尼斯官方网站】本篇就分享一些常规的运维

【威尼斯官方网站】本篇就分享一些常规的运维

发布时间:2020-01-04 07:04编辑:威尼斯官方网站登录浏览(81)

      数据库运转中传出三个小段子,小编误删除了数据库,改如何做?有备份还原备份,未有备份就策动简历!听上去风趣但产生在何人身上,何人都笑不起来。接触了累累的客商发掘十分九客商的运转攻略都不是很周全。本篇就享受部分常规的运转脚本,本篇未有提到到的或不足的也请大家留言无私进献深藏多年的本子,感激!

    邮件

      邮件首要用来监督作业是还是不是运转成功,要是你已经安排了就像是zabbix等软件请忽视。

    布署邮件服务

      

    --SQL Server 并没有内置邮件服务器(Mail Server),它跟我们发送邮件一样,需要用户名和密码通过 SMTP(Simple Message Transfer Protocol)去连接邮件服务器。我们想让 SQL Server 来发送邮件,首先要告诉它用户名称,密码,服务器地址,网络传送协议,邮件服务器的端口。。。等信息。
    --  以下脚本实现了数据库邮件的配置:
    ----下面是具体的配置邮件步骤 
    ----在 sa 系统帐户下运行。 
    --
    --1. 启用 SQL Server  邮件功能。 
    use master
    go
    exec sp_configure 'show advanced options',1 
    go 
    reconfigure with override
    go
    exec sp_configure 'Database Mail XPs',1
    go
    reconfigure  with override
    go
    --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 msdb
    go
    select * from sysmail_allitems
    select * from sysmail_mailitems
    select * 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
    

     

     

    布局操作员

      操作员首倘若用来作业的通报对象:

      威尼斯官方网站 1

      

      配置如下:

      威尼斯官方网站 2

      

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

     

     

      注 :操作员可遵照是不是在学业成功或倒闭时通报,后续脚本均未配备操作员,如需配置可在学业属性中机动添加 

      威尼斯官方网站 3

     

    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 JOB
    if not exists (select 1 from msdb.dbo.sysjobs where [category_id]= 13 or [category_id]= 16 ) and  @ROLE = 1
    begin 
    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 enable
    IF @ROLE = 1 and @ENABLE = 0
    BEGIN
    
    ----如果存在原有作业为禁用,无法确定哪些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  disable
    IF @ROLE <> 1 and @ENABLE  = 1
    BEGIN
    ----如果存在原有作业为禁用,无法确定哪些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 ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- 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)
    AS
    BEGIN
    SET 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_BackUpFull'
    EXECUTE master.dbo.xp_create_subdir N'F:KK_BackUpDifference'
    EXECUTE master.dbo.xp_create_subdir N'F:KK_BackUpLog_Bak'
    
    IF @backupType = 'F'
    set @filepath_backup='F:KK_BackUpFull'
    IF @backupType = 'D'
    set @filepath_backup='F:KK_BackUpDifference'
    IF @backupType = 'L'
    set @filepath_backup='F:KK_BackUpLog_Bak'
    
    SET ANSI_WARNINGS OFF
    SET @dateTime = replace(convert(varchar,current_timestamp, 112)+'_'+convert(varchar,current_timestamp, 108),':','')
    
    ----删除超过3天的备份文件
    DECLARE @delete_time datetime
    set @delete_time = getdate() - 3
    
    EXECUTE master.dbo.xp_delete_file 0,N'F:kk_backup',N'trn',@delete_time,1
    EXECUTE master.dbo.xp_delete_file 0,N'F:kk_backup',N'bak',@delete_time,1
    
    SELECT @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  int
    OPEN 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 @sqlCommand
    
    
    
    EXECUTE 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 ON
    END
    GO
    

     

    备份作业

      备份作业很简短,便是调用存款和储蓄进度用安插调整备份频率

      

    -- 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 TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @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)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @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 OUTPUT
    IF (@@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=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @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 QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    GO
    
    
    
    
    
    -------------------日志备份作业------------------
    USE [msdb]
    GO
    
    /****** Object:  Job [LOG_BACKUP]    Script Date: 2016/9/30 12:13:25 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @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)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @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 OUTPUT
    IF (@@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=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @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 QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    GO
    
    
    
    ----------------------差异备份作业
    USE [msdb]
    GO
    
    /****** Object:  Job [DIFF_BACKUP]    Script Date: 2016/9/30 12:13:19 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @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)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @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 OUTPUT
    IF (@@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=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @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 QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    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 TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @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)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @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 OUTPUT
    IF (@@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  int
    DECLARE @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 @sqlCommand
    
    EXECUTE 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=4
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @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 QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    GO
    

     

    Agent作业备份

      备份作业能够透过备份MSDB完结,然而保留风度翩翩份脚本依旧不错的,脚本为存款和储蓄过程,提议贰个周或二个月备份二次,可利用JOB 调用存款和储蓄进程。

      

    USE [master]
    GO
    
    /****** Object:  StoredProcedure [dbo].[DumpJobsql]    Script Date: 02/07/2014 11:38:46 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    alter PROCEDURE [dbo].[usp_DumpJobsql]
    AS
    BEGIN
    
    
    -- 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_backupjob_'+@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
        end
    GO
    

     

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

    Expert 确诊优化种类 

     

     


     

      总计 : 随笔中山高校部分本子针对于中型小型数据库,由于职业性质涉及多数顾客布署维护作业,所以力求总计出豆蔻梢头套相比较完善的脚本,黄金年代键布置。

         本文脚本近来还不周全,后续会持续增补。其余也请各位看官们贡献下自个儿收藏的脚本,方便公众,笔者也酌盈剂虚!

         再度谢谢!

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

    注:此小说为原创,应接转发,请在篇章页面显明地点给出此文链接!
    若您感觉那篇随笔还不易请点击下右下角的推荐,特别谢谢!

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:【威尼斯官方网站】本篇就分享一些常规的运维

    关键词:

上一篇:没有了

下一篇:SQL本领内情