Upgrade package

Question

Hi,
I'm new around here, but I got some (few) experience with SQL, and now I need your help.

I'm currently developing solutions supported by sql server 2005. When I install the application on the client I have a script to build the database. So far no problem.


But, as all in life, things change. And I need to upgrade the database in the client's server. My method is to compare the database installed on the client, and the new one with the changes and make a script to aplly to it. This is not very practical, cause things are not done in an automatical way, it's manual. These changes can be, new stored procedures, functions, parameteres, tables columns, etc.

I would like to know if there is any tool that can help me to do this task. Or if there is any more practical way of doing this.

Any help is appreciated.
Thanks in advance.

cheers.

Answer 1

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/


SQL SERVER – 2005/2008

Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

 

Answer 2

What I do in cases like yours is:

1. Add a new table called Parameters with two attributes Key and Value
2. Then add new Row, where Key="DB version" and Value=<version number>

Now the script doesn't have to compare the database, because it has the version of the DB. Each version brings some changes (new scripts to add, alter, or drop existing objects).

When upgrading, it checks the DB version, and continues with the next version changes (Ex: if current DB Version is 3.1 then it executes the scripts of version 3.2 and above). At each successful version applied, it updates the database version.

Answer 3

I have been using SQL Compare tool from www.red-gate.com for many years  without problems

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/