sqlserver 数据库数据迁移和自动备份

       随着平台监控的硬件设备越来越多，每天采集器采集的数据越来越多，预估每天数据库日志表中要插入300万条数据，  现在只有主备数据库服务器两台，平时工作的只有主数据库，所以数据量增大，给数据库造成了巨大的压力，必须要对主数据库进行分割，于是我决定把主数据分成原始日志数据库和业务数据库，分别用两台服务器存放，这样日志数据库只负责收集原始日志，业务数据库负责业务，平台就不会太卡了，用户体验就会变的很好了。

                                     

上图就是分割数据库的大概思路了，业务数据库服务器会保留近三个月的数据库原始日志，因为用户需要查看原始日志，来查看设备的健康状况。日志数据库会建一个作业，每隔一小时就会取上一个小时的数据。放到日志数据库中。由于原始日志数据量太大，为了方便备份，于是就一个月自动建一个数据库然后自动在数据库下建一个日志表 ，然后自动把上一个月的日志也就是上月建的的那个日志数据库自动备份。

 

 用户一般都会查看近期原始日志数据，但是为了满足用户能够查看任意时间日志。于是就定义了原始日志查看机制。

     1，如果用户查看近期日志，那就直接调取业务数据库日志。

     2，如果用户要查看很久以前的日志。

          1）调取日志数据库服务器中的日志。（已经做好跨月，跨年获取日志的机制了）

 

 

          2）直接从ES中调取日志。（ELK日志监控系统，前面以前说过日志监控系统 ：https://www.cnblogs.com/zpy1993-09/tag/ELK/）

 

 下面就是数据库定时程序了。

    迁移数据时最好建两个记录表。

    1，用来记录建库，建表的记录。

 

 

    2，用来记录每次迁移数据的记录。

     

 

 有了这两个记录表我们可以做一个一个后期补操作，就是通过记录表来检查哪一段时间定时程序执行失败，然后重新补数据。

   定时程序：

   

