当前位置: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=NMS_Description, @value=NServer IP(数据位于中央管理器中,所以归档数据库库所在的IP要维护,可维修虚拟的IP) , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NIP GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N要归档的数据库 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NDBName GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N要归档的表 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NDataTable GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N备份指向的IP , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NTargetIP GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N备份指向的数据库 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NTargetDB GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N备份指向的表 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NTargetTable GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N归档条件 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NPrerequisite GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N循环中一次归档删除的数据量 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NDelMaxQTY GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N此为 备用字段,考虑可能有些表,会和其他表关联 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NIsCheckOrderID GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N为提高并发度,一个DB对应的归档SP可能是多个,通过此列,进行分组。 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NSP_Name GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N此为拓展字段,原计划根据 开始时间、结束时间,每天可以多个时间段内执行 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NStartTime GO EXEC sys.sp_addextendedproperty @name=NMS_Description, @value=N此为拓展字段,原计划根据 开始时间、结束时间,每天可以多个时间段内执行 , @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDBData_ArchiveConfig, @level2type=NCOLUMN,@level2name=NEndTime 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. <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
  64. SELECT @icount = COUNT(1)
  65. 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">
  66. 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">
  67. insert into [中央管理器].[中央管理数据库].dbo.DBData_ArchiveLog (IP, DBName, DataTable, BakQTY, BakStartDate, BakEndDate)
  68. 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>
  69. <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000">,@icount,getdate(),null
  70. WHILE @icount > 0
  71. BEGIN
  72. 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">)
  73. 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">
  74. 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">
  75. SET @icount = @icount -(</span><span style="color: #ff0000">‘</span>
  76. <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">)
  77. 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">
  78. END </span><span style="color: #ff0000">‘</span>
  79. <span style="color: #0000ff">BEGIN</span><span style="color: #000000"> TRY
  80. </span><span style="color: #0000ff">EXEC</span> (<span style="color: #008000">@sql</span><span style="color: #000000">)
  81. </span><span style="color: #0000ff">EXEC</span> (<span style="color: #008000">@sql1</span><span style="color: #000000">)
  82. </span><span style="color: #0000ff">END</span><span style="color: #000000"> TRY
  83. </span><span style="color: #0000ff">BEGIN</span><span style="color: #000000"> CATCH
  84. </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">)
  85. </span><span style="color: #0000ff">SELECT</span> <span style="color: #008000">@Errmsg</span><span style="color: #808080">=</span><span style="color: #000000">ERROR_MESSAGE()
  86. </span><span style="color: #008080">--</span><span style="color: #008080">----0001 BEGIN SAVE ERR LOG IN TABLE</span>
  87. <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">)
  88. </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">))
  89. </span><span style="color: #008080">--</span><span style="color: #008080">----0001 END</span>
  90. <span style="color: #008080">--</span><span style="color: #008080">-----------0002 BEGIN SEND EMAIL MESSAGE---------------- </span>
  91. <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">)
  92. </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">)
  93. </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">)
  94. </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>
  95. <span style="color: #0000ff">SET</span> <span style="color: #008000">@SPName</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘‘</span>
  96. <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归档异常,请及时检查!!!
  97. <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>
  98. <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>
  99. <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>
  100. <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>
  101. <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>
  102. <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">)
  103. </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>
  104. <span style="color: #0000ff">BEGIN</span>
  105. <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>
  106. <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>
  107. <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>
  108. <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@AllEmailToAddress</span><span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
  109. <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@AllEmailCcAddress</span><span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
  110. <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
  111. </span><span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span><span style="color: #000000"> profile_id
  112. </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>
  113. ,<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>
  114. ,<span style="color: #008000">@copy_recipients</span><span style="color: #808080">=</span><span style="color: #008000">@AllEmailCcAddress</span><span style="color: #000000">
  115. ,</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>

人气教程排行