Versioning SQL Server Stored Procedures and other db Objects in VSS (Part I)


Q: ‘How can I backup my SQL Server stored procedures, triggers, UDFs, and
other database objects using VSS?’


A1: If you work in Visual Studio .NET 2003 with
source control integration, you can opt to install a SQL Server versioning
component that allows you href="http://msdn.microsoft.com/library/en-us/vsintro7/html/vxurfenablesourcecontroldialogbox.asp?frame=true">to
add sprocs to source control and perform checkins, checkouts, and other SCC
commands in the VS.NET IDE .*  As href="http://weblogs.asp.net/Yreynhout/">Yves Reynhoot says however (and
he’s right on the

money):


Why people still use this goes beyond me … Use a VS.NET database
project and you’ll be able to version every object in your database. The fact
that it is a central and consistent solution can only play in it’s
advantage.”


A2: If you’re not a Visual Studio .NET developer, the
answer is a bit more involved.
VSS is a file

management solution. It won’t accept
nickels, neutrinos, dimes, euros, objects, paragraphs, sprocs, widgets, user defined functions,
or any other sub- or super-file items. Since SQL Server database objects (dbos) are
objects, not files, the first step in versioning them is to convert them files.


I’ve seen one script and have heard several interesting ideas for how
to version SQL Server database objects using VSS.  None are as
automatic or as comprehensive as the one I have in mind.  My goal is to
create a script that adds all SQL Server database objects (table schema, stored
procedures, UDFs, etc) to a VSS database and backs them up routinely and
automatically.


Over the next few days, weeks, or months, I
plan–nay, hope–to create a SQL Server database object versioning script
on(blog)line.  In airing my unedited thoughts and
thought processes in live blogtime, I will probably reveal many of my otherwise
hidden technical deficiencies and blindspots. Feel free to ridicule or encourage
me, highlight flawed assumptions, correct
coding and grammatical errors, propose alternative solutions, and post your code in the comments section of
these posts.  If I think your feedback merits more attention, I will certainly post it
in the body of a future blog post.  As always, Microsoft and I
reserve the right to remove any comment, at any time, for
any reason (not that I’ve ever done so or wish to do so in future). Your
comments and suggestions are greatly appreciated.


style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Verdana; mso-fareast-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">* Fabrice’s Blog provides a href="http://weblogs.asp.net/FMARGUERIE/archive/02242003.aspx">step-by-step how to for setting up stored procedure
versioning in VS.NET. href="http://weblogs.asp.net/Mroselius/posts/2882.aspx">Rosey’s Blog, which points to Fabrice’s post, provides
good background reading.


style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Verdana; mso-fareast-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA"> class=stilemessaggiodipostaelettronica18> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Il
size=2>presente posting viene fornito “così come é”, senza garanzie, e non
conferisce alcun diritto.

Comments (13)

  1. Kevin Greiner says:

    re: "I plan–nay, hope–to create a SQL Server database object versioning script "

    Korby, I have a script that already does this. It’s really two scripts; one to dump the entire database schema to disk and the 2nd to check in all changed objects to VSS. The author of this product, http://www.sqlservercentral.com/products/bwunder/archiveutility/, sent me these two WSH scripts about a year ago. I’m updated and modified them considerably but the base functionality is the same. Contact me directly if I could be of assistance to you in this effort.

  2. Julia says:

    Hi, can I contact you by ICQ ? Spasibo.

  3. hurcane says:

    I’ve posted a review on my weblog of two of the up-and-coming products that are supposed to help with this issue. They try to solve other problems, too, which I also discuss.

    If one person reads it, that will double my readers! 🙂

  4. I just want to say THANKS to all people in this community. You really help me.

  5. Milking Tits says:

    how can i save this page on my hard drive ?

  6. Rod says:

    I’ve been looking into this same issue. I hadn’t discovered Database projects until this blog (thanks!) and really like their convenience. The downside would be less performance compared with SPs (yes?). However, the ease of creation, management, and source control sure is attractive. The piece I haven’t figure out yet is how to use the queries from within my project. The help pages have not been clear on this.

  7. louise says:

    stored procedures vss and vs.net – does this work with VSS Version 5

  8. Hi Louise,
    I don’t know. I think it might require VSS 6.0, which happens to come for free in the Enterprise Architect, Enterprise Developer, and MSDN Subscription versions of Visual Studio .NET 2002 and 2003.

  9. hgh age says:

    A little late but I want to wish you a good luck in this year Happy New Year