I am trying to figure out mechanisms for upgrading the DB. Presently i use SQL 2008.I have read that there are some tools (free as well paid) to check the difference of DB schemas. Also there is tablediff in SQL 2008. Now i was thinking of upgrading the DB through scripts with the installers. I will be maintaining the DB versions in the system as well. How do i use the information from the tool and upgrade the DB in a client system? I can give an use case: Suppose client has a DB schema version 1.2, I want to upgrade the schema to 1.5 . How do i do that? should be running the scripts of 1.3 and 1.4 as well? whats the better solution in this case.
There are a number of tools that compare schema between two databases. I work for Red Gate software, and we make one called [SQL Compare]. If you have Visual Studio 2012, Microsoft has a free one built into the SQL Server Database Tools (SSDT) which is OK. Either will work for what you're looking for. But to really get this under control, you need to be looking to place your database's structures into source control. That way you can version them very carefully, knowing exactly what is in 1.3,1.4,1.5, etc. If you do that, then you can either generate a change script to go from 1.3 to 1.5 all on your own, or you can stack up the change scripts from 1.3 to 1.4, to 1.5. It really depends on if you can know what version the client is going to or not. If you know, generate a single script between the appropriate versions. If you don't, create all the scripts you need and pass them on. Red Gate has an additional tool that can help you here too. It's called SQL Source Control. It allows you to use whatever source control system you want directly from within SQL Server Management Studio. But most of the work you'd need is covered by SQL Compare. I wrote a few chapters in the book [SQL Server Team Based Development]. The tools I use are a little older now (the book is from 2010), but the concepts are the same. You can download the book for free from here. :