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

Replicate SQL Structure, Not Data

SQL Server 2005 Enterprise Edition - How can I replicate the structure of a table (or other db objects) without replicating the data?  Here is the situation ... I have one Primary DB that I make changes to (ie:  add new columns, new triggers, etc) and I want those changes that I made to replicate down to my subscribers, but I do not want to reinitialize the data.  In fact, I don't want to touch their data at all, just maintain structural integrity .... automatically.  Transactional replication as long as I'm on SP 1?  How do I filter all rows?
0
psisqlmgr
Asked:
psisqlmgr
  • 5
  • 3
1 Solution
 
Ted BouskillSenior Software DeveloperCommented:
I use either or both of these tools www.sqldelta.com and "SQL Compare" from www.red-gate.com.  You can set them up to work on the command line or manually yourself.
0
 
psisqlmgrAuthor Commented:
Those types of EMS tools look sufficient, however, I want a no cost solution and figured that replication could/should work.  Am I mistaken?
0
 
Ted BouskillSenior Software DeveloperCommented:
Hmm, I don't think replication was designed for the system tables.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ted BouskillSenior Software DeveloperCommented:
One other point.  Labour has a cost.  I don't know what your time is worth, but that $300 tool will safe you money in the long run.
0
 
psisqlmgrAuthor Commented:
I don't want to replicate system tables.  I just want to replicate the structure of tables within a user db without replicating the data.  
0
 
Ted BouskillSenior Software DeveloperCommented:
Hmm, I'll back up a little bit.  Replication is intended for data, not the schema.

However, in each database there are system tables that hold the schema you design (sysobjects for example).  I thought you were considering using replication on the system tables in the database to replicate the schema which I wouldn't recommend.

As I said labour has a cost.  I would strongly recommend you consider purchasing the products I recommended because they do exactly what you need.  If you try to do it yourself the cost of your labour will be far higher than those products.
0
 
psisqlmgrAuthor Commented:
tedbilly ... thank you for contributing to this Q&A session.  I was hoping that SQL had some way that I wasn't aware of (built in) to do what I asked.  Maybe in a future version...
0
 
Ted BouskillSenior Software DeveloperCommented:
I agree with you.  I wish SQL had it built in.  However, both of those companies do make some great products.  I use SQL Docs from Red-Gate as well and am VERY happy with it.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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