Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Copying database objects Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 5:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 3:46 AM
Points: 30, Visits: 132
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.
Post #1452045
Posted Monday, May 13, 2013 5:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1452048
Posted Monday, May 13, 2013 5:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 179, Visits: 819
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
Post #1452055
Posted Monday, May 13, 2013 5:48 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 3,999, Visits: 3,022
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1452059
Posted Monday, May 13, 2013 5:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 3:46 AM
Points: 30, Visits: 132
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
Post #1452060
Posted Monday, May 13, 2013 7:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1452087
Posted Tuesday, May 14, 2013 5:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 3:46 AM
Points: 30, Visits: 132
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.
Post #1452521
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse