Wednesday, March 23, 2011

Exporting/Importing Stored Procedures between DBs

I have two SQL Server 2005 databases, one is for development and the other is on the final production server. I would like to know the fastest way of ensuring that the production database has the exact same stored procedures (number and most recent version).

Assumptions:

  1. Databases have same table schema.

  2. Production database is currently in use and has more recent data than development database

  3. Development database has stored procedures that do not exist on production database

  4. Development database no longer has [outdated] stored procedures that are currently found on production database.

  5. Development database has more recent versions of stored procedures than those currently found on production database.

Edit: Note the servers for the two databases are not on the same network and production database is behind client's firewall (we have a VPN established with the server and can remote desktop in)

From stackoverflow
  • SqlCompare by Redgate is a great tool for doing this.

    It basically gives you a diff of all stored procedures and schema differences between two databases and gives you the option to sync up a selected set of differences, or generate a script to do the same.

    TheTXI : I have the RedGate SQL Toolbelt but not SQLCompare.
    PhilPursglove : SQL Toolbelt includes SQLCompare.

0 comments:

Post a Comment