Copy tables and stored procedures from one SQL db to another

Posted on 2009-12-21
Medium Priority
Last Modified: 2012-05-08
What is the easiest way to copy "ALL" tables (with constraints, triggers and validations) and stored procedures from one DB to another.

My original db is called dbRec and Im trying to move them to another called dbPanel.

Im not sure how to write this syntax and if it is doable.

Question by:AIdoHSG
  • 3
LVL 75

Accepted Solution

Aneesh Retnakaran earned 1000 total points
ID: 26099240
you can create a backup and restore it as a new database

Expert Comment

ID: 26099294
IF trying to copy tables/store procedures from one db to another db on the same server
1.) right click on the destination DB and click on All Tasks> Import Data
2.) click on Next,
3.) choose the source DB
4.) enter the credentials
5.) choose the object you want to move / copy

this is the simplest

Assisted Solution

rajquest earned 1000 total points
ID: 26099317
another simple way
1.) select all the tables/stored procedure from source DB
2.) right click on the selection and then click on All Tasks>generate sql
3.) copy the SQL statements and paste it on sql query analyser with respect to your destination db
4.) run all the queries which in turn will recreate the structure of the tables and stored procedure

good luck

Author Closing Comment

ID: 31668684
That worked... thank you...
the import wizard didn't bring my data or kept the table structures (i.e. constraints, indexes and validations)

I still needed to do a back up in order to retrieve my data.

Expert Comment

ID: 26100086
the import data transfers data too.
in the wizard the last dialog screen has an option called ***** copy data.
you have to select that

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question