SQLSERVER实现简单的数据同步功能

一、SQLSERVER自动备份数据库到共享文件夹。

1、 新增局域网电脑共享文件夹。

2、创建自动备份数据库到共享文件夹的存储过程

/*

备份数据库存储过程exec BackUpDBForLloyd  

*/

createproc [dbo].[BackUpDB]

as

BEGIN 

--启用xp_cmdshell

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

declare @machine nvarchar(50) ='192.168.254.32' -- '192.168.254.32'       --服务器IP

declare @path nvarchar(50) = 'backup'               --服务器共享目录名称 backup

declare @user nvarchar(50) = 'administrator'--'administrator'               --服务器的用户名

declare @pwd nvarchar(50) = 123456--'123456'                    --服务器的密码

declare @database nvarchar(50)  ='佳木斯供水收费系统'-- 'sx_print'            --本地要备份的数据库名称

if left(@path,1) != '\' set @path = '\'+@path

declare @sql nvarchar(800) = 'net use \\'+@machine+@path+' "'+@pwd+'" /user:'+@machine+'\'+@user

--建立连接类似：'net use \\192.168.200.175\208bf " " /user:192.168.200.175\happy'

Exec xp_cmdshell @sql

declare @date datetime = getdate();

Declare @fileName nvarchar(200) = '\\'+@machine+@path+'\'+@database + +Convert(nvarchar(10),@date,120 )  + '.bak'

--备份数据库

Backup Database @database To disk = @fileName With INIT,CHECKSUM;

set @sql = 'net use \\'+@machine+@path+' /delete'

--断开连接类似：'net use \\192.168.200.175\208bf /delete'

Exec xp_cmdshell @sql

 END

go

3、 SQLserver创建”定时备份同步数据库”定时作业。

USE [msdb]

GO

/****** Object:  Job [定时备份同步数据库]    Script Date: 12/02/2021 19:18:27 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/02/2021 19:18:27 ******/

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'定时备份同步数据库', 

@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'com', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [1]    Script Date: 12/02/2021 19:18:27 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1', 

@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 BackUpDBForLloyd', 

@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'2', 

@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=20211202, 

@active_end_date=99991231, 

@active_start_time=5000, 

@active_end_time=235959, 

@schedule_uid=N'632c4c5e-6126-496c-888e-c94ea742c874'

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

二、SQLSERVER自动还原数据库

1、创建自动还原数据库的存储过程

/*

还原数据库存储过程

*/

create proc [dbo].[RestoreDatabase]

as

BEGIN

declare @dumpfile varchar(50)

declare @msg varchar(70)

declare @date datetime = getdate();

   select @dumpfile = 'E:\backup\佳木斯供水收费系统'+Convert(nvarchar(10),@date,120 )+'.bak'

   select @msg=convert(char(26),getdate(),9)

   print @msg

ALTER DATABASE 佳木斯供水收费系统同步 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;   

----同一个备份文件还原成不同名称数据库--WITH RECOVERY  

RESTORE DATABASE 佳木斯供水收费系统同步   

   FROM disk=@dumpfile

   WITH REPLACE,

   MOVE 'NewB8' TO 'D:\MSSQL\佳木斯供水收费系统同步.mdf'   , 

   MOVE 'WaterData_tFeeDetail' TO 'D:\MSSQL\佳木斯供水收费系统同步tFeeDetail.ndf', 

   MOVE 'WaterData_tMeterRead' TO 'D:\MSSQL\佳木斯供水收费系统同步tmeterread.ndf', 

   MOVE 'NewB8_log' TO 'D:\MSSQL\佳木斯供水收费系统同步.ldf'

ALTER DATABASE 佳木斯供水收费系统同步 SET MULTI_USER WITH ROLLBACK IMMEDIATE;  

if (@@ERROR <> 0 )

begin

   select @msg=convert(char(26),getdate(),9)+'-----还原数据失败或出现异常'

   print @msg

end

else

begin

   select @msg=convert(char(26),getdate(),9)+'-----数据库还原完毕'

   print @msg

end

 END

2、 SQLserver创建”定时同步更新数据”定时作业。

  USE [msdb]

GO

/****** Object:  Job [定时同步更新数据]    Script Date: 12/02/2021 19:23:17 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/02/2021 19:23:17 ******/

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'定时同步更新数据', 

@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'com', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [1]    Script Date: 12/02/2021 19:23:17 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1', 

@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 RestoreDatabase', 

@database_name=N'master', 

@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'2', 

@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=20211202, 

@active_end_date=99991231, 

@active_start_time=23000, 

@active_end_time=235959, 

@schedule_uid=N'9c1e2dcf-cdd7-4ad2-a0a0-1ea4c59b1639'

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

结论：实现简单的隔天数据同步的功能，可用于简单的报表查询服务器功能。

好处：

1.通过将报表查询分离到不同的数据库服务器上，降低对主服务器的CPU、内存、存储、网络资源的争用；

2.主服务器的增删改进行时，不影响查询服务器的查询，降低阻塞的发生，提高了并发性；

3.在应用程序提交了报表请求、不合理的查询请求时，不会导致长时间的锁表；

4.建立容灾副本甚至实现异地容灾，在发生灾难时，可以减少数据的损失。