SQL Server Database Restore with Replication

Question

Forgive me if this sounds angry/rant-y, but it kinda is.

High Level Overview:

How can I restore a SQL DB that's involved in transactional replication with 5 pub/subs, without having to drop, create, & re-initialize (or re-snapshot) all the subs. Or at least, without having to re-init/re-snapshot. I can handle scripting the pubs/subs themselves, that's understandable (somewhat). But, I have a known good backup, and/or am willing to make a "fresh" backup on the primary server to share with the secondary server, in an attempt to use the "initialize from backup" feature when creating the subscriptions, and I just can't seem to get that to work.

Rant-y version:

Why can't SQL Server "trust me" that the publisher and subscriber DBs are in sync by nature of being restored from the SAME backup file, and that replication can simply continue on its merry way without having to do a damn reinit or be re-created from scratch!?

---

The Details:

I have a database MyCoolDB. It lives (originates) on server OldFoo. In the "Old" environment, it's replicated to server OldBar, for read-only reporting purposes. I am prepping a migration to a "New" environment, with similarly named servers NewFoo and NewBar. For purposes of this discussion, Foo servers will be the publishers, and Bar servers will be the subscribers. To simplify things, we'll be letting the publisher double as the distributor, i.e. OldFoo will publish & distribute to OldBar, and same for NewFoo to NewBar.

In the "New" environ, I want to restore MyCoolDB to both NewFoo (pub) and NewBar (sub), using the same backup file. AND, because I'm doing this multiple times to refresh said environ before final cutover, I want to NOT have to drop, re-create, and re-init or re-snapshot the pubs/subs every dang time.

Is that so much to ask? Surely there must be a way to "refresh" Dev & QA environments, from higher (Prod/Pre-Prod) environments, when it comes to SQL DBs that may be involved in replication.

Assumptions/Notes:

1. I can effectively "Pause" replication by stopping the associated Agent Jobs in the Repl categories (Distribution & Log-Reader agents). This may be a bad assumption.
2. I can quickly and efficiently restore MyCoolDB from a native backup file on-disk & accessible to both server instances.
3. I have scripted-out the repl pub/sub creation for the NewFoo instance by going thru the generated-scripts from OldFoo and doing string-replacement. I have also verified that, when using said creation scripts and initializing the normal way, things work as expected.
4. I have reviewed articles & blog posts that purport to walk thru the "init a subscription from a backup" process but have not found a working solution. I got close; details below.
5. While the environs in question here are definitively NOT production, this is all prep & practice for a production migration that involves similar issues (replicated DB, re-init causes performance problems).

---

Close, but No Cigar:

After attempting this methodology...

• Delete Pubs/Subs
• restore DB on Publisher
• create Pubs
• backup DB to special location
• restore DB on Subscriber from special location
• create Subs with init-from-backup in special location

I checked the Repl-Monitor for the subscription, and under the Log Reader Agent status, the following error was shown. The error below is why I attempted to exec sp_replrestart, but as I said, that failed too.

The process could not execute 'sp_repldone/sp_replcounters' on 'NewFoo'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
The log scan number (267414:8865:55) passed to log scan in database 'Grading' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. (Source: MSSQLServer, Error number: 9003)
Get help: http://help/9003
The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017
The process could not execute 'sp_repldone/sp_replcounters' on 'NewFoo'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

Any takers?

TL;DR: My primary goal is to understand how to get replication subscriptions initialized from a backup, so that re-init/re-snapshot isn't needed. My secondary goal would be to avoid having to drop & re-create (from scripts) the pubs/subs every time, but at this point that's a "nice to have", not a "requirement".

Answer 1

Actually, @NateJ, you are almost there. But instead of create Subs with init-from-backup in special location, you need to add subscription with @sync_type='replication support only' like the following

EXEC sp_addsubscription @publication = N'Test',  
                        @subscriber = N'subscriber',  
                        @destination_db = N'SubDB',  
                        @subscription_type = N'Push',  
                        @sync_type = N'replication support only',  
                        @article = N'all'

An easy way to do that is to modify your previously scripted out replication script by looking for sp_addsubscription and change the @sync_type value.

I did this very frequently before. (I assume your replication is a push subscription with transactional publication)

Answer 2

first let me say that restoring a replication db from domain1 into domain2 is not recommended. you will need to script all the replication and re-apply the scripts including the accounts and passwords for the agents in the new domain.

if it is on the same domain then, where all the agents accounts already have all the necessary permissions on servers - publisher, distributor and subscriber then I have been restoring replicated databases without scripting first and applying scripts later on.

here is what I did:

1) backup

2) script all users and permissions

3) script the drop and create for all replication (just in case you need to come to this)

4) restore, troubleshoot and test

---------------------------------------

-- 5.1 --> if the database is involved in replication 
       -- check if it is Sync With backup

--At the Distributor, use the DATABASEPROPERTYEX (Transact-SQL) function to return the IsSyncWithBackup property of the distribution database. 


--IsSyncWithBackup
--The database is either a published database or a distribution database, 
--and can be restored without disrupting transactional replication.

--1 = TRUE
--0 = FALSE
--NULL = Input not valid

-- if that is false, then enable it
-- How to: Enable Coordinated Backups for Transactional Replication (Replication Transact-SQL Programming)
-- http://msdn.microsoft.com/en-us/library/ms147311(v=sql.105).aspx

-- get the distributor
EXEC sp_get_distributor
GO
-- get the distributor server and distributor database too
EXEC sp_helpdistributor
GO
--SQLDISTLON1
-- distribution

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

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

---------------------------------------

--Script to Enable/Disable Database for Replication
--I DID NOT DO THIS
--use master
--exec sp_replicationdboption @dbname = 'Product',
--@optname = 'publish',
--@value = 'false'
--go


--=====================================================================================
-- 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 [Product] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [Product] TO  DISK = N'F:\SQLBackups\UserDB\Product\SQLAPPLON1_the_tail_log.trn' WITH NOFORMAT, NOINIT,  NAME = N'tail log backup', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 1
RESTORE DATABASE [Product] FROM  DISK = N'F:\SQLBackups\UserDB\Product\SQLAPPLON1_Product_FULL_20140909_222917.bak' 
WITH  FILE = 1,  MOVE N'Product' TO N'E:\SQLData\Product.mdf', 
 MOVE N'Product_log' TO N'E:\SQLLogs\Product_log.ldf',  
 KEEP_REPLICATION,  
 NOUNLOAD,  
 REPLACE,  STATS = 1
 go
 ALTER DATABASE [Product] 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 product
go
-- at the product database
declare @db sysname
select @db = 'Product'
SELECT DATABASEPROPERTYEX ( @db, 'IsSyncWithBackup' )
--0

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

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

---------------------------------------
--Script to Enable/Disable Database for Replication
use master
exec sp_replicationdboption @dbname = 'Product',
@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 Product database (in the publisher)

use Product
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
--=====================================================================================

use product
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)

Now, this is not a complete solution, it is more my notes while restoring a replicated database, the troubleshooting I did, some ways to get around possible problems, and a tiny test at the end, before I let users know the db is active.

Hope it helps