• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Replication: SQL Server

I found the following article about how to create Transactional replication without Initial snapshot but i am not very sure how we can do that. If someone has done this already could you please help me out here.


Our database is really big (Abt 200+ GB) and its on SQL Server 2005. We already have Transactional replication setup (pull subsc) via normal method like create publication and then initialize with Snapshot.. which takes overall 7 Hrs.
  • 3
  • 3
1 Solution
i've done it
basically, if you are able to copy the database yourself, you don't need to use the snapshot,
you just make the copy and then tell sql server not to perform the snapshot,
but you must take into account that the databases must be identical when sql server starts the replication process.
in order to achieve that, you will need to suspend the update activity against your source envrionement
AanvikAuthor Commented:
Thank you for your response.

So it means that
1.Stop all the db activity...
2 take backup..
3.copy and restore to reporting server
4.Configure distribution on reporting server
5. dont configure snapshot on Prod server
6. Configure subscription on reporting server

Am I going in correct direction... Also If yes then doing this backup/copy and Restore will take abt 4 hrs and its better to generate snapshot in 3 hrs in this case if you have to keep the application down against the database.

Also can we do the same with GUI interface..


you caN do everything using the gui

basically you are in the right direction, in order to tell sql server not to use a snapshot, you need to define everything regulary through the gui, and just unmark the perform snapshot in the wizard
but basically, you will need to make sure the database are identical before the replication starts, otherwise you might encounter errors
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

AanvikAuthor Commented:
Hi Shalom,
Thank you for your comments. But as I said backup/Copy to Reporting server/Restore will take about 4 hrs and we can;t keep the application down for 4 hrs.. Its better to create snapshot in this case cause it takes about 3 hrs...  

>>but basically, you will need to make sure the database are identical before the replication starts, otherwise you might encounter errors

Regarding this above comments do you mean just the structure or data as well?
structure and data
AanvikAuthor Commented:
Its not completely answered but helped me getting into right direction.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now