sql-server-2008 - 恢复数据库 - 如何保持复制到位

Question

服务器 A:有两个数据库 - DB1(发布者)和 DB2(订阅者)
复制目前对这两个数据库工作正常。

服务器 B:有两个数据库 - DB1(发布者)和 DB2(订阅者)
复制目前对这两个数据库工作正常。

现在，由于一些测试问题，我想将 DB1(Publisher) 的副本从 Server1 恢复到 Server2。
但我担心复制。

在 Server2 上保持复制的最佳方法是什么？

我正在考虑遵循以下步骤-

从 Server1 备份数据库 DB1

禁用 DB1 上所有关联的 SQL Server 代理作业，Server2 上的 DB2

在步骤 1 中从后面恢复 Server2 上的数据库 DB1
(我需要选择 WITH KEEP_REPLICATION 吗？？)

在 DB1 上启用 SQL Server 代理作业，在 Server2 上启用 DB2

如果这些步骤看起来没问题，请指导一下吗？

Answer 1

请参阅下面我如何成功恢复复制(事务复制)。

1 - I script everything related to that publication - so that I can create it again from those scripts if things go wrong
2 - I script all the user and permissions in the database I want to restore (if the backup comes   from a different environment/server)
3 - disable any processes that hit the database you want to restore
4 - backup the current database
5 - restore verifyonly the backup you have just created (in case you need to restore it)
6 - see if you have enough disk space
7 - check if the database is involved in replication



---------------------------------------
--> 5 - see if the database is involved in replication (transactional replication)
---------------------------------------
SELECT 
name as [Database name],
CASE is_published 
    WHEN 0 THEN 'No' 
    ELSE 'Yes' 
    END AS [Is Published],
CASE is_merge_published 
    WHEN 0 THEN 'No' 
    ELSE 'Yes' 
    END AS [Is Merge Published],
CASE is_distributor 
    WHEN 0 THEN 'No' 
    ELSE 'Yes' 
    END AS [Is Distributor],
CASE is_subscribed 
    WHEN 0 THEN 'No' 
    ELSE 'Yes' 
    END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4
go



use DB1
go
-- at the DB1 database
declare @db sysname
select @db = 'DB1'
SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' )
--0

--change the value to TRUE
sp_replicationdboption @dbname= 'DB1',
                   @optname= 'sync with backup',
                   @value='true'


--=====================================================================================
-- THE RESTORE
-- note the backup of the tail of the transaction log alongside the restore.
-- note also the KEEP_REPLICATION option
--=====================================================================================


USE [master]
ALTER DATABASE [DB1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [DB1] TO  DISK = N'F:\SQLBackups\UserDB\DB1\SQLAPPLON1_the_tail_log.trn' WITH     NOFORMAT, NOINIT,  NAME = N'tail log backup', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 1

RESTORE DATABASE [DB1] FROM  DISK =   N'F:\SQLBackups\UserDB\DB1\SQLAPPLON1_DB1_FULL_20140909_222917.bak' 
WITH  FILE = 1,  MOVE N'DB1' TO N'E:\SQLData\DB1.mdf', 
 MOVE N'DB1_log' TO N'E:\SQLLogs\DB1_log.ldf',  
 KEEP_REPLICATION,  
 NOUNLOAD,  
 REPLACE,  STATS = 1
 go

ALTER DATABASE [DB1] SET MULTI_USER
GO
--=====================================================================================
-- THE RESTORE - FINISH
--=====================================================================================



---------------------------------------
-- set sync with backup to false (unless decided otherwise)
-- problem with this is that it slows the log reader a bit

use DB1
go
-- at the DB1 database
declare @db sysname
select @db = 'DB1'
SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' )
--1

--change the value to False
sp_replicationdboption @dbname= 'DB1',
                       @optname= 'sync with backup',
                       @value='FALSE'

-- at the DB1 database
declare @db sysname
select @db = 'DB1'
SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' )
--0


---------------------------------------
--Script to Enable/Disable Database for Replication
use master
exec sp_replicationdboption @dbname = 'DB1',
@optname = 'publish',
@value = 'True'
go



---------------------------------------
-- There were some problems
-- the backup that I needed to use to restore the DB was much older and from a different server


-- I used these two commands
-- from inside the DB1 database (in the publisher)

use DB1
go

DBCC OPENTRAN 
sp_replrestart

-- http://msdn.microsoft.com/en-us/library/ms174390.aspx

--sp_replrestart is an internal replication stored procedure and s
--hould only be used when restoring a database published in a transactional replication 
--topology as directed in the topic Strategies for 
--Backing Up and Restoring Snapshot and Transactional Replication.

--Used by transactional replication during backup and restore so that 
--the replicated data at the Distributor is synchronized with data at the Publisher. 
--This stored procedure is executed at the Publisher on the publication database.

--How if works? 
--sp_replrestart will fill NO-OP (No-Operation) transaction which will cause the 
--LSN's to increase until the log LSN matches the LSN as per Distribution database. 
--So from there-on, Logreader agent will be able to read the LSN, it is expecting.

--Note: Depending on how old the backup of the Published database which was restored, 
--it may take hours for this operation and may make the transaction log to grow big.
--and grow by gigs, until they match.



--=====================================================================================
-- THE TEST
-- I add the folowing table to the published database
-- then I add the article to the publication
-- start a snapshot
-- and check if the article is in the subscription
-- when I generated the SNAPSHOT - it only generate this ONE ARTICLE
--=====================================================================================

use DB1
go
--DROP TABLE dbo.marcelo_test

create table dbo.marcelo_test(
i int not null identity(1,1) not for replication
,the_name varchar(40) not null )

insert into marcelo_test values ('belluno')
insert into marcelo_test values ('rovigo')
insert into marcelo_test values ('feltre')
insert into marcelo_test values ('cremona')
insert into marcelo_test values ('padova')
insert into marcelo_test values ('vicenza')
insert into marcelo_test values ('venezia')


select * from dbo.marcelo_test

alter table dbo.marcelo_test
add constraint pk_marcelo primary key clustered (i)