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.