通过SQL Server 2008数据库复制实现数据库同步备份

悠悠 2022-07-08 17:25 156阅读 0赞

----数据库复制(同步)
    -----创建发布服务器 的整个流程逻辑思路
    1、在本地磁盘上创建一个设置快照的目录(文件夹)
    2、设置数据库恢复模式为 完整模式 即 RECOVERY MODE =FULL
    3、启用代理服务
    4、点击 "复制" 功能按钮 ---右键本地发布---新建分布  进入到发布向导界面
    5、默认选择第一项(“ZHAOWENZHONG”将充当自己的分发服务器；SQL SERVER将创建分发数据库和日志)；当然也可以选择其他服务器作为 分发服务器；
    6、选择接 代理服务配置为 自动启动模式；
    7、填写快照文件夹目录绝对路径；
    8、选择需要执行 “复制”的数据库；
    9、选择发布快照类型：(快照发布、事务发布、具有可更新订阅的事务发布、合并发布)
     快照发布：发布服务器按预定的时间间隔向订阅服务器发送已发布数据的快照；
     事务发布：在订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器；
     具有可更新订阅的事务发布：在SQL SERVER 订阅服务器收到已发布数据的初始快照后，发布服务器将事务式传输到订阅服务器。来自订阅服务器的
            事务被应用于发布服务器。
     合并发布：在订阅服务器收到已发布数据的初始快照后,发布服务器和订阅服务器可以独立更新已发布数据。更改会定期合并。
    10、选择要发布的对象（我们选择全部对象）。
    11、选择需要同步的数据(列)，如果要进行筛选 则点 右侧的 “添加” 在弹出的窗体中进行筛选。否则直接进行下一步操作（即选择全部）
    12、设置代理服务的运作方式（立即运行还是设置相应的时间间隔 来运行快照）。如果设置为时间间隔来运行快照，则需要设置 “作业计划”。
    13、设置 代理快照 登陆名（windows 方式 或 混合模式(指定用户名和密码)）
    14、发布 并填写发布的名称。
    
    --------SCRIPT
    ---本次测试是选择的 快照发布 类型
    /****** 正在编写复制配置的脚本。脚本日期: 2010/4/23 15:27:43 ******/
    /****** 请注意: 出于安全原因，所有密码参数均使用 NULL 或空字符串代替。******/
    
    /****** 开始: 要在发布服务器上运行的脚本 ******/
    
    /****** 正在将服务器作为分发服务器安装。脚本日期: 2010/4/23 15:27:43 ******/
    use master
    exec sp_adddistributor @distributor = N'WENZHONGZHAO', @password = N''
    GO
    
    -- 添加代理配置文件
    -- 更新代理配置文件默认值
    exec sp_MSupdate_agenttype_default @profile_id = 1
    GO
    exec sp_MSupdate_agenttype_default @profile_id = 2
    GO
    exec sp_MSupdate_agenttype_default @profile_id = 4
    GO
    exec sp_MSupdate_agenttype_default @profile_id = 6
    GO
    exec sp_MSupdate_agenttype_default @profile_id = 11
    GO
    
    -- 添加分发数据库
    use master
    exec sp_adddistributiondb @database = N'distribution', @data_folder = N'D:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Data', @data_file = N'distribution.MDF', @data_file_size = 5, @log_folder = N'D:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/Data', @log_file = N'distribution.LDF', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
    GO
    
    -- 添加分发发布服务器
    exec sp_adddistpublisher @publisher = N'WENZHONGZHAO', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'G:/SQLSERVER2008/ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
    GO
    
    
    /****** 结束: 要在发布服务器上运行的脚本 ******/
    
    
    -- 启用复制数据库
    use master
    exec sp_replicationdboption @dbname = N'TestCopyDataBase', @optname = N'publish', @value = N'true'
    GO
    
    exec [TestCopyDataBase].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
    GO
    exec [TestCopyDataBase].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
    GO
    -- 添加快照发布
    use [TestCopyDataBase]
    exec sp_addpublication @publication = N'zwz_publication', @description = N'来自发布服务器“WENZHONGZHAO”的数据库“TestCopyDataBase”的快照发布。', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'true', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_address = N'192.168.0.101', @ftp_port = 21, @ftp_subdirectory = N'/ftp', @ftp_login = N'admin', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'snapshot', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1
    GO
    
    
    exec sp_addpublication_snapshot @publication = N'zwz_publication', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N''
    exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'sa'
    GO
    exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'NT AUTHORITY/SYSTEM'
    GO
    exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'wenzhongzhao/Administrator'
    GO
    exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'NT SERVICE/SQLSERVERAGENT'
    GO
    exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'NT SERVICE/MSSQLSERVER'
    GO
    exec sp_grant_publication_access @publication = N'zwz_publication', @login = N'distributor_admin'
    GO
    
    -- 添加快照项目
    use [TestCopyDataBase]
    exec sp_addarticle @publication = N'zwz_publication', @article = N'A', @source_owner = N'dbo', @source_object = N'A', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'A', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL'
    GO
    
     -----SCRIPT
    
    -----设置FTP方式 访问 数据库复制快照
    15、在主数据库上设置FTP服务，然后建立一个指向 数据库复制快照 文件夹的FTP站点。
     并设置好可以远程连接FTP站点的账号，最后在 发布服务器上设置一下FTP客户端即：
     a、选择刚刚建立的 本地发布名称 例如： zwz_publication
     b、右键 ----属性----选中FTP快照---选中右侧 “运行订阅服务器使用FTP下载快照文件”，然后设置FTP参数
      需要注意的是 "从FTP 根文件夹开始的路径" 参数 设置为 “/ftp’即可。点击 确认 按钮后会在 快照文件夹
      中 创建一个FTP 文件夹，并在该文件夹中生成快照文件。
    
     
    ------订阅服务器
    注意： 进入登陆 订阅服务器时，在服务器名称 中一定要输入 订阅服务器名称 而不能输入 订阅服务器的IP 否则会
      提示错误。
    1、进入订阅服务器 >>>>>复制>>>>>本地订阅>>>>>右键>>>>新建订阅
    2、选择发布服务器>>>>> 在数据库和发布 中选择 发布服务器上的 发布名称例如：zwz_publiction
    3、选择分发代理的订阅方式(推送订阅、请求订阅)
     推送订阅：是在发布服务器 XXX 上运行所有代理的方式。
     请求订阅：是在订阅服务器 YYY 上运行每个代理的方式。
    4、我这里选择的是 推送订阅 方式。
    5、紧接着 选择 订阅服务器和 订阅服务器上的 订阅数据库。可以同时设置多个订阅服务器，只需 点击 右下角的 添加
     订阅服务器。同样需要注意的时，在订阅服务器的 服务器名称 中一定要输入 订阅服务器名称。
    6、指定每个分发代理的进程账号和连接 （即配置安全选项 ：设置和分发服务器 上设置的 代理方式、 用户、密码 一样）
    7、指定 每个代理的同步计划 这里 选择  ”连续运行“
    8、开始创建订阅
    
    -----------------开始: 要在发布服务器“WENZHONGZHAO”上运行的脚本-----------------
    use [TestCopyDataBase]
    exec sp_addsubscription @publication = N'zwz_publication', @subscriber = N'ZFM-PC', @destination_db = N'TestCopyDataBase', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
    GO
    -----------------结束: 要在发布服务器“WENZHONGZHAO”上运行的脚本-----------------
    
    -----------------开始: 要在订阅服务器“ZFM-PC”上运行的脚本-----------------
    use [TestCopyDataBase]
    exec sp_addpullsubscription @publisher = N'WENZHONGZHAO', @publication = N'zwz_publication', @publisher_db = N'TestCopyDataBase', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1
    
    exec sp_addpullsubscription_agent @publisher = N'WENZHONGZHAO', @publisher_db = N'TestCopyDataBase', @publication = N'zwz_publication', @distributor = N'WENZHONGZHAO', @distributor_security_mode = 0, @distributor_login = N'sa', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20110623, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'True', @job_login = null, @job_password = null, @publication_type = 0
    GO
    -----------------结束: 要在订阅服务器“ZFM-PC”上运行的脚本-----------------
    
     
    复制订阅 事务模式
    发布库
    1、 表名不能修改
    2、 表上可以增加列(只能在表的末尾追加新列，不能在中间插入新列。在末尾追加的新列默认被发布到订阅库上）
    3、 可以删除列
    4、 不能修改列列类型
    
    
    订阅库
    1、可以删除参与订阅的列，但会导致订阅失败。可以通过修改 自动生成的 存储过程或SQL语句实现订阅。
    2、可以增加列
    3、参与订阅的列不能修改列名。
    4、实现不了订阅索引和触发器
    
    如果修改过发布数据库服务器的名称会报错，执行执行下面SQL即可 修复错误
    
     
    select @@servername
          select serverproperty('servername')
    如果两个结果不一致，说明机器改过名字，在配置复制的时候就会报错误。
    
         --执行下面的语句,完成后重新启动SQL服务   (修复)
        
      if   serverproperty('servername')   <>   @@servername   
             begin   
                 declare   @server   sysname   
                set   @server   =   @@servername   
                exec   sp_dropserver   @server   =   @server   
                set   @server   =   cast(serverproperty('servername')   as   sysname)  
                exec   sp_addserver   @server   =   @server   ,   @local   =   'LOCAL'  
            end