Copying database objects

  • How to copy the database objects (tables, views, Stored procedures, functions, schemas) from one database to another database of different server. Both source and target are SQL server 2008 R2. I just want to copy the database objects and not the actual data. Could somebody please help.

  • Right click Management -> Generate scripts -> select the DB then go through it.

    On the objects list it should have "Script Create" as true and "Script Data" as false by default. Select the sp/tables you want then Script it to file and execute that file on the server you want (check it first).

    Dird


    Dird

  • or you can use the transfer sql objects task in SSIS

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • If you want everything (data, types, users, security settings, etc.), I found the best way to do it is to take a full backup of the source database and then restore it as a different name. This also allows you to move the backup file from one server to another (for example, a test server) and restore it there. Just another option to consider depending on the situation.

  • I tried suing generate scripts.. I was successful in transferring few objects but not all 🙁 :'(.. It has not copied all the tables.Can someboby please help me more on it

  • ranganathleo (5/13/2013)


    It has not copied all the tables.Can someboby please help me more on it

    Did you select all the tables you wanted to copy?

    Dird


    Dird

  • Thanks for the help. The problem was the db was having more than 3000 secondary files. It also had partition functions and schemas. I first copied the partitioned fn's and schemas and was then able to copy the tables using the generate script method.

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

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