?
Solved

Export a table - triggers, data and all

Posted on 2009-07-13
8
Medium Priority
?
1,367 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

770 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