Solved

Export a table - triggers, data and all

Posted on 2009-07-13
8
1,298 Views
Last Modified: 2012-05-07
I have seen this discussed all over the place, but no answers.  Maybe I can get one here.

Best way to present this question is with a scenario:-

I have a Development Database, and a Production Database.  My client needs a new feature on their website, which requires a new table.  Once i have developed the new feature and completed testing I am ready to go live. To do this I upload the new and modified files to the webserver, and export the new table to the production database.  Simple.  I've been doing it for years.

The thing is, it is simple in SQL Server 2000 Enterprise Manager, but seems impossible in SQL Server 2008 Management Studio.  I can export a table, but None of the indexes, triggers or data go across.

So, the question is, using SQL Server 2008 Management Studio, what is the best way to get a table (includeing all triggers, indexes and data) from one database to another?

Many Thanks in advance.  Whoever can help me solve this will be my hero for life!!
0
Comment
Question by:Jay1607
  • 5
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24846287
>> The thing is, it is simple in SQL Server 2000 Enterprise Manager, but seems impossible in SQL Server 2008 Management Studio.  I can export a table, but None of the indexes, triggers or data go across.

You need Database Publishing wizard to achieve your objective.

http://go.microsoft.com/fwlink/?LinkId=119368

Below one for SQL Server 2000 and 2005 alone

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Hope this helps
0
 

Author Comment

by:Jay1607
ID: 24846366
Thanks rrjegan17,

I have downloaded and installed, but can't find where I launch it.

Can you confirm that this is for SQL Server 2008 management studio?

Thanks again.

Jason
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24846393
Hope this helps with the usage of Database Publishing wizard.

http://msdn.microsoft.com/en-us/library/bb895179.aspx

No need to download that one as in SSMS 2008, you have that option integrated by default.
You have to download if you are using either SSMS 2008 Express or SSMS 2005.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24846451
In SSMS 2008, you have several options to achieve your objective:

1. Right click your Database --> Tasks --> Generate Scripts
Make sure that in Select Script Options page, Under Table / View options Script Data, Script Triggers and other required options are set to True.

2. Install Database Publishing utility from

http://go.microsoft.com/fwlink/?LinkId=119368

Go to Visual Studio 2008 which installs as part of SQL Server 2008 installation and it will open up a wizard similar to Generate Scripts Wizard and let you achieve your objective.

Hope this helps.
0
 

Author Comment

by:Jay1607
ID: 24846641
rrjegan12, thank you!  I have been able to move the tables using the Database--> Tasks --> Generate Scripts approach you suggested.   I don't have theactual SQL 2008 DB installed, so could not try option 2.

Currently I am ....

1. generating the script to a Query Window.
2. Opening a new query window for the target DB.
3. Copy and past generated query from the source DB query window (created in step 1) to the target DBs query window and running the query.

This is fine, and I am happy with this, but seems awfully inefficient.  Is this the only way, or is there a more efficient way?  Can a script be run straight into the target DB without having to go via the Query windows?

Thanks again rrjegan17
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24846726
As you asked steps to do in SSMS, I gave those options and No other go other than those above.

If you are interested in any third party tools to ease your process, then you can try this one out:

http://www.apexsql.com/sql_tools_script.asp

Just script the entire database including your data and then run that sql file in the target database and you can automate this task using this tool.

Kindly revert if you need any clarifications on this.
0
 

Author Closing Comment

by:Jay1607
ID: 31603137
Thank you!  Very Promptly solved a long standing issue I have had.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24847092
Glad to help you out.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

713 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