Updating live server

[aTekTipsUser]

I have set up a server (my computer) to be a development server. I have copied the databases to the development server and have made changes. How do I get the changes that I have made to the database (edited/deleted/added tables, stored procedures, etc) to the live server without affecting the data. I had set up scripts to import the live server data into the production server and set the production server as the live server, but they can be a pain to maintain. Any suggestions would be greatly appreciated.

 

[Catadmin]

When you refer to changes, are you speaking about data changes or schema changes or a combination of both?

Usually the way we do it at our work place is that we have 5 Template scripts we use for every release.

Tables
Functions
Views
Procedures
Data

Each script (run specifically in the order listed above) has only the schema/data changes specific to what it is named as. So, tables contains ALTER TABLES scripts with constraints, indexes, triggers, etc. PROCEDURES contains CREATE/ALTER PROCEDURE scripts.. Data, of course, contains any Inserts/Deletes/Updates to the actual data.

Once we've tested our application in Test & QC to make sure none of the changes we've made breaks anything, we run the scripts in the above order against the Production database.

Does this help?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[aTekTipsUser]

Thanks for the response!

I'm referring to schema changes. I add a new field(s) on the development, and I want to update the live server. Or I add/edit new procedures and want to update the live server.

So you keep a list of all the changes that you make? What if you add 5 new procedures, 2 views and 8 functions, edit 3 other procedures and 2 other functions, add a trigger and edit 3 other triggers? Do you maintain this list or run the alter/create table/procedure/function for all the tables/procedures/functions in the database?

My main problem is remembering all of the changes that I make, which could span a month or more.

 

[SQLSister]

Look at a product called SQLCompare by RedGAte. It will script the differneces between the two systems.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[mrdenny]

This is where documentation comes into play. Each change to the system should be based on a request from the business or customer. With each request for a change you should track what objects are changed within the database.

When you release the change to production you document that change in the initial request and check the changes to the objects into what ever version control you are using.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com