Easy code archiving in SQL Server 2005

by Tim Chapman | Jan 12, 2007 8:00:00 AM

Tags: Databases, Backups, archiving, Tim Chapman, Microsoft SQL Server, database, procedure code, backup, Microsoft SQL Server 2005, SQL Server Newsletter

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

OBJECT_DEFINITION

The new SQL Server 2005 system function OBJECT_DEFINITION will return the TSQL code for the ID of the object supplied to the function. To make a little more sense of how this works, let's take a look at an example. First I will create a user-defined function. The script for this function is below.

CREATE FUNCTION udf_Multiply

(
      @Val1 INT, 
      @Val2 INT

)

RETURNS INT

AS

BEGIN
      DECLARE @RetVal INT
      SET @RetVal = (@Val1 * @Val2)


      RETURN(@RetVal)

END

This is a silly function because it only multiplies two numbers, but it will do a good job for us to illustrate how OBJECT_DEFINITION works. The script for testing this system function is below.

DECLARE @ObjectID INT

SET @ObjectID = OBJECT_ID('udf_Multiply')



SELECT OBJECT_DEFINITION(@ObjectID)

In this example, we actually use two system functions. First, we retrieve the OBJECT_ID, which is a system identifier for an object in the SQL Server database engine, for the function we created above.  We then pass this ID to the system function OBJECT_DEFINITION, which returns the object code for the ID provided. The value returned by this function is the TSQL code that we wrote earlier for the udf_Multiply function.

Now that we have a good idea of how OBJECT_DEFINITION works, let's take a look at how we can use this function to archive the procedure code in our database. First, run the script in Listing A to create 20 stored procedures in your test database.

You'll see in the above script that we are using dynamic SQL statements. Typically when I create dynamic SQL statements I prefer to use the system stored procedure sp_executesql, as it does a good job of caching the SQL statement in the system. However, for our example, the EXECUTE command will work just fine.

Now that we have some objects in our database, we can create the necessary objects and code to archive these procedures. The script in Listing B will accomplish this for us.

Our archiving solution first requires a table to store the definition of our code in. As you can see in the table script above, we store the definition of the object in the ObjectDefinition field, which is a VARCHAR(MAX) data type. This data type, new to SQL Server 2005, can store up to 2GB worth of data. So, we are no longer constrained to TEXT data types or keeping our data on one single data page. This data type will have no problem storing the code from our objects.

There are a couple of considerations worth noting in the script above concerning the insertion into the CodeArchive table. First are the types we are including in the query. These are the object types for which OBJECT_DEFINITION will return a value. A listing covering these object types are listed below. Second is the manner in which this script should be invoked. I typically execute script similar to this one once a night using a SQL Server scheduled job. (You can run it more or less frequently as your business needs dictate.) Regardless of how it is called, you'll need to run this script on a regular schedule so that you are able to restore your procedure code when necessary.

Here is a listing of the object types available for use by OBJECT_DEFINITION:

• T: Check Constraint
• D: Default
• P: TSQL Stored Procedure
• FN: TSQL Scalar valued User Defined Function
• R: Rule
• RF: Replication filter procedure
• TR: TSQL Trigger
• IF: TSQL Inline Function
• TF: TSQL Valued Function
• V: View

The need to archive

I hope you find this article useful, and try the new OBJECT_DEFINITION function out for yourself if you haven't already. However, I cannot stress enough the importance of having some type of code backup system in place for your environments. I actually have a system similar to this for all of our environments, development through production. If you are able to set up a system similar to the one I mentioned in this article that will make a local or remote backup of your procedure code, you will be in very good shape in the event that you have to restore code and not have to travel to backup to retrieve it.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.