Scripts and Source Control - 102

In the comments section of my SCRIPT IT post, Lisa asked a couple of questions for clarification.  These were a good reminder to me that some of the things that I just take for granted these days are actually very new concepts to others who haven't had the same experience.  So rather than just post a reply in comments, I thought it'd be worth a post unto its own.  Lisa's questions are in summarized italics, with my responses following.

Q1) You stated: "Often in EM there is an option to 'Script Changes'”... I don't see any "Script Changes" in the BOL when I did a search. However, I did a search in the index of BOL and I find this: "script generating"...GREAT. However, when I open up the sql file, I was surprised by the amount of the code that is generated. I mean, there are about 60 lines of code!?!??!??! So far, I only created 1 db called, "TestingDB". I thought for a simple db, it should only a few line of commands.

A1) You found the option to generate scripts based on the current database settings.  This is a good way to get an intial snapshot in script form of your database.  As you have seen, there are many different options that will get scripted in order to have a script that would rebuild your database exactly the way it is now.  Many of those options are default settings and many people don't bother to keep them in their scripts, but the script generator produces everything it can.

Where I was talking about scripting the changes, I meant, for example, changing the code in a stored procedure, or changing a table definition.  Here's an example:  Open Enterprise Manager, open the pubs database, click on tables, right-click authors and choose Design Table.  You are now in the table definition grid.  Change the length of column au_lname from 40 to 50.  Now, BEFORE YOU SAVE the change you just made, on the toolbar there is a button that looks like a small disk with a scroll behind it (should be the third button from the left).  That is the "Save Change Script" button.  If you click it, it will generate a script in a window that represents the change you just made.  Then you can save that to a file.  But remember that here, too, the generator will provide a lot of standard default settings in the script and it also may not produce the most efficient way of making the change.  Because it's an automated process, it follows certain rules in order to generate a script that definitely will produce the change you have just made, but there may be other more efficient ways of achieving the same result.  As you gain more experience with different SQL statements, and since you can analyze the table and your needs, you may come up with shorter methods than what the generator produces.

Q2) You stated: "You can (and should) save your script files (usually ending with a .sql extension) into some form of source control (Visual Source Safe, Concurrent Versioning System, etc.). "
 - What is the benefit of saving .sql script into VSS or CVS?
 - Do I need a special softwares to open these files?

You also stated: "This allows you to know exactly what changes have been made, and if you ever need to “roll back“ you have a history of changes." I understand what you are saying but it is hard for me to visualize without real example.

A2)  The .sql files are just plain text so they can be opened by any editor, including Notepad.  Some people like to associate the .sql extension with Query Analyzer so if they double-click the file in Windows Explorer it opens with that.  Personally, I don't want the overhead of opening QA when all I want is to do a quick view of the text file, so I use a simple editor like Notepad for quick reading.  But I use QA for real editing because it has color formatting and I can execute tests while I'm working.

As far as the benefits of saving changes into source control, there are many.  One is that it's a central storage area, so you always know where to find your scripts.  Another is that it's a nice way to organize a history of copies of the file.  Source control will be able to show you not only the current version of your script, but also previous versions.  Most source control programs also let you do a "diff" which is a way of showing the differences between two versions.  This is very handy for answering questions like "Just exactly what changes were made the last time?"  I find this most useful when I'm working on scripts for stored procedures.  And you can check out an old copy of the script and execute it in order to "rollback".  For example, let's say you made changes to a stored procedure script, executed it in your database and saved (checked-in) the changes to your source control.  Later you discover that there is something wrong with the change you made.  Assuming that it is not significant enough to require restoring the database from backup, you could check-out the previous version of the script and execute it, thus effectively rolling back (undoing) the change you made.  Of course this all only works if you've been checking-in the changes to your script and thus producing a history of changes.

Some source control systems automatically check in any changes you made every time you save or execute.  I don't like this approach.  I don't want the previous version listed in history to be my previous typo or some unit-test run with part of the code commented out for testing purposes.  I want it to be truly the last version that was believed by the developer to be a working version, so I want it to be a conscious act to check-in updated scripts.

Hope that helps you Lisa, and all others who are coming in to this arena anew.

posted @ Wednesday, March 31, 2004 9:42 AM


Comments on this entry:

# re: Scripts and Source Control - 102

Left by Lisa at 3/31/2004 10:38 AM
WOW! That helps a lot. I am having fun to learn simple commands and with your help, I have more confidence. I am sure others at my experience level would find your posts very useful. Thx again.

# re: Scripts and Source Control - 102

Left by Avonelle Lovhaug at 3/31/2004 10:53 PM
Do you have a recommendation for keeping all these files straight, and executing them in the correct order? If I create my database, and then generate an initial script of the schema (stored in one file), then that's okay. But if I make multiple changes to that schema (and those changes are dependent on each other in some way), how do I keep track of the order those changes need to be made? Any suggestions for keeping it all straight in a some-what or semi-automated fashion would be appreciated.

In the past, I have ended up manually creating a script that included all of my db changes to be run on the production database when we went live. Invariably, I missed one of the db changes, and had to go back and regenerate it (or executed the changes in the incorrect order). I'm hoping for something a bit more oops-proof.

# re: Scripts and Source Control - 102

Left by AjarnMark at 4/1/2004 10:05 AM
Unfortunately, I don't have an automated process for this. You might check with Damian on his Antlog at He's doing some cool stuff with nAnt and usually has good tricks.

# re: Scripts and Source Control - 102

Left by Avonelle Lovhaug at 4/1/2004 11:03 AM
Thanks for the tip - I will check that out.

# re: Scripts and Source Control - 102

Left by Tara at 4/1/2004 4:03 PM
I keep a checklist of the order in which to run the files. I've got a large deployment coming up at the end of April which involves running about 40 files. I purposely broke them up into 40 files so that it would be easier to troubleshoot problems. I created a Word document that lists the order of the files. Before deploying to production, I always run through the deployment a couple of times in a "staging" environment. Just went through that today as a matter of fact.
Comments have been closed on this topic.