Solved

Export a table - triggers, data and all

Posted on 2009-07-13
8
1,247 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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

862 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now