February 27, 2009 at 1:33 pm
Can anyone offer their thoughts/suggestions on how to best breakup scripts for managing a db? For instance, I'm starting a new project, and have already used SQL Server Manager to create the tables, pk's, indexes and the relationship between tables.
Moving forward, any changes that I may apply to the db (e.g. new table, new index) will have to be saved to the db. Should I create scripts for any incremental changes? What is the best way to capture these changes? Is there something in sql server manager that will say "capture changes" and script just the changes??
I guess the advantage of scripting all of your incremental scripts would be to easily back out changes that may have caused problems with your code.
Do you create one giant script with a bunch of ALTER statements for your VIEWS, SP's, FUNCTIONS, etc...?
Thanks
February 27, 2009 at 1:59 pm
You could look at using some sort of source control like source safe, subversion etc. and script out each table, sproc etc. then you can check them in and out like you would any other code.
I believe the Data Dude Visual Studio Team edition may have some better automated functionality for doing some of this, but I've never used it and can't speak from any expirience whatsoever.
-Luke.
February 28, 2009 at 6:05 am
I'd suggest getting all the database objects into source control. Then, make all your changes through the scripts, not by editing the database through Management Studio. You should very carefully label versions of the code in source control. Then, when you're ready for a deployment, you can get all the changes that have occurred since the last label very easily out of source control. You may still need to manipulate these scripts manually in order to protect the data in the database. This all assumes you've got at least two servers, the production server and a dev server. Better still to introduce other interim servers such as a QA and possibly a Production Staging environment.
My preferred tool to help manage all this is Visual Studio Teams System Database Edition. It's fantastic as a development and management tool. Another tool I'm experienced with that can help in this area is Red Gate's SQL Compare. It can manage database objects through code very ably. A tool called DBGhost is also supposed to work in this fashion really well, but I don't have any experience with it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply