Restoring database - How to keep replication in place

Question

Server A: have two databases - DB1(Publisher) and DB2(Subscriber)
Replication is currently working fine for these two databases.

Server B: have two databases - DB1(Publisher) and DB2(Subscriber)
Replication is currently working fine for these two databases.

Now, due to some testing issues, I want to restore copy of DB1(Publisher) from Server1 to Server2.
But I am concerend about replication.

What is best way to keep the replication in-place on Server2?

I am thinking to follow these steps -

• Take backup of database DB1 from Server1
• Disable all the associated SQL Server Agent Jobs on DB1, DB2 on Server2
• Restore Database DB1 on Server2 from back in step# 1
  (do I need to go for option WITH KEEP_REPLICATION ??)
• Enable SQL Server Agent Jobs on DB1, DB2 on Server2

Can please guide if these steps seems okay?

Answer 1

see below how I successfully restore a replication (transactional replication).

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)