SQLServer 以备份初始化订阅

在创建事务复制时，如果发布数据库很大，使用快照初始化时，将等待很久，如果出现问题可能又得重新初始化。使用备份初始化会省很多时间，但是数据库在创建发布订阅期间不能访问。使用备份代替快照迁移数据，备份可以压缩或使用 FTP 传输，若在同一个机房，拷贝复制更快。

现在测试：

创建测试库（发布服务器）

--创建测试库（发布服务器）USE [master]GOCREATE DATABASE [PubDB]ON  PRIMARY (NAME = N'PubDB', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PubDB.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )LOG ON (NAME = N'PubDB_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PubDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)GO--创建测试表（发布服务器）USE [PubDB]GOCREATE TABLE [dbo].[PubTab]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](20) NOT NULL,[Value] [decimal](18, 4) NULL,CONSTRAINT [PK_PubTab] PRIMARY KEY CLUSTERED ([id] ASC),) ON [PRIMARY]GOINSERT INTO [dbo].[PubTab]([name],[Value])VALUES('aa',99),('bb',100)GO

添加复制用的登录账户和数据库用户。订阅不是同一个实例的添加登录账户（发布服务器/订阅服务器）

--添加复制用的登录账户和数据库用户。订阅不是同一个实例的添加登录账户（发布服务器/订阅服务器）USE [master]GOCREATE LOGIN [ReplUser] WITH PASSWORD=N'ReplUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOALTER SERVER ROLE [sysadmin] ADD MEMBER [ReplUser]GOUSE [PubDB]GOCREATE USER [ReplUser] FOR LOGIN [ReplUser]GO

创建发布（发布服务器）

创建完备份后，设置允许从备份文件初始化（发布服务器发布数据库）

--允许从备份文件初始化（发布服务器发布数据库）USE [PubDB]GOEXEC sp_changepublication    @publication = N'PublName',     @property = N'allow_initialize_from_backup',     @value = trueGO

备份数据库（发布服务器）

USE [master]GOBACKUP DATABASE [PubDB] TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PubDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'PubDB-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO

还原最新的备份数据库，将作为订阅库（订阅服务器）

USE [master]GORESTORE DATABASE [SubDB] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PubDB.bak' WITH  FILE = 1,  MOVE N'PubDB' TO N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SubDB.mdf', MOVE N'PubDB_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SubDB_log.ldf',  NOUNLOAD,  STATS = 5GO

添加订阅，指定备份文件（发布服务器发布数据库）

USE [PubDB]GOEXEC sp_addsubscription@publication = N'PublName',@subscriber = N'HZC',@destination_db = N'SubDB',@subscription_type = N'Push',@sync_type = N'initialize with backup',@backupdevicetype='disk',@backupdevicename='D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PubDB.bak'

创建订阅后，更改订阅服务器连接账号

至此，发布订阅据配置完成了，现在看看表中的数据。

SELECT *  FROM [PubDB].[dbo].[PubTab]SELECT *  FROM [SubDB].[dbo].[PubTab]

增加一行记录再查看，复制正常！

INSERT INTO [PubDB].[dbo].[PubTab]([name],[Value]) VALUES('cc',0)GOSELECT *  FROM [PubDB].[dbo].[PubTab]SELECT *  FROM [SubDB].[dbo].[PubTab]

但是，当添加新表发布时，不能使用快照发布来同步了！~

创建测试表（发布服务器发布数据库）

--创建测试表（发布服务器发布数据库）USE [PubDB]GOCREATE TABLE [dbo].[PubTab02]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](20) NOT NULL,[Value] [decimal](18, 4) NULL,CONSTRAINT [PK_PubTab02] PRIMARY KEY CLUSTERED ([id] ASC),) ON [PRIMARY]GO

正常的添加发布（发布服务器发布数据库）

这时不能启用快照同步了，快照没有用。

右键发布的表查看创建表的结构（发布服务器发布数据库），接着到订阅服务器数据库执行。

USE [SubDB]GOCREATE TABLE [dbo].[PubTab02]([ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,[Name] [varchar](20) NOT NULL,[Value] [decimal](18, 4) NULL, CONSTRAINT [PK_PubTab02] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO

好了，添加算是完成了，现在测试添加数据（发布服务器发布数据库）

USE [PubDB]GOINSERT INTO [dbo].[PubTab02]([name],[Value]) VALUES('aa',99)GOSELECT *  FROM [PubDB].[dbo].[PubTab02]SELECT *  FROM [SubDB].[dbo].[PubTab02]

若是添加 / 删除 字段，按正常添加 / 删除即可，直接在发布服务器数据库执行，如。

USE [PubDB]GOALTER TABLE [dbo].[PubTab02] ADD InsertDate DATETIME NULLGOINSERT INTO [dbo].[PubTab02]([name],[Value],[InsertDate]) VALUES('aa',99,GETDATE())GOSELECT *  FROM [PubDB].[dbo].[PubTab02]SELECT *  FROM [SubDB].[dbo].[PubTab02]

参考：

初始化事务订阅（不使用快照）

SQL Server 通过备份文件初始化复制