Majid Sadeghi G.

Many of us use MS SQL server in our programs. As a matter of fact, the best way to have a DB in .NET  or Microsoft programming tools is that. I know each SQL software has its own abilities, but this post of mine is for MS SQL.

During my experiences about MS SQL Server as a simple user not a SQL administrator, one of the biggest problem is how we can make our different databases up to date. For example, you have a program that uses MS SQL server. You sell it to 10 different companies and you should support them with their new needs. Instead of updating executable files and DLL and components, you should keep your database updated. We have some ways for updating DB:

1. Getting a new backup from Old database and create a new instance and restore new empty DB on it, then move all information from the old one to the new one.

2. Making Scripts from each changed items (Tables, Views, Functions, Triggers, …) ordered by date of changes, then step by step run them in the target DB.

3. Other than Tables that updating them need scripts that has been taken from MS SQL server when we changed them, we can Update whole Views and Functions without paying attention to which one of them has changed.

Maybe we can find another way more than what I’ve said but I can remember these only in this time!!

So, each of those states has its time to use. I’ll try to describe it more.

In first state, we are replacing our Old DB with a new one with just replace and transfer. This is a good solution but it’s really hard to handle that which tables should transferred into new one. But in some cases, we’ve experimented if the DB is really out of date and making script is not a good way, we can do this one as a last resort. (The Last way standing )

Second way is the most popular way to handle the updating. You can make a Log from all your changes and then each time you want to update your DB, you just need to know what time you had updated your DB last time, then you can update your old DB with series of scripts that you have made them in a period of time. Surely it is a good way with all of our details about which parts need to be updated, but in some situations it takes a long time or does not the best choice we can have.

The third way, is one of the most efficient ways to update our old DB, in this way you keep just Table’s information. In fact, this step is a kind of step 2, but we decreased the mount of information we should keep. So what’s better than this? we optimized our information. we just keep as much as information basically we need to have, and other parts of our database will be updated. It does not matter which one of them should be updated, We update all of them and we know Views and Functions do not making any damages to our information which stored in our DB(Tables).

At the end, I coded one simple program for handling  state 2 and 3. In the next post, I will explain that program.

Thanks For reading,

Dijam