数据库的运维策略脚本篇SQL SERVER（内附脚本，无私分享）
数据库运维中盛传一个小段子，我误删除了数据库，改怎么办？有备份还原备份，没有备份就准备简历！听起来有趣但发生在谁身上，谁都笑不起来。接触了很多的客户发现90%客户的运维策略都不是很完善。本篇就分享一些常规的运维脚本，本篇没有涉及到的或不足的也请大家留言无私贡献深藏多年的脚本，谢谢！
邮件
邮件主要用来监控作业是否运行成功，如果您已经配置了类似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
配置操作员
操作员主要是用于作业的通知对象：
配置如下：
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
注 ：操作员可根据是否在作业成功或失败时通知，后续脚本均未配置操作员，如需配置可在作业属性中自行添加
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 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_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 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_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 end GO
附上 博客地址
Expert 诊断优化系列 http://www.cnblogs.com/double-K/
