Applying a differential backup to a replicated DB

Question

Applying a differential backup to a replicated DB

steve.roberts_boohoo

SSChasing Mays

Points: 646
More actions
March 1, 2019 at 2:08 am

#370831

Hi,
We are about to look at migrating a server running Windows/SQL 2008 to W16/SQL17 and currently this server is a publisher/distributor for some DBs.
On the day prior to the migration I plan to restore a copy of the new server and on the day apply a diff backup to minimise the cutover window. The question is can I setup replication ( i.e new subscribers ) and then apply a diff backup to the origin DB and these changes will be replicated across?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I think what you want to do is script out the replication, and fill in the service account passwords if needed in the script.
then when you restore the database, you run the replication script,and it will apply the data to the subscriber.

a diff backup would not be transactionally related to the subscriber,and would tell you you are missing a full backup with lsn xxx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steve.roberts_boohoo SSChasing Mays Points: 646 More actions · Answer 2

Lowell - Friday, March 1, 2019 3:27 AM
I think what you want to do is script out the replication, and fill in the service account passwords if needed in the script.
then when you restore the database, you run the replication script,and it will apply the data to the subscriber.
a diff backup would not be transactionally related to the subscriber,and would tell you you are missing a full backup with lsn xxx

Hi,

I would have already applied a full backup though? And then a diff backup on top?

Lowell SSC Guru Points: 323518 More actions · Answer 3

to restore a differential, you need the database to have been restored from a full backup and also be in recovery mode...nothing can read or write to it between when the Full LSN completed and the DifLSN that starts.

to me it sounds like you are talking about letting replication continue, and wanting to still restore a differential, right?
that would not be possible....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steve.roberts_boohoo SSChasing Mays Points: 646 More actions · Answer 4

Lowell - Friday, March 1, 2019 4:17 AM
to restore a differential, you need the database to have been restored from a full backup and also be in recovery mode...nothing can read or write to it between when the Full LSN completed and the DifLSN that starts.
to me it sounds like you are talking about letting replication continue, and wanting to still restore a differential, right?
that would not be possible....

Hi,

Yes I'd like to restore a full DB to the new server. Setup replication to several subscribers and then apply a diff backup to the new server.

Rather than restore a full DB, next day restore a diff backup then setup replication to the subscriber.

This is not possible then?