Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Marioi
Posting Yak Master
132 Posts |
 Posted - 2007-05-03 : 10:18:03
|
| We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-03 : 13:02:32
|
| You can save package to other servers and use dts to copy db objects, but do need same work to build the package. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-03 : 13:27:23
|
I wrote a tool to do this (except for the DTS part). It's open source so you can tweak it if you need to (to add DTS scripting, say). It will generate scripts for all objects in a 2000 or 2005 db, one file for each object. Included is a .bat file that executes them all using sqlcmd. It's called scriptdb.exe, available here: http://www.elsasoft.org/tools.htmyou can also use the .bat file as an autobuilder to catch build breaks in your checked-in sql code early.
www.elsasoft.org |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2007-05-03 : 15:03:53
|
| Very useful jezemine. Thank you for making this available to the community. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-03 : 15:26:17
|
you bet. it was very useful to me in getting all the databases where I work now under source control. when i started it was much more of a cowboy atmosphere, but it's been tamed now.
www.elsasoft.org |
|
|
|
|
|
|
Copying sprocs, DTS to multiple DBs and servers