SQL Server Database Restore with Replication

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 re

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)

StackExchange Database Administrators Q#150218, answer score: 2