Sql-server – Move database without replication taking new snapshot

What version of SQL Server are you using ?

I just setup a publication and subscription in the method you describe on SQL Server 2012 (11.0.2218) and it appears to be working fine.

I tested inserting, updating, and deleting records from the source - and all transactions made it successfully to the destination.

It is true that the timestamp column is of interest but I didn't have to use the option you mentioned in your update. From this SQL 2000 TechNet article note that

"The literal values for a timestamp column are replicated, but the data type for the replicated values is changed to binary (8) on the Subscriber."

Since this was for SQL 2000 I can only imagine things may have changed... as my subscriber table, just like the publication, has a timestamp column not a binary(8).

Therefore the values that are there when you backup and restore will match. Future inserts will have different timestamp values in the source and destination.

I scripted the entire thing out into one file. The order of events:

• create the publication (with @allow_initialize_from_backup = N'true');
• backup the source database (with format, init);
• restore the database to the destination;
• create the subscription (with @sync_type = N'initialize with backup', @backupdevicetype = 'disk', @backupdevicename = '\myUNCPath\Backup\Backup.bak').

I ran into Msg 21397 during my first try - and an interesting read, explanation, solution are in this blog post.

A lot of testing later, I have the answers.

Does the filter apply to the snapshot?

Yes.

So out of my numbered list, 1 is correct. The reason I didn't really believe this to be true is because when quickly testing this I forgot about the option in Article Properties that controls how the Snapshot is applied. This also made me ask the question after the numbered list beginning 'Further'. The answer to that paragraph is: it depend how it is set in Article Properties. To explain this more fully:

Right click your Publication in SSMS > Properties > click Articles in the left tab > Article Properties button (pick either option that is relevant to you). Look at the Destination Object section > Action if name is in use

This option controls (for this or ALL articles) what the Agent does when applying the Snapshot to the Destination database. As Default it Drops and recreates the object (this is why it seemed to me to be replacing all the data - essentially it was because it was dropping the object, creating the object, BCP'ing the data in (so anything previously in the table is gone)).

I needed "Delete data. If article has a row filter, delete only data that matches the filter" as this therefore kept the data already in the table that was not > 2011.

Hopefully someone in the future can save some time testing by finding this answer! I'm slightly surprised that the default option is to Drop and replace the object to be honest, but I suppose that fits most people's purposes for Snapshot. So someone can find this answer if they are searching google I shall reproduce the question as I would google it:

When initializing replication from Snapshot on a publication with Filtered rows, does it apply the filter to the snapshot it takes?