Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. The snapshot process (which creates a copy of all of the objects and data specified by a publication) is also commonly used to provide the initial set of data and database objects for transactional and merge publications.
Snapshot replication can be used to update the structure of tables in a database. For example, if a customer needs to update the schema of certain tables, they can set up a snapshot agent job to do this automatically. This job will remove the old tables and create new ones with the latest design/schema from the original database.
Previously, to move specific tables from one database to another, the customer needed to manually create scripts for the desired tables from the original database, delete these tables from the target database, and then use the scripts to recreate them in the new location. However, there’s a tool called sqlpackage.exe that can also perform this task, although it might seem complicated at first.
In this blog post, we will discuss on how to refresh only the schema using SQL server snapshot replication. We will transfer schema of some tables from “AdventureWorks2022” database to “AdventureWorks2022_Schema1” database using snapshot replication using below steps.
- Created a empty database “AdventureWorks2022_Schema1” as target database for schema transfer.
- Selected few tables for the schema transfer
- Add filter “WHERE 1=2” for all the tables where only schema is required to be transferred.
- Complete the ‘New Publication Wizard’ by following the wizard instructions. I created the publication with name ‘AdventureWorksSchemaTransfer’
- Run the snapshot agent job.
- Create a new subscription for publication ‘AdventureWorksSchemaTransfer’ with subscriber database “AdventureWorks2022_Schema1” and complete the ‘New Subscription Wizard’
- Run distribution agent job.
- Once snapshot and distribution database job is complete, it will refresh the schema of target database tables using current schema in source database tables
- We can see that all schema of selected tables is transferred to target database without any data.
Now I added one column in [Person].[BusinessEntity] table in ‘AdventureWorks2022’ database and will show that it will be replicated to target database ‘AdventureWorks2022_Schema1’ once I will apply a fresh snapshot.
In below screenshot you can see that column ‘TestID’ has been replicated successfully.
Hope you will find this article helpful.
Happy Learning !!