How to Transfer objects from test SQL DB to Production DB

I am new to SQL, using 2014 Developer Edition

Since we are still working exclusively with the developement DB I have not run into this situation but I'm wondering how it will work when I do.

I wondering how to move new or changed, non-table objects from a test SQL DB to the production DB.

Example:
In our in-house development environment of the SQL DB we create and test a new stored procedure 'spNew' to handle a lookup in the production DB.  How do we move just 'spNew' to the production SQL DB?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Éric MoreauSenior .Net ConsultantCommented:
The best way is to script your objects from the DEV environment and run them on the prod server.

I really like to use http://www.red-gate.com/products/sql-development/sql-compare/ which is a tool that compares 2 databases (your dev and prod for examples) and shows the differences. It can then create a script to fix the differences.

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
Jim HornMicrosoft SQL Server Data DudeCommented:
>How do we move just 'spNew' to the production SQL DB?
The quick and dirty answer is open the SP, log into that server, change the database name, and execute.

To handle the above with more than one object, SQL Server Data Tools (SSDT) > Data Project > Tools menu > Schema Compare ... > New Schema Comparison, which allows you to define a source and destination db's, compare objects between them, and all/selectively execute each object from one to the other.   There are many other tools that have this capability.

The 'Change Management' answer is to have some kind of software that deploys objects, and to get approvals.
mlcktmguyAuthor Commented:
Thanks to both of you for these options.  I have not had time to evaluate them and probably won't in the near future so I am splitting the points between you.  It is nice to know in general that the situation I was concerned about can be handled.
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

From novice to tech pro — start learning today.