Solved

Export a table - triggers, data and all

Posted on 2009-07-13
8
1,318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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 …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

734 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