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!!
Jay1607Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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
Jay1607Author Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay1607Author Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Jay1607Author Commented:
Thank you!  Very Promptly solved a long standing issue I have had.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Glad to help you out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.