内附脚本，使用SQLServer同义词和SQL邮件
数据库运转中传来三个小段子，笔者误删除了数据库，改如何做？有备份还原备份，未有备份就计划简历！听上去风趣但发生在哪个人身上，何人都笑不起来。接触了重重的顾客发现十分九顾客的运行计谋都不是很完善。本篇就享受部分好端端的运行脚本，本篇未有提到到的或不足的也请我们留言无私进献深藏多年的剧本，多谢！
最近几年给客商做了根据SQLServer的揭破订阅的“读写分离”作用，可是一些表数据比非常的大，平常发出某几条数据错过的主题材料，导致订阅不能继续开展。不过每一回开采难题再次做贰遍公布订阅又不行消耗费时间间，所以还得依照“复制监视器”的晋升，找到遗失的数目，手工业管理。
邮件
邮件重要用来监督作业是还是不是运营成功，假诺你已经配备了类似zabbix等软件请忽视。
固化缺点和失误数据
首先，找到出标题标同台语句，在公布服务器的“复制监视器”上业务订阅的详细新闻里面，找到出错的新闻
尝试的命令:
if @@trancount > 0 rollback tran
(事务序列号: 0x0000992600000D09007F00000000，命令 ID: 19)
错误消息:
应用复制的命令时在订阅服务器上找不到该行。 (源: MSSQLServer，错误号: 20598)
获取帮助: http://help/20598
应用复制的命令时在订阅服务器上找不到该行。 (源: MSSQLServer，错误号: 20598)
下一场在散发服务器上实行下边包车型地铁SQL语句，
use distribution
go
sp_browsereplcmds '0x0000992600000D09007F00000000' ,'0x0000992600000D09007F00000000'
go
根据指令ID（如上面的ID:19），找到切实可行的一块命令（Command列），类似于这样的：
{CALL [dbo].[sp_MSdel_dboT_TODO] ('697e7cacf5354a36be1ae4cf50dcdaa6')}
此间是 订阅库上的 sp_MSdel_dboT_TODO 存款和储蓄进程，查看存款和储蓄进程定义知道参数是ID的值，这里说找不到要删减的多少，那么我们在订阅Curry面模拟增添这些ID的笔录即可。增添数码，
配置邮件服务
--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
补录数据
英特网提供的应用方案是用三个工具生成差距的SQL数据然后给订阅库实行，但看了下感觉不是很有益于，想起来SqlServer还提供二个insert...from....语句，那么是还是不是足以一向从透露数据库查询数据然后插入给订阅数据库呢？
可以行使同义词从发布库查询过来插入到地面订阅库，请看下边具体经过：
先在订阅库上创设贰个同义词，比如下边为表 Biz_Customer 创立四个一律词
Biz_Customer_Master，创设的时候，供给内定同义词所在的服务器名称，数据库名称，架构，表名称等消息。
然而此时同义词还不可能一贯运用，还索要创设“链接服务器”，具体进程如下：
EXEC sp_addlinkedserver
@server='192.168.7.4',--被访问的服务器别名（习惯上直接使用目标服务器IP，或取个别名如：JOY）
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.7.4' --要访问的服务器
go
EXEC sp_addlinkedsrvlogin
'192.168.7.4', --被访问的服务器别名（如果上面sp_addlinkedserver中使用别名JOY，则这里也是JOY）
'false',
NULL,
'sa', --帐号
'1234567890' --密码
go
select * from sys.servers;
接下来利用上边包车型客车SQL语句插入数据：
insert into [Biz_Customer]
select * from Biz_Customer_Master where id='7B210173-7382-43EB-BC5E-0000C3BA564A'
查询报错，有个别列的数据类型错误，展开表一看，原本是 发表库上的表的字段顺序跟订阅库上区别样，因为那时做订阅的时候，为了消除提姆estamp 难题，将订阅库的Timestamp字段修改成了binary(8)类型，故订阅库上表的字段顺序更动了。
此刻，只必要在insert 和 select
语句上，钦定同样顺序的列就能够了。那么如何收获表全体的列名称？
相当粗略，直接选取某个表，新建查询，生成的SQL语句就隐含表全部的字段了。
最终正确的讲话如下：
insert into [TB_Customer]([Id]
,[CustomerId]
,[Code]
,[Name]
,[BusinessId]
,[CreatedOn]
,[CreatedById]
,[ModifiedOn]
,[ModifiedById]
,[AppraiseTableType]
,[Timestamp]
)
SELECT [Id]
,[CustomerId]
,[Code]
,[Name]
,[BusinessId]
,[CreatedOn]
,[CreatedById]
,[ModifiedOn]
,[ModifiedById]
,[AppraiseTableType]
,[Timestamp]
FROM dbo.TB_Customer_Master
where id='7B210173-7382-43EB-BC5E-0000C3BA564A';
由此这么的办法，非常低价的把公布库的数量就补偿到订阅库上了，之后，数据库的公布订阅错误就消除了。
陈设操作员
操作员首假诺用于作业的通报对象：
配置如下：
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
注 ：操作员可依照是或不是在作业成功或失利时通报，后续脚本均未配备操作员，如需陈设可在学业属性中自行增添
修改订阅仓库储存款和储蓄进程
可是，假若这么的谬误相当多，每一遍都去靠手工修补数据是不行的，所以大家还需求找到订阅库上的体系存款和储蓄进程，做相应的修改。
- 修改数据，对应的囤积进程名字是 sp_MSupd_dboTableName ，所以大家能够得到要操作的表名字：dbo.TableName
- 删除数据，对应的积攒进程名字是 sp_MSdel_dboTableName，所以我们能够得到要操作的表名字：dbo.TableName
假诺是剔除数据，直接把囤积进程中的上面内容注释：
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
一经是修改数据，首先也要把下面的内容注释，然后在仓库储存进程的最后，加多底下这样的代码：
if @@rowcount = 0
begin
insert into [TB_Customer]([Id]
,[CustomerId]
,[Code]
,[Name]
,[BusinessId]
,[CreatedOn]
,[CreatedById]
,[ModifiedOn]
,[ModifiedById]
,[AppraiseTableType]
,[Timestamp]
)
SELECT [Id]
,[CustomerId]
,[Code]
,[Name]
,[BusinessId]
,[CreatedOn]
,[CreatedById]
,[ModifiedOn]
,[ModifiedById]
,[AppraiseTableType]
,[Timestamp]
FROM [192.168.7.4].XXDB.dbo.Biz_Customer
where id=@pkc1
end
这里未有利用同义词，而是直接使用远程服务器名字加数据库名字格局内定远程表名字，当您要修改的积攒进度比较多，推荐应用这种形式并非同义词。
参数 @pkc1 是积攒进程接纳的主键参数，每一个存款和储蓄进度都以如此的。
AlwaysOn相关
运用游标生成修改语句
唯独，固然要修改从存款和储蓄进度非常多，那样三个个的去手工业修改存储进度是极度麻烦的，所以大家得以把地点的历程，写三个T-SQL来输出，大家使用游标来便利表全部的列，生成语句：
declare @ObjTbName varchar(100)
declare @ColName varchar(100)
declare @ColType varchar(50)
declare @AllColName varchar(max)
declare @SqlText varchar(max)
set @ObjTbName='TB_Customer'
set @SqlText ='insert into ' @ObjTbName '('
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=@ObjTbName
OPEN column_cursor
FETCH NEXT FROM column_cursor into @ColName,@ColType
set @AllColName ='[' @ColName ']'
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
--print 'Col Name:' @ColName ',Col Type:' @ColType
FETCH NEXT FROM column_cursor into @ColName,@ColType
if @@FETCH_STATUS = 0
--print ' ,' @ColName
set @AllColName = @AllColName ',[' @ColName ']'
END
CLOSE column_cursor
DEALLOCATE column_cursor
--print @AllColName
set @SqlText =@SqlText char(10) @AllColName ')' CHAR(10)
set @SqlText =@SqlText 'select ' CHAR(10) @AllColName CHAR(10)
set @SqlText =@SqlText ' from [192.168.7.4].XXDB.dbo.' @ObjTbName ' where id=@pkc1 '
print '--if @@rowcount = 0'
print '-- if @@microsoftversion>0x07320000'
print '-- exec sp_MSreplraiserror 20598'
print 'end '
print 'end '
print 'if @@rowcount = 0'
print 'begin'
print @SqlText
print 'end '
将输音讯复制粘贴在要修改的蕴藏进度尾部就能够。
修改并施行这些蕴藏进度，等订阅代理重新执行那个蕴藏进度后，数据就过去了。
为了便利那个那些进度被前后相继调用，能够将它封装成存款和储蓄进程，具体内容如下：
/*
--创建数据库复制的时候订阅库修改使用的存储过程
--具体原理和使用，请参考博客文章：
-- http://www.cnblogs.com/bluedoctor/p/5680582.html
--作者：请参考博客文章作者
--时间：2016.7.20
--调用示例：
exec BuildReplUpdateTable 'MainSqlServer','HRDB','TB_AuditOrgBalance',1
*/
create procedure BuildReplUpdateTable
@LinkServer varchar(100),
@ObjDBName varchar(50),
@ObjTbName varchar(100),
@IsSp_MSupd bit
as
begin
declare @ColName varchar(100)
declare @ColType varchar(50)
declare @AllColName varchar(max)
declare @SqlText varchar(max)
declare @TempText varchar(max)
set @SqlText ='insert into ' @ObjTbName '('
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=@ObjTbName
OPEN column_cursor
FETCH NEXT FROM column_cursor into @ColName,@ColType
set @AllColName ='[' @ColName ']'
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'Col Name:' @ColName ',Col Type:' @ColType
FETCH NEXT FROM column_cursor into @ColName,@ColType
if @@FETCH_STATUS = 0
set @AllColName = @AllColName ',[' @ColName ']'
END
CLOSE column_cursor
DEALLOCATE column_cursor
set @SqlText =@SqlText char(10) @AllColName ')' CHAR(10)
set @SqlText =@SqlText 'select ' CHAR(10) @AllColName CHAR(10)
set @SqlText =@SqlText ' from [' @LinkServer '].[' @ObjDBName '].[dbo].[' @ObjTbName '] where id=@pkc1 '
if @IsSp_MSupd = 1
begin
set @TempText='--if @@rowcount = 0' CHAR(10)
'-- if @@microsoftversion>0x07320000' CHAR(10)
'-- exec sp_MSreplraiserror 20598' CHAR(10)
'end ' CHAR(10)
'end ' CHAR(10)
'if @@rowcount = 0' CHAR(10)
'begin' CHAR(10)
@SqlText CHAR(10)
'end '
select @TempText
end
else
begin
select @SqlText
end
end
就算上边封装的积累进度能够很便利的转换修改订阅存款和储蓄进度的某些修改语句，可是假诺系统的表很多，前段时间还不曾水到渠成批量的整套改换这个订阅存储进度，借使有一种方法及时通告DBA 哪些订阅数据出现了难点，然后再依据前边的不二等秘书技解决难点，就很方便了。那么些成效，正是上边说的方法。
节点切换监察和控制
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
SQL邮件监察和控制订阅错误
SQL邮件提供了蹲点数据库各个性能，难题，警示，然后发邮件通告管理员的功能，大家也能够采纳这几个意义，当订阅库发生多少同步错误，发一封邮件及时通报管理员，而不用实时去盯着“复制监视器”，查看难点了。
- 第一在“服务器”-管理-数据库邮件节点上，配置二个数据库邮件账号，具体经过略，请参见其他相关小说；
- 下一场，在Sql Server 代理-操作员效能上，增加多个操作员，填写上通报该操作员的电子邮件账号；
- 最终，在Sql Server 代理-作业节点，选取用于订阅的功课名称，然后张开属性窗口，进行如下设置：
如图填写上贰个妥贴的重试次数，默许那是叁个相当的大的数字，所以会重试比较久都不会时有产生难题邮件。该问题小编查找了相当久才发掘，我们不要走弯路了。
通过那样的计划之后，出现订阅同步难点，会收到大约如下的邮件内容：
作业运行: “DNXSQL-HRDB-XX发布-DNXSQL1-HRDB-3D57B9A6-207B-486A-8B5D-41125B68A876”已在 2016/7/22 14:00:46 运行
持续时间: 0 小时，8 分钟，55 秒
状态: 失败
消息: 该作业失败。 用户 sa 调用了该作业。最后运行的是步骤 1 (运行代理。)。.
选拔该邮件后，去服务器依据后边介绍的章程，解决此主题材料就可以。
迄今截至，DBA能够放心去干别的政工了。
（注：本文是三个业余DBA奋战N多天，不断尝试总计，数10回修正本文而成，转发请评释作者，并迎接使用SOD开垦框架，它的数据库工具将会提供自动生成修改的订阅存储进度的效能。）
补充：
若果订阅库少了几许记录，能够通过下边好像的查询化解：
update [MainSqlServer].[XXDB].[dbo].TB_Appropriation set ModifiedOn=GETDATE () where ID in
(
SELECT ID FROM [MainSqlServer].[XXDB].[dbo].TB_Appropriation where id not in (
SELECT ID FROM [XXDB].[dbo].TB_Appropriation
)
)
里头，MainSqlServer是发布服务器对应的链接服务器名称，要是要填补缺点和失误数据的表有三个ModifiedOn 字段。
节点切换作业调整
作业能够动用手动调节或如下脚本，也足以修改作业在作业施行前增添节点决断
--------------------------判断当前节点是否为主节点 如果不是则禁用作业 -------
------------节点 切换为主节点则启用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 '
本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处：内附脚本，使用SQLServer同义词和SQL邮件
TAG标签：
