远程定时备份ACCESS数据库到SQL Server

剑指工控星期二, 08/07/2018 - 07:00 发表

一、整体思路

1、使用SQL Server内存储过程被定时执行的方法，在作业>步骤>计划时间内设置时间节点触发步骤，时间节点可以触发重复执行步骤或只执行一次。

2、在Host1机器上SQL Server中创建数据库，表

3、在Host2（Host1上也可以，此处用于验证局域网机器备份）机器上创建一个Access数据库，表，该数据库位于共享路劲下

4、 Host1上的SQLServer内创建的表与Host2上ACCESS内的表字段一致

5、在Host1上SQL Server中创建的数据库下创建存储过程数据库>可编程性>存储过程

6、新建存储过程，在存储过程中写SQL语句用于将ACCESS中的表数据插入到SQL Server表中。

7、在步骤中调用刚才创建的存储过程

8、在SQL Server中查看结果

二、实验环境

Host1：

OS：Win7 sp1

IP：172.20.10.10/24

数据库类型：SQL Server 2008R2

数据库：BackupAccess

表：dbo.Access

字段：序号，日期，时间

Host2：

OS：win7 sp1

IP：172.20.10.9/24

数据库类型：Access2007

数据库：Test.mdb

表：Test

字段：序号，日期，时间

三、填坑过程

1、 SQL语句执行时需要用到microsoft.jet.oledb.4.0或者Microsoft.ACE.OLEDB.12.0驱动，需要开启两项服务SQLServer(MSSQLSERVER)和SQL Full-text Filter Daemon Launcher (MSSQLSERVER)将这两项的登录身份设置为本地系统账户，并且勾选允许服务与桌面交互

2、需要在SQL Server内开启2项服务

在新建查询内运行一下语句

--启用Ad Hoc Distributed Queries

Exec sp_confiure ‘show advanced options’,1

Reconfigure

Exec sp_configure’Ad Hoc Distributed Queries’,1

Reconfigure

使用完成后，可以用如下语句关闭

Exec sp_configure’Ad Hoc Distributed Queries’,0

Reconfigure

Exec sp_confiure ‘show advanced options’,0

Reconfigure

3、使用SQL Server2012用户请自觉下载AccessDatabaseEngine_X64文件并安装。

4、局域网远程计算机的登录账户和密码必须与SQL Server计算机账户密码一致，远程计算机共享ACCESS文件时注意共享路劲权限，需要设置为Everyone和管理员账户完全控制，否则会提示“文件已经被使用”的错误。

四、具体步骤

1、连接SQL Server数据库

在Host1上连接SQL Server数据库

2、创建数据库和表

3、创建ACCESS数据库和表

4、 ACCESS内创建测试数据

5、 Host1上对SQL Server创建存储过程

6、配置存储过程

SQL语句：

” insert into dbo.Access SELECT * from openrowset('microsoft.jet.oledb.4.0','\\172.20.10.9\E\AccessBackup\Test.mdb';;,Test) where 序号 > (select top 1 序号 from dbo.access order by 序号 desc )”

注意：SQL server2008内支持microsoft.jet.oledb.4.0驱动。

SQLServer2012内不支持，需要下载安装AccessDatabaseEngine_X64文件，将此处修改为“Microsoft.ACE.OLEDB.12.0”

该SQL语句目的：将ACCESS的表Test中的最新数据同步到SQL Server的表dbo.Acesss中，并且实现只同步最新的记录。利用数据库排列唯一序号，并检查该序号数值大小的原理实现。

7、配置完存储过程后可手动执行一次存储过程或将存储过程内的SQL语句拷贝至新建查询内运行。