Like most development groups, we develop our database code in a specific development environment and then promote it to different environments for testing and production. Storing scripts for tables, views, procedures, functions, etc. is pretty straightforward, but what about static (lookup) data? We wanted to have a way to back up this data in development and staging environments and version it in Subversion just like the database object code.

Our requirements for exporting static data were as follows:

• Data should be easily human-readable and comparable between versions
• Data should be easily importable back into SQL Server for disaster recovery
• Data should be checked into Subversion nightly
• The process should allow for auditing distinct data changes to see point-in-time changes and who made the change
• The process should work in development, staging, and production environments
• Only certain databases and tables within those databases should be exported
• The output filename should be the table name
• The exported file should be a plain text file with column headers and text-qualified fields
• The file should support the Unicode character set for foreign language characters

Initially, we set up a SSIS package to export the data, but it required a lot of maintenance as new tables were added or existing tables were modified. We then looked into using BCP and SQLCMD, but they had their own limitations. Finally, we found a solution using a SQL CLR procedure that met all of our requirements.

The solution involved creating a SQL CLR procedure that mimicked the functionality of BCP. The procedure exported the data from the specified tables and saved it as a plain text file with column headers and text-qualified fields. We used the SQL# (SQLsharp) CLR library to simplify the process.

We also created three tables to store information about the environment, servers, and databases:

• StaticDataExportSourceServer: stores details about the environment and server names
• StaticDataExportSourceDB: stores the databases to export from each server
• StaticDataExportException: lists the tables that should not be exported

We scheduled the export process to run nightly via a SQL Server Agent job. The process exported the data from the specified tables, added any new files to Subversion, and committed all updates to Subversion.

While the solution met most of our requirements, we still needed a way to audit distinct data changes. We will explore this topic in a future installment.

In conclusion, using the CLR within SQL Server can be a powerful and flexible tool for exporting and versioning static data. It allows for dynamic exports and eliminates the need for manual maintenance. By using the CLR, we were able to integrate the export process into a native T-SQL procedure and create a general-use function for exporting dynamic data.

Disclaimer: This article is based on the personal experience of the author and does not reflect the views of the author’s employer.

Author: [Your Name]