[dba-SQLServer] Version Control

I tried replying to Mr. Stewart but maybe the reply was too lengthy. For the
benefit of others interested in how to use VSS with SQL, I am posting this
(hoping it won't appear twice):

 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 <http://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 <http://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 <http://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
<http://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 <http://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