Compartilhar via

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

  • Artigo

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 to
add sprocs to source control and perform checkins, checkouts, and other SCC
commands in the VS.NET IDE .*  As 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.

* Fabrice's Blog provides astep-by-step how to for setting up stored procedure
versioning in VS.NET.Rosey's Blog, which points to Fabrice's post, provides
good background reading.

Il
presente posting viene fornito “così come é”, senza garanzie, e non
conferisce alcun diritto.

Comments

  • Anonymous
    September 08, 2003
    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.

  • Anonymous
    September 15, 2003
    Hi, can I contact you by ICQ ? Spasibo.

  • Anonymous
    September 24, 2003
    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! :-)

  • Anonymous
    October 04, 2003
    I just want to say THANKS to all people in this community. You really help me.

  • Anonymous
    November 09, 2003
    how can i save this page on my hard drive ?

  • Anonymous
    November 19, 2003
    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.

  • Anonymous
    November 25, 2003
    stored procedures vss and vs.net - does this work with VSS Version 5

  • Anonymous
    December 01, 2003
    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.

  • Anonymous
    January 06, 2004
    A little late but I want to wish you a good luck in this year Happy New Year

  • Anonymous
    June 01, 2009
    PingBack from http://paidsurveyshub.info/story.php?id=76816

  • Anonymous
    June 08, 2009
    PingBack from http://cellulitecreamsite.info/story.php?id=6486

  • Anonymous
    June 16, 2009
    PingBack from http://fixmycrediteasily.info/story.php?id=9286

  • Anonymous
    June 19, 2009
    PingBack from http://debtsolutionsnow.info/story.php?id=4573