December 13, 2001 at 8:01 am
I need to copy most tables, stored procedure, and views from one very big production system to a small test server. I do not have room for all the data, so I cannot just restore a backup.
Is there a way to create a script that will allow me to re-create all tables, sproc etc. on a new server?
I would like to copy some of the data as well, but that bit I can work out myself.
TIA
Henrik Staun Poulsen
Stovi Software
Denmark
December 13, 2001 at 8:21 am
Several posibilities come to mind.
If you're happy doing this manually, just highlight the tables, sps etc in EM and right click, All Tasks, Generate SQL Scripts. You can also script indexes and permissions using this form. The resulting script can be run in QA in the destination server.
There are third party tools to do this automatically, or you could use DMO with VB to make your own tool.
If you have SQL 2000 you can use DTS to Copy Objects and uncheck "Copy Data".
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 14, 2001 at 5:17 am
Paul,
Thank you very much for your reply.
I've also seen an Ad for a product called Red-gate on this site.
In fact I saw it just after having posted my question <g>.
So I've downloaded a demo, and I'll try it out next time I'm on-site.
If it does not work, I'll use your suggestion.
Best regards
Henrik
December 14, 2001 at 5:52 am
Haven't reviewed Red-Gate but still might be worth trying the DTS route. I have a setup which copies objects between servers using the copy objects task - all objects, sps, tables etc can be moved by selecting the relevant checkboxes - it's dead easy to initialise and if you have SQL 2000 installed it's free. You could then use an onsuccess step to connect to an Execute SQL task which moves over the data where needed.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 14, 2001 at 8:33 am
I'd use dts. you can quickly copy structures and limited data.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 14, 2001 at 10:18 am
Ditto....DTS!
-JG
December 18, 2001 at 10:38 am
If you want to do it once then probably dts is easier.
If you want to do it many times and change objects trnsferred then maybe dmo would be easier. I have an example of a dmo transfer (of a single object but I think most of the useful fields are shown) at
Transfer an object via sql-dmo
(Now has reference to this site hopefully.)
Cursors never.
DTS - only when needed and never to control.
December 18, 2001 at 11:19 am
Thanks for the reference!!!!!
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply