SQL Server数据库实现表数据的定期导出与查询还原

本文以对数据库表tb_TraceData做处理举例；

时间关系梳理举例：假如今天6月9日，数据要求保存近一周7天的（即2号--8号），1）中需要导出为txt文件的数据时间范围是1号0点--2号0点，3）中需要清理的数据范围是时间<1号0点；

存储过程编写好之后，设置定时执行作业执行存储过程即可。

-----------------------------------------以下为存储过程示例代码------------------------------------------------------------

1）定时导出数据为txt文件用到的存储过程建立

CREATE PROCEDURE [dbo].[usp_gather_ExportTxt] 
    
AS
BEGIN
        

        declare @csum int --转存的条数

        declare @filename varchar(100)--文件名
        set @filename='健康数据库'+ REPLACE(REPLACE(convert(varchar(10),dateadd(day,-32,GETDATE()),121),':','：'),' ','_')+'.txt';

        declare @sql varchar(500)
        set @sql= 'EXEC master..xp_cmdshell ''bcp "select * from 数据库名.[dbo].[tb_TraceData]  where packagetime>=convert(varchar(10),dateadd(d,-32,getDate()),120) and packagetime<=convert(varchar(10),dateadd(d,-31,getDate()),120)" queryout D:\数据存储\'+@filename+' -c -q -S"192.168.1.000" -U"sa" -P"20162016"'''
        begin try
            execute(@sql)

            --导出文件信息入表
            select @csum=count(*) from 数据库名.[dbo].[tb_TraceData] where convert(varchar(10),InsertTime,120)=convert(varchar(10),dateadd(day,-32,getdate()),120)
            insert into tb_gatherHealtSignFile(filenames,fileAddress,belongDate,packageSum) values(@filename,'D:\数据存储\',convert(varchar(10),dateadd(day,-32,getdate()),120),@csum)
        end try
        begin catch
            --异常记录
            insert into tb_sys_OperateLog(oplo_level,oplo_page,oplo_event,oplo_describe) 
            values('802','usp_gather_ExportTxt','导出txt',ERROR_MESSAGE())
        end catch

        --清理已经导出为txt文件的数据
        delete from 数据库名.[dbo].[tb_TraceData] where convert(varchar(10),PackageTime,120)=convert(varchar(10),dateadd(day,-32,getdate()),120)
END
GO

2）将txt文件中的数据还原存储过程建立

CREATE PROCEDURE [dbo].[usp_gather_ImportTxt] 
      @startTime varchar(20),
    @endTime varchar(20)
AS
BEGIN

  declare @filenames varchar(50),@fileAddress varchar(200),@belongDate varchar(30)

        declare @fileInfor varchar(200) --文件完整地址
        declare @toCount int --统计位，判断是否已经导出了

        declare save_cur cursor for --定义游标
        select filenames,fileAddress,belongDate from tb_gatherHealtSignFile where belongDate between ''+@startTime+'' and ''+@endTime+''

        open save_cur --打开游标
        fetch next from save_cur into @filenames,@fileAddress,@belongDate
        while (@@fetch_status=0)
            begin
                select @toCount=count(*) from tb_TraceData where convert(varchar(10),PackageTime,120)=@belongDate
                if @toCount=0 --校验，不可重复导入
                    begin
                        set @fileInfor=@fileAddress+@filenames
                        declare @sql varchar(500)
                        set @sql= 'EXEC master..xp_cmdshell ''bcp "[数据库名].[dbo].[tb_TraceData]" in '+@fileInfor+' -c -T -S"192.168.200.200" -U"sa" -P"Aotto2016"'''
                        begin try
                            execute(@sql)
                        end try
                        begin catch
                            --异常记录
                            insert into tb_sys_OperateLog(oplo_level,oplo_page,oplo_event,oplo_describe) 
                            values('803','[usp_gather_ImportTxt]','导入txt',ERROR_MESSAGE())
                        end catch
                    end
                fetch next from save_cur into @filenames,@fileAddress,@belongDate
            end
        close save_cur
        deallocate save_cur
        END
GO

3）清理因查询而导入进表的数据

 CREATE PROCEDURE [dbo].[usp_gather_ImportTxtClear] 
 
AS
BEGIN

 
--数据清理
    declare @csum int
    select @csum=count(*) from tb_TraceData
    where 
    PackageTime<convert(varchar(10),dateadd(d,-31,getdate()),120)

    while @csum>=0
        begin
            delete top(10000) from tb_TraceData 
            where 
            PackageTime<convert(varchar(10),dateadd(d,-31,getdate()),120)
            set @csum-=10000
        end

end
GO

------------------------------以下为定时导出数据的定时作业脚本示例--------------------------------------------------

USE [msdb]
GO

/****** Object:  Job [健康数据库：每天0点10分将31天前的数据转为Txt]    Script Date: 2021/6/9 星期三 下午 3:02:32 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

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'健康数据库：每天0点10分将32天前的数据转为Txt', 
        @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 [62天前的数据转为Txt]    Script Date: 2021/6/9 星期三 下午 3:02:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'31天前的数据转为Txt', 
        @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'exec usp_gather_ExportTxt', 
        @database_name=N'数据库名', 
        @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'每天0点10分将31天前的数据转为Txt', 
        @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=20200730, 
        @active_end_date=99991231, 
        @active_start_time=1000, 
        @active_end_time=235959, 
        @schedule_uid=N'86157eab-175e-4756-88c7-bec389a9d8f1'
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