USE [SmartNetN]
GO
/****** Object:  StoredProcedure [dbo].[Sys_InserLog]    Script Date: 2020/9/28 12:00:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sys_InserLog]
AS 

declare @iscreate int
set @iscreate=0;

 --创建数据库和数据表
   ---------------------------------------------------------------------------------------------------------------------------------------------
  declare
    @createsql nvarchar(max),
    @createdate nvarchar(max),
    @sql nvarchar(max)
   if (select COUNT(*) from Sys_TableLog  where DataName='Data'+CONVERT(varchar(6), GETDATE(), 112) )=0
   begin
   set @iscreate=1
    set @createdate='CREATE DATABASE Data'+CONVERT(varchar(6), GETDATE(), 112)+'
ON PRIMARY
(
    name="Date'+CONVERT(varchar(6), GETDATE(), 112)+'", 
    filename="D:\Sql2012\MSSQL11.MSSQLSERVER\MSSQL\DATA\DATA'+CONVERT(varchar(6), GETDATE(), 112)+'.mdf",
    size=5mb,
    maxsize=10240mb,
    filegrowth=15%  
)
log on
( 
    name="mydb2_log",
    filename="D:\Sql2012\MSSQL11.MSSQLSERVER\MSSQL\DATA\DATA'+CONVERT(varchar(6), GETDATE(), 112)+'_log.ldf",
    size=2mb,
    filegrowth=1mb 
)'
 exec(@createdate)
-----------------------------------------------------------
 
set @createsql='use Data'+CONVERT(varchar(6), GETDATE(), 112)+' CREATE TABLE poweritem'+CONVERT(varchar(6), GETDATE(), 112)+'(
    [JZ_ID] [int] NULL,
    [DB_ID] [int] NULL,
    [AddTime] [datetime] NULL,
    [Channel] [int] NOT NULL,
    [P1] [decimal](18, 2) NOT NULL,
    [P2] [decimal](18, 1) NOT NULL,
    [P3] [decimal](18, 1) NOT NULL,
    [P4] [decimal](18, 1) NOT NULL,
    [P5] [decimal](18, 1) NOT NULL,
    [P6] [decimal](18, 1) NOT NULL,
    [P7] [decimal](18, 1) NOT NULL,
    [P8] [decimal](18, 1) NOT NULL,
    [P9] [decimal](18, 1) NOT NULL,
    [P10] [decimal](18, 1) NOT NULL,
    [P11] [decimal](18, 1) NOT NULL,
    [P12] [decimal](18, 1) NOT NULL,
    [P13] [decimal](18, 3) NOT NULL,
    [P14] [decimal](18, 3) NOT NULL,
    [P15] [decimal](18, 3) NOT NULL,
    [P16] [decimal](18, 3) NOT NULL,
    [P17] [decimal](18, 3) NOT NULL,
    [P18] [decimal](18, 4) NOT NULL,
    [P19] [decimal](18, 4) NOT NULL,
    [P20] [decimal](18, 4) NOT NULL,
    [P21] [decimal](18, 4) NOT NULL,
    [P22] [decimal](18, 4) NOT NULL,
    [P23] [decimal](18, 4) NOT NULL,
    [P24] [decimal](18, 4) NOT NULL,
    [P25] [decimal](18, 4) NOT NULL,
    [P26] [decimal](18, 4) NOT NULL,
    [P27] [decimal](18, 4) NOT NULL,
    [P28] [decimal](18, 4) NOT NULL,
    [P29] [decimal](18, 4) NOT NULL,
    [P30] [decimal](18, 3) NOT NULL,
    [P31] [decimal](18, 3) NOT NULL,
    [P32] [decimal](18, 3) NOT NULL,
    [P33] [decimal](18, 3) NOT NULL,
    [P34] [decimal](18, 2) NOT NULL,
    [P35] [decimal](18, 2) NOT NULL,
    [P36] [decimal](18, 2) NOT NULL,
    [P37] [decimal](18, 2) NOT NULL,
    [P38] [decimal](18, 2) NOT NULL,
    [P39] [decimal](18, 2) NOT NULL,
    [P40] [decimal](18, 2) NOT NULL,
    [P41] [decimal](18, 2) NOT NULL,
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [SB_CODE] [varchar](30) NULL,
    [SBD_ID] [int] NULL,
    [SBD_BeiLv] [int] NULL,
    [SBT_ID] [int] NULL
) ON [PRIMARY]'
 exec(@createsql)

 insert into Sys_TableLog(DataName,TableName,AddTime)values('Data'+CONVERT(varchar(6), GETDATE(), 112),'poweritem'+CONVERT(varchar(6), GETDATE(), 112),GETDATE())
   end
   -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   --跨数据库读取数据，并插入
   ------------------------------------------------------------------------------------------------------------------------
             declare 
                 @starttime varchar(30),
                 @endtime varchar(30)
                 set @starttime=YEAR(getdate())
                  if DATEPART(hour,DATEADD(HOUR,0,GETDATE())) <10
                      set @endtime=  CONVERT(varchar(10),getdate(),120)+' 0'+cast(DATEPART(hour,DATEADD(HOUR,0,GETDATE()))as varchar)+':00:00.000'
                   else
                     set @endtime=  CONVERT(varchar(10),getdate(),120)+' '+cast(DATEPART(hour,DATEADD(HOUR,0,GETDATE()))as varchar)+':00:00.000'
                  if DATEPART(hour,DATEADD(HOUR,-1,GETDATE())) <10
                      set @starttime=  CONVERT(varchar(10),getdate(),120)+' 0'+cast(DATEPART(hour,DATEADD(HOUR,-1,GETDATE()))as varchar)+':00:00.000'
                   else
                     set @starttime=CONVERT(varchar(10),getdate(),120)+' '+cast(DATEPART(hour,DATEADD(HOUR,-1,GETDATE()))as varchar)+':00:00.000'
                 
             set @sql='insert into [Data'+CONVERT(varchar(6), DATEADD(HOUR,-1,GETDATE()), 112)+'].[dbo].[poweritem'+CONVERT(varchar(6), GETDATE(), 112)+'] SELECT [JZ_ID],[DB_ID],[AddTime],[Channel],[P1],[P2],[P3],[P4],[P5],[P6],[P7],[P8],[P9],[P10],[P11],[P12],[P13],[P14],[P15],[P16],[P17],[P18],[P19],[P20],[P21],[P22],[P23],[P24],[P25],[P26],[P27],[P28],[P29],[P30],[P31],[P32],[P33],[P34],[P35],[P36],[P37] ,[P38],[P39],[P40],[P41],[SB_CODE],[SBD_ID],[SBD_BeiLv],[SBT_ID] FROM  SIQISmartNet.[SmartNet].[dbo].Power_Item where AddTime >= cast('''+ @starttime+''' as datetime) and AddTime < cast('''+ @endtime+''' as datetime) '
             exec(@sql)
        insert into Sys_InsertItem_Log([TableName],[StartTime],[EndTime],[AddTime])values(CONVERT(varchar(7), GETDATE(), 120),@starttime,@endtime,GETDATE())
-----  - -------------------------------------------------------------------------------------------------------------------------------
 
--自动备份上个月的数据
-----------------------------------------------------------------------------------------------------------------------------------------------
 if (select COUNT(*) from Sys_TableLog  where DataName='Data'+CONVERT(varchar(6),  DATEADD(month,-1,getdate()), 112) )>0 and @iscreate=1
 begin
 DECLARE @name varchar(50)
DECLARE @datetime char(14)
DECLARE @path varchar(255)
DECLARE @bakfile varchar(255)
set @name='Data'+CONVERT(varchar(6),  DATEADD(month,-1,getdate()), 112)
set @datetime=CONVERT(char(8),getdate(),112) + REPLACE(CONVERT(char(8),getdate(),108),':','')
set @path='D:\DataBak'
set @bakfile=@path+'\'+'Bak_'+@datetime+'_'+ @name
backup database @name to disk=@bakfile with name=@name
end

尽管还有些不足，但饭要一口一口吃，路要一步一步走，后期会慢慢优化。