[dba-SQLServer] Source code version control

Arthur Fuller fuller.artful at gmail.com
Thu Jun 7 06:04:00 CDT 2007


Your general strategy is correct, IMO, but I would be tempted to take it a
little further into the past, if possible. This depends on the state of your
backups and available disk space. Think back to milestones of significant
change and see if you have backups before and immediately after such
changes. If so, then if I were you, I would start there, and build up a
history in VSS that culminates in the current build.

Among other things, this would enable you to re-create any past version
relatively painlessly. In several situations in which I worked, this was a
paramount concern, because clients 1, 2 and 3 had build x.yy while clients
4, 5 and 8 had build x+1.00. Should a customer call in with a bug report on
any version, VSS enabled us to quickly recreate exactly the version we
shipped and then duplicate the bug and then address it.

Why is this important? The larger the customer, the more important it is,
IMO. Your developer-code may have moved on from Vx to Vy, and in Vy fixed
this particular bug. But the client doesn't want to migrate; he wants
minimum ripples throughout the firm. He want this and only this bug fixed
and *nothing* else changed. IME, the larger the client firm, the more this
holds true.

That among many other things is why VSS (or any other version control
system) is so vital.

So. Two possible situations pertain: do you have VS.NET <http://vs.net/> or
not?

Yes: even then, it depends on which version you have. Standard and Express
lack features that the expensive versions have.

With VS.Studio:

0. Ensure that VSS is installed and create at least one user besides Admin.
1. Create the database project in VS.NET.
2. Open Solution Explorer.
3. Make sure that your database is one of the Database References. If the
reference does not already exist, right-click Database References and select
New Database Reference to create one that points to your database.
4. In Server Explorer, expand the Data Connections node, then your database
and keep expanding until you get to your list of stored procedures.
5. Right-click one sproc and choose Generate Create Script to Project. The
program adds the script to the Create Script folder of your project.
6. Navigate to the list of tables in your database. Choose a table and
right-click and select Generate Create Script to Project.
(Note: the Generate Create Script command may not be available in your
version of SQL 2005. If it is not available, you can generate scripts using
the Script Wizard. See below.)
7. Now we add the project to VSS. From VS.NET, select File | Source Control
| Add Project to Source Control. Log in on the next dialog.
8. VSS prompts you to add a project. Create a name and optionally add a
comment.

VSS creates a project and locks all the Create scripts you generated. Their
locked status is indicated by a lock icon beside their names. From this
moment on, you must check out a Create script before you can change it. To
view a script from within VS.NET, right-click its name and select Open. This
is a read-only view.

To change a script:

1. Close the View window.
2. Right-click the script name, then choose Check Out. The program prompts
you for a comment.
3. Make some change to the script.
4. Save the changed script and close the window.
5. Right-click the script name and choose Check In. VS.NET saves the new
script in VSS. You now have two versions of the script in VSS. That's why
it's important in Step 2 above to supply a comment that describes the change
you made.
6. *This is important*: if you follow these steps exactly, the stored
procedure *does not yet exist in the database*. To create it there, you must
right-click its name and select Run to deploy it. However, it probably makes
more sense to run the script and fix any errors before adding it to VSS.

--------
Without VS.NET

Use Management Studio to generate scripts for all objects in the database:
1. In Object Explorer, pick a database.
2. Right-click its name, then choose Tasks | Generate Scripts (not Script
Database As | Create To).
3. The Script Wizard opens. Select a database. In the next step, you can
select the scripting options.
4. On the next page, you can select which object types you want to script.
5. For each selected object type, you then get a page listing them and can
select the ones you want to script. (You can even script CLR objects if any
exist.)
6. Last, the wizard prompts you for a destination (file, clipboard or query
window).
7. Now run VSS, create a project and add the scripts you just generated.

hth,
Arthur

On 6/6/07, Robert L. Stewart <rl_stewart at highstream.net> wrote:
>
> Thanks Arthur.
> Further info.
>
> We have a large db in place with multiple instances of it.
> The web guy's bright idea was to take the time to put
> each individual object in VSS then keep it up to date.
> He got rather upset when I told him "No. It needs more
> thought." What I cam up with was to check VSS and see if
> an object existed. If not, add the original object and
> then the new object. This would build the VSS repository
> as we made changes. The idea was to be able to go back
> to a previous version of an object if needed.
>
> Robert
>
> At 12:00 PM 6/6/2007, you wrote:
> >From: dba-sqlserver-bounces at databaseadvisors.com
> >[mailto:dba-sqlserver-bounces at databaseadvisors.com ] On Behalf Of Arthur
> >Fuller
> >Sent: Tuesday, June 05, 2007 11:15 AM
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: Re: [dba-SQLServer] Source code version control
> >
> >I thought that I responded at length to you. It's not in my drafts. Maybe
> I
> >accidentally discarded it. I described the two ways of using it, one with
> >VS.NET <http://vs.net/> and the other without. I'll see if I can dig it
> up and re-send it. If
> >not I'll redo it.
> >
> >Arthur
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>



More information about the dba-SQLServer mailing list