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 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.
