Easy code archiving in SQL Server 2005

DBA Tim Chapman extols one of the virtues of SQL Server 2005 is the ability to return the entire code of an object from a system function. He notes that this function will make it much easier to archive your procedure code.

As a database administrator, I always try to give code that I promote to our production environments a thorough look before the move is made. However, as much as I hate to admit it, I can't catch everything that may disrupt our production system. When these situations occur, sometimes the remedy is to revert back to a previous version of the object code, which can be a stored procedure, view, function, etc. What you do not want to have to do, if possible, is have to restore the code from a database backup. It often takes too long to get to the backup if it is stored on tape, and if the database is large, it takes quite a while to restore. Not to mention that you have to find a server large enough for restoring the backup file. There's got to be a better way.

A solution I created a long time ago was to back up the database code to a separate table, so that in the event of an error in our production code I could rebuild the procedure or function from that table. This solution has absolutely saved me hours upon hours of time.

The way this can be accomplished in SQL Server 2000 is to make a copy of the syscomments table for a specific database nightly and place that copy in an archive table. I usually keep up to two weeks worth of procedure code. The only cumbersome thing about this technique is that if the code object is a large one, the code would have to be rebuilt because the code would be contained on separate rows in syscomments, which could sometimes be a pain.

One of the many great things new to SQL Server 2005 is the ability to return the entire code of an object from a system function. This function will make it much easier to archive your procedure code.

complete read this article visit at : http://articles.techrepublic.com.com