SqlServer自动化表分区解决方案(二)之数据备份

wynnyo 2020年06月05日 1,129次浏览

SqlServer自动化表分区数据备份

背景

自动化表分区解决方案 一文中, 我们实现了 表分区的自动化, 但是31天的数据清理后, 无法还原, 所以需要备份下以前的数据.

解决方案

  • 采用 xml 文件备份;
  • 使用 bcp 和 xp_cmdshell 把数据导出到指定的文件夹;

技术介绍

BCP-大容量复制程序实用工具

更多信息请参考官网文档

语法

bcp {dbtable | query} {in | out | queryout | format} 数据文件
[-m 最大错误数] [-f 格式化文件] [-e 错误文件]
[-F 首行] [-L 末行] [-b 批大小]
[-n 本机类型] [-c 字符类型] [-w 宽字符类型]
[-N 将非文本保持为本机类型] [-V 文件格式版本] [-q 带引号的标识符]
[-C 代码页说明符] [-t 字段终止符] [-r 行终止符]
[-i 输入文件] [-o 输出文件] [-a 数据包大小]
[-S 服务器名称] [-U 用户名] [-P 密码]
[-T 可信连接] [-v 版本] [-R 允许使用区域设置]
[-k 保留 Null 值] [-E 保留标识值][-G Azure Active Directory 身份验证]
[-h"加载提示"] [-x 生成 xml 格式化文件]
[-d 数据库名称] [-K 应用程序意向] [-l 登录超时]

xp_cmdshell-输出作为文本的行返回

更多信息请参考官网文档

启用

  • 在 MSSM 中执行以下 Sql 语句;
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
  • 得到以下结果开启成功;
配置选项 'show advanced options' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。
配置选项 'xp_cmdshell' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。

语法

xp_cmdshell { 'command_string' } [ , no_output ]

项目示例

修改 Consts.cs 文件

在 Consts.cs 文件中加入 常量: TempTableName 和 XmlBakFilePath

public static string TempTableName = "Log_Temp";  // 临时表的 name
public static string XmlBakFilePath = FilePath + "\\XmlBak"; // 备份文件 xml 目录

修改定时任务

在 PartitioningTablesTask 找到 TODO, 添加导出语句并删除 TODO 注释

// 利用 bcp 和 xp_cmdshell 把 sql data 保存为 xml 文件
sql.Append("EXEC xp_cmdshell 'bcp " +
           $"\"SELECT * FROM [{Consts.DbName}].[dbo].[{Consts.TableName + "_temp"}] FOR XML PATH(''Row'')," + 
           $" ROOT(''Log_{deleteDate}'')\" queryout \"{Path.Combine(Consts.XmlBakFilePath, fileName + ".xml")}\" -T -c -r'");