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 |
|
Firefly17
Yak Posting Veteran
78 Posts |
 Posted - 2006-03-13 : 00:09:14
|
| Hi..I want to copy a whole database including storedprocedures and tables to another database in the same server. I have tried with backup and restore but when it got restored, the designated database data (tables and all are gone.) what I want is to combine two databases and make as one.Any help? Tks |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-03-13 : 00:21:51
|
| Hi,you can generate scripts for stored procedures and tables with in DB 1 and execute them in DB2.However if you want to copy table data as well you can use DTS for that. |
 |
|
|
Firefly17
Yak Posting Veteran
78 Posts |
Posted - 2006-03-13 : 00:28:12
|
tks for the prompt reply. yeah, i have tried with DTS but stored procedures are not copied along with the tables. Is there any way to get copied the stored procedures along the tables to the destination database?quote:
Originally posted by shallu1_gupta
Hi,you can generate scripts for stored procedures and tables with in DB 1 and execute them in DB2.However if you want to copy table data as well you can use DTS for that.
|
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-03-13 : 00:34:40
|
Hi,quote:
Originally posted by Firefly17
tks for the prompt reply. yeah, i have tried with DTS but stored procedures are not copied along with the tables. Is there any way to get copied the stored procedures along the tables to the destination database?quote:
Originally posted by shallu1_gupta
Hi,you can generate scripts for stored procedures and tables with in DB 1 and execute them in DB2.However if you want to copy table data as well you can use DTS for that.
I think you did not read the post properly. I said generate scriits of all the tored procs from DB1. and execute it on DB2. this will create stored proc from DB1 to DB2For generating script you can select all the stored procedures right click and select generate script. |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-03-13 : 00:44:15
|
quote:
Originally posted by Firefly17
tks for the prompt reply. yeah, i have tried with DTS but stored procedures are not copied along with the tables. Is there any way to get copied the stored procedures along the tables to the destination database?
DTS is Data transformation Service which is used to migrate the tables and Table data within the databases. for migrating the stored procedure,views,UDF you have to create the script and run them on other database.Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-03-13 : 02:32:51
|
| you can script out stored procs, views and other objects using either Enterprise Manager or Query Analyzer. There are also tools that you can purchase that help with this type of thing. I know that red-gate and idera have products made specifically for this. If you are just moving a few objects and only need to do this pretty infrequently then EM and QA are your best bet. If you need to regularly deploy changes then you might want to consider purchased a 3rd party tool made specifically for this task.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-03-13 : 03:09:20
|
| Vyas also has an excellent stored procedure that will script out table inserts for you. His script is here: http://vyaskn.tripod.com/code.htm#inserts-ec |
|
|
|
|
|
|
|
|
Restoring database