当前位置：Gxlcms > 数据库问题 > 关于SQL Server 数据库归档的一些思考和改进

关于SQL Server 数据库归档的一些思考和改进

时间：2021-07-01 10:21:17 帮助过：11人阅读

TABLE [dbo].[DBData_ArchiveConfig]( [ID] [int] IDENTITY(1,1) NOT NULL, [IP] [varchar](50) NULL, [DBName] [varchar](50) NULL, [DataTable] [varchar](50) NULL, [TargetIP] [varchar](50) NULL, [TargetDB] [varchar](50) NULL, [TargetTable] [varchar](50) NULL, [Prerequisite] [varchar](300) NULL, [DelMaxQTY] [int] NULL, [IsCheckOrderID] [int] NULL, [SP_Name] [int] NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘Server IP(数据位于中央管理器中，所以归档数据库库所在的IP要维护，可维修虚拟的IP)‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘IP‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘要归档的数据库‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘DBName‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘要归档的表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘DataTable‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘备份指向的IP‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘TargetIP‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘备份指向的数据库‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘TargetDB‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘备份指向的表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘TargetTable‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘归档条件‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘Prerequisite‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘循环中一次归档删除的数据量‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘DelMaxQTY‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘此为 备用字段，考虑可能有些表，会和其他表关联‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘IsCheckOrderID‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘为提高并发度，一个DB对应的归档SP可能是多个，通过此列，进行分组。‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘SP_Name‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘此为拓展字段，原计划根据 开始时间、结束时间，每天可以多个时间段内执行‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘StartTime‘ GO EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘此为拓展字段，原计划根据 开始时间、结束时间，每天可以多个时间段内执行‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘DBData_ArchiveConfig‘, @level2type=N‘COLUMN‘,@level2name=N‘EndTime‘ GO

2.2 归档运行的Log表

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">DBData_ArchiveLog</span><span style="color: #ff0000">]</span><span style="color: #000000">(
  2.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">ID</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">int</span><span style="color: #ff0000">]</span> <span style="color: #ff00ff">IDENTITY</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #800000; font-weight: bold">1</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  3.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">IP</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  4.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DBName</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  5.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DataTable</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">80</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  6.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">BakQTY</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  7.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">BakStartDate</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">datetime</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  8.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">BakEndDate</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">datetime</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  9. ) </span><span style="color: #0000ff">ON</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">PRIMARY</span><span style="color: #ff0000">]</span>
  10. <span style="color: #0000ff">GO</span><span style="color: #0000ff"><br></span>

2.3 异常错误信息表

执行的过程中会外包一层 try...catch,将操作过程中的错误信息保存在表 DBData_ArchiveErrLog。表结构如下：

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">DBData_ArchiveErrLog</span><span style="color: #ff0000">]</span><span style="color: #000000">(
  2.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">ID</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">int</span><span style="color: #ff0000">]</span> <span style="color: #ff00ff">IDENTITY</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #800000; font-weight: bold">1</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  3.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">IP</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  4.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DBName</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">60</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  5.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DataTable</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">80</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  6.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TargetIP</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  7.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TargetDB</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">60</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  8.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TargetTable</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">80</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  9.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Errormsg</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">nvarchar</span><span style="color: #ff0000">]</span>(<span style="color: #ff00ff">max</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  10.     </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TransDateTime</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">
  11. ) </span><span style="color: #0000ff">ON</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">PRIMARY</span><span style="color: #ff0000">]</span> TEXTIMAGE_ON <span style="color: #ff0000">[</span><span style="color: #ff0000">PRIMARY</span><span style="color: #ff0000">]</span>
  12. <span style="color: #0000ff">GO</span>

四. 存储过程相应的主要代码

  1. <span style="color: #0000ff">SET</span> ANSI_NULLS <span style="color: #0000ff">ON</span>
  2. <span style="color: #0000ff">GO</span>
  3. <span style="color: #0000ff">SET</span> QUOTED_IDENTIFIER <span style="color: #0000ff">ON</span>
  4. <span style="color: #0000ff">GO</span>
  5. <span style="color: #008080">--</span><span style="color: #008080"> =============================================</span><span style="color: #008080">
  6. --</span><span style="color: #008080"> Author:        <Author,,Name></span><span style="color: #008080">
  7. --</span><span style="color: #008080"> Create date: <Create Date,,></span><span style="color: #008080">
  8. --</span><span style="color: #008080"> Description:    <Description,,></span><span style="color: #008080">
  9. --</span><span style="color: #008080"> =============================================</span>
  10. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">PROCEDURE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">SP_XXXXX_DataArchive</span><span style="color: #ff0000">]</span>
  11. <span style="color: #0000ff">AS</span>
  12.     <span style="color: #0000ff">SET</span> NOCOUNT <span style="color: #0000ff">ON</span><span style="color: #000000">;
  13.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql1</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  14.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  15.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql2</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
  16.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@IP</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  17.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@DBName</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  18.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@DataTable</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  19.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@TargetIP</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  20.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@TargetDB</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  21.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@TargetTable</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  22.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Prerequisite</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">) 
  23.     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@DelMaxQTY</span> <span style="color: #0000ff">INT</span>
  24.     <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@StartTime</span> <span style="color: #0000ff">DATETIME</span>
  25.     <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@EndTime</span> <span style="color: #0000ff">DATETIME</span>
  26.     <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@qty</span> <span style="color: #0000ff">INT</span> 
  27.     <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@ISCHECKORDERID</span> <span style="color: #0000ff">INT</span> 
  28. <span style="color: #008080">--</span><span style="color: #008080">--Carson   2018-12-17 备份数据的时间往往比删除的时间长3倍,因此,如果考虑将备份的操作转移到辅助库,将会对线上的操作影响降至更低</span>
  29.     <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@BakDateIP</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">30</span><span style="color: #000000">)  
  30.     </span><span style="color: #0000ff">set</span> <span style="color: #008000">@BakDateIP</span><span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">[XXX.XXX.XXX.XXX].</span><span style="color: #ff0000">‘</span><span style="color: #008080">--</span><span style="color: #008080">-----后面一定要有一个点</span><span style="color: #008080">
  31. --</span><span style="color: #008080">------------------------------------------------归档操作---------------------------------</span>
  32.     <span style="color: #0000ff">DECLARE</span> DBName <span style="color: #0000ff">CURSOR</span>
  33.     <span style="color: #0000ff">FOR</span>
  34.         <span style="color: #0000ff">SELECT</span><span style="color: #000000">  IP ,
  35.                 DBName ,
  36.                 DataTable ,
  37.                 TargetIP ,
  38.                 TargetDB ,
  39.                 TargetTable ,
  40.                 Prerequisite ,
  41.                 DelMaxQTY ,
  42.                 ISCHECKORDERID ,
  43.                 StartTime ,
  44.                 EndTime
  45.         </span><span style="color: #0000ff">FROM</span>    <span style="color: #ff0000">[</span><span style="color: #ff0000">中央管理器</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">中央管理数据库</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">DBData_ArchiveConfig</span><span style="color: #ff0000">]</span>
  46.         <span style="color: #0000ff">WHERE</span>   DataTable <span style="color: #808080"><></span> <span style="color: #ff0000">‘‘</span>
  47.                 <span style="color: #808080">AND</span> TargetTable <span style="color: #808080"><></span> <span style="color: #ff0000">‘‘</span>
  48.                 <span style="color: #808080">AND</span> DBNAME <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">XXXXXXXXX</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> SP_Name<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">?????</span><span style="color: #ff0000">‘</span>
  49.     <span style="color: #0000ff">OPEN</span><span style="color: #000000"> DBName    
  50.     </span><span style="color: #0000ff">FETCH</span> <span style="color: #0000ff">NEXT</span> <span style="color: #0000ff">FROM</span> DBName <span style="color: #0000ff">INTO</span> <span style="color: #008000">@IP</span>, <span style="color: #008000">@DBName</span>, <span style="color: #008000">@DataTable</span>, <span style="color: #008000">@TargetIP</span>, <span style="color: #008000">@TargetDB</span><span style="color: #000000">,
  51.         </span><span style="color: #008000">@TargetTable</span>, <span style="color: #008000">@Prerequisite</span>, <span style="color: #008000">@DelMaxQTY</span>, <span style="color: #008000">@ISCHECKORDERID</span><span style="color: #000000">,
  52.         </span><span style="color: #008000">@StartTime</span>, <span style="color: #008000">@EndTime</span>   
  53.     <span style="color: #0000ff">WHILE</span> ( <span style="color: #008000; font-weight: bold">@@fetch_status</span> <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span><span style="color: #000000"> )
  54.     </span><span style="color: #0000ff">BEGIN</span>  
  55.         <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@datetime</span> <span style="color: #0000ff">DATETIME</span>
  56.         <span style="color: #0000ff">IF</span> <span style="color: #008000">@ISCHECKORDERID</span> <span style="color: #808080"><></span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span>  <span style="color: #808080">AND</span> <span style="color: #008000">@DataTable</span> <span style="color: #808080"><></span> <span style="color: #ff0000">‘‘</span>
  57.         <span style="color: #0000ff">BEGIN</span>
  58.             <span style="color: #0000ff">SET</span> <span style="color: #008000">@datetime</span> <span style="color: #808080">=</span> <span style="color: #ff00ff">CONVERT</span>(<span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>), <span style="color: #ff00ff">GETDATE</span>() <span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">30</span>, <span style="color: #800000; font-weight: bold">120</span><span style="color: #000000">)                
  59.             </span><span style="color: #0000ff">SET</span> <span style="color: #008000">@sql</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Insert into [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@TargetIP</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">].</span><span style="color: #ff0000">‘</span>
  60.                 <span style="color: #808080">+</span> <span style="color: #008000">@TargetDB</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@TargetTable</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
  61.                  select * FROM </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@BakDateIP</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DataTable</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> 
  62.                  with(nolock) where </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@Prerequisite</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘</span>
  63.                 
  64.             <span style="color: #0000ff">SET</span> <span style="color: #008000">@sql1</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">DECLARE @icount INTEGER  
  65.                         SELECT @icount = COUNT(1)  
  66.                         FROM </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@BakDateIP</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DataTable</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
  67.                         where </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@Prerequisite</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">  
  68.                         insert into [中央管理器].[中央管理数据库].dbo.DBData_ArchiveLog (IP, DBName, DataTable, BakQTY, BakStartDate, BakEndDate)
  69.                         select </span><span style="color: #ff0000">‘‘‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@IP</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘‘‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘‘‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DataTable</span>
  70.                                     <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000">,@icount,getdate(),null
  71.                         WHILE @icount > 0   
  72.                         BEGIN  
  73.                     
  74.                             DELETE TOP (</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@DelMaxQTY</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)  
  75.                             FROM </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DataTable</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> 
  76.                             where </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@Prerequisite</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
  77.       
  78.                             SET @icount = @icount -(</span><span style="color: #ff0000">‘</span>
  79.                                     <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@DelMaxQTY</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)  
  80.                             WAITFOR DELAY </span><span style="color: #ff0000">‘‘</span><span style="color: #ff0000">00:00:01</span><span style="color: #ff0000">‘‘</span><span style="color: #ff0000">  
  81.                         END  </span><span style="color: #ff0000">‘</span>                    
  82.           <span style="color: #0000ff">BEGIN</span><span style="color: #000000"> TRY
  83.             </span><span style="color: #0000ff">EXEC</span> (<span style="color: #008000">@sql</span><span style="color: #000000">)
  84.             </span><span style="color: #0000ff">EXEC</span> (<span style="color: #008000">@sql1</span><span style="color: #000000">) 
  85.           </span><span style="color: #0000ff">END</span><span style="color: #000000"> TRY
  86.           </span><span style="color: #0000ff">BEGIN</span><span style="color: #000000"> CATCH
  87.              </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Errmsg</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">nvarchar</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
  88.              </span><span style="color: #0000ff">SELECT</span> <span style="color: #008000">@Errmsg</span><span style="color: #808080">=</span><span style="color: #000000">ERROR_MESSAGE()
  89.                </span><span style="color: #008080">--</span><span style="color: #008080">----0001 BEGIN SAVE ERR LOG IN TABLE</span>
  90.                <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">中央管理器</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">中央管理数据库</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.DBData_ArchiveErrLog  (<span style="color: #ff0000">[</span><span style="color: #ff0000">IP</span><span style="color: #ff0000">]</span> ,<span style="color: #ff0000">[</span><span style="color: #ff0000">DBName</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">DataTable</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">TargetIP</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">TargetDB</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">TargetTable</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">Errormsg</span><span style="color: #ff0000">]</span> ,<span style="color: #ff0000">[</span><span style="color: #ff0000">TransDateTime</span><span style="color: #ff0000">]</span><span style="color: #000000">)
  91.                </span><span style="color: #0000ff">VALUES</span>(<span style="color: #008000">@IP</span>, <span style="color: #008000">@DBName</span>, <span style="color: #008000">@DataTable</span>, <span style="color: #008000">@TargetIP</span>, <span style="color: #008000">@TargetDB</span>, <span style="color: #008000">@TargetTable</span>,<span style="color: #008000">@Errmsg</span>,<span style="color: #ff00ff">convert</span>(<span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">25</span>),<span style="color: #ff00ff">GETDATE</span>(), <span style="color: #800000; font-weight: bold">120</span><span style="color: #000000">))       
  92.                </span><span style="color: #008080">--</span><span style="color: #008080">----0001 END</span>
  93.                 <span style="color: #008080">--</span><span style="color: #008080">-----------0002 BEGIN SEND EMAIL MESSAGE----------------              </span>
  94.                     <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Subject</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">nvarchar</span>(<span style="color: #800000; font-weight: bold">200</span><span style="color: #000000">)
  95.                     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Body</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">nvarchar</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
  96.                     </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@SPName</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">nvarchar</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
  97.             
  98.                     </span><span style="color: #0000ff">SET</span> <span style="color: #008000">@Subject</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">数据库归档异常 -重要!;ServerIP:</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@IP</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> DB:</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span>
  99.                                 <span style="color: #0000ff">SET</span> <span style="color: #008000">@SPName</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘‘</span>
  100.                                 <span style="color: #0000ff">SET</span> <span style="color: #008000">@Body</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"><html><body>Dear All,<br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ServerIP:</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span><span style="color: #008000">@IP</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ; DataBase:</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span><span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">上的Table归档异常,请及时检查!!!
  101.                                <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;You can get detail information from the table. <br><br><table border=1 bgcolor=#aaff11></span><span style="color: #ff0000">‘</span> 
  102.                                 <span style="color: #0000ff">SET</span> <span style="color: #008000">@Body</span> <span style="color: #808080">=</span> <span style="color: #008000">@Body</span><span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"><tr bgcolor=#ff3311><td>ServerIP</td><td>DBName</td><td>TableName</td><td>TargetIP</td><td>TargetDB</td><td>Errmsg</td><td>TransDateTime</td></tr></span><span style="color: #ff0000">‘</span>
  103.                                 <span style="color: #0000ff">SELECT</span>  <span style="color: #008000">@SPName</span> <span style="color: #808080">=</span> <span style="color: #008000">@SPName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"><tr bgcolor=#ffaa11><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@IP</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">50</span>))<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@DBName</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">50</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@DataTable</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">50</span>))<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td>
  104.                                 <td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@TargetIP</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span>))<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@TargetDB</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">50</span>))<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">SUBSTRING</span>(<span style="color: #008000">@Errmsg</span>,<span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">100</span>)<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">CONVERT</span>(<span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">100</span>), <span style="color: #ff00ff">GETDATE</span>(), <span style="color: #800000; font-weight: bold">21</span>)<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td></tr></span><span style="color: #ff0000">‘</span>
  105.                                 <span style="color: #0000ff">SET</span> <span style="color: #008000">@Body</span> <span style="color: #808080">=</span> <span style="color: #008000">@Body</span> <span style="color: #808080">+</span> <span style="color: #008000">@SPName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></table></span><span style="color: #ff0000">‘</span>
  106.                     <span style="color: #0000ff">SET</span> <span style="color: #008000">@BODY</span><span style="color: #808080">=</span><span style="color: #ff00ff">REPLACE</span>(<span style="color: #008000">@BODY</span>,<span style="color: #ff0000">‘‘‘‘</span>,<span style="color: #ff0000">‘‘</span><span style="color: #000000">)
  107.                     </span><span style="color: #0000ff">IF</span> <span style="color: #ff00ff">REPLACE</span>(<span style="color: #008000">@BODY</span>,<span style="color: #ff0000">‘</span> <span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘‘</span>)<span style="color: #808080"><></span><span style="color: #ff0000">‘‘</span>
  108.                         <span style="color: #0000ff">BEGIN</span>
  109.                             <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@AllEmailToAddress</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">3000</span>)<span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
  110.                             <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@AllEmailCcAddress</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">3000</span>)<span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
  111.                             <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Allprofile_name</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">100</span>)<span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
  112.                             <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@AllEmailToAddress</span><span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
  113.                             <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@AllEmailCcAddress</span><span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
  114.                             <span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">TOP</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #008000">@Allprofile_name</span><span style="color: #808080">=</span>NAME <span style="color: #0000ff">FROM</span><span style="color: #000000"> msdb.dbo.sysmail_profile 
  115.                             </span><span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span><span style="color: #000000"> profile_id
  116.                             </span><span style="color: #0000ff">EXEC</span> msdb..sp_send_dbmail <span style="color: #008000">@profile_name</span> <span style="color: #808080">=</span> <span style="color: #008000">@Allprofile_name</span>   <span style="color: #008080">--</span><span style="color: #008080"> profile 名称 </span>
  117.                              ,<span style="color: #008000">@recipients</span>   <span style="color: #808080">=</span>  <span style="color: #008000">@AllEmailToAddress</span>        <span style="color: #008080">--</span><span style="color: #008080"> 收件人邮箱 </span>
  118.                              ,<span style="color: #008000">@copy_recipients</span><span style="color: #808080">=</span><span style="color: #008000">@AllEmailCcAddress</span><span style="color: #000000">
  119.                              ,</span><span style="color: #008000">@subject</span>      <span style="color: #808080">=</span>  <span style="color: #008000">@Subject</span>                  <span style="color: #008080">--</span><span style="color: #008080"> 邮件标题 </span>
  120.

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！