January 7, 2008 at 9:46 am
Hi guys,
I have some stored procedures, UDFs in my development server and i need to move them to the production server...
Any suggestions how to do it????
January 7, 2008 at 10:32 am
1) Have a backup of the existing udf's,stored procs
2)
You could create a script and execute it on the other server
Enterprise manger->rightclick database->generate sqlscript
->select the udf's/stored procedures
->choose your options (be careful with drop existing objects)
->save script
open query analyzer on the other server
open script
check connection settings
execute script
check permissions
3)
There are also different software tools to help you with comparing/moving databases.
SqlToolbelt from Redgate has a 14-day trial (click red banner at the top of the page)
January 8, 2008 at 12:53 pm
I deployment and rollback scripts for everything as a matter of course that way I know I have a way of installing and uninstalling software in a hurry if need be.
The Red-Gate SQL Compare 6 is pretty good as it will list all objects that are different between the two environments. You can also create a SQL Compare snapshot of the database before you do anything drastic.
The main things to worry about are
Permissions differences between development and production environments. Always grant permissions to database roles rather than logins.
Ownership problems. Ideally pre SQL2005 objects should be owned by the dbo.
I have seen replication create multiple instances of stored procs and EM has trouble scripting these sometimes in which case fall back to sp_helptext.
January 11, 2008 at 4:09 am
You may also want to try out SwisSQL DBChangeManager which performs similar to Red-gate SQLCompare (ie. compare and synchronize sql server database schema objects). DBChangeManager has the added advantage that you can filter the objects that alone needs to be compared/synchronized as against the entire database. Added to that is the fact that the product has a web interface which makes it accessible from anywhere on the network through a browser.
More importantly, for smaller size databases, you can use the Free version of the tool. Yes, you can continue to use the tool to synchronize upto 10 objects at a time for FREE (even after the trial period expires).
Visit http://dbchangemanager.com for more details.
Sathish
PS: I work for SwisSQL DBChangeManager
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply