asked on

Export a table - triggers, data and all

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!!

Raja Jegan R

>> 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

Jay1607

ASKER

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

Raja Jegan R

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.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jay1607

ASKER

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

Raja Jegan R

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.

Jay1607

ASKER

Thank you! Very Promptly solved a long standing issue I have had.

Raja Jegan R

Glad to help you out.