内附脚本,使用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,创设的时候,供给内定同义词所在的服务器名称,数据库名称,架构,表名称等消息。

图片 1

然而此时同义词还不可能一贯运用,还索要创设“链接服务器”,具体进程如下:

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';

由此这么的办法,非常低价的把公布库的数量就补偿到订阅库上了,之后,数据库的公布订阅错误就消除了。

陈设操作员

  操作员首假诺用于作业的通报对象:

  图片 2

  

  配置如下:

  图片 3

  

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

 

 

  注 :操作员可依照是或不是在作业成功或失利时通报,后续脚本均未配备操作员,如需陈设可在学业属性中自行增添 

  图片 4

 

修改订阅仓库储存款和储蓄进程

 可是,假若这么的谬误相当多,每一遍都去靠手工修补数据是不行的,所以大家还需求找到订阅库上的体系存款和储蓄进程,做相应的修改。

  •   修改数据,对应的囤积进程名字是 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 代理-作业节点,选取用于订阅的功课名称,然后张开属性窗口,进行如下设置:

图片 5

如图填写上贰个妥贴的重试次数,默许那是叁个相当的大的数字,所以会重试比较久都不会时有产生难题邮件。该问题小编查找了相当久才发掘,我们不要走弯路了。

通过那样的计划之后,出现订阅同步难点,会收到大约如下的邮件内容:

作业运行:    “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标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。