Need to copy structure but not contents

  • 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.


    Henrik Staun Poulsen

    Stovi Software


  • 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,

    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


  • 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

  • I'd use dts. you can quickly copy structures and limited data.

    Steve Jones

  • Ditto....DTS!


  • 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.

  • Thanks for the reference!!!!!

    Steve Jones

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply