Maintaining SQL Server Databases

BenL — Thu, 15 Jan 2004 02:40:00 GMT

I read an interesting article by Eli Robillard recently. The very top of the article quotes Hlade's Law, "If you have a difficult task, give it to a lazy person - they will find an easier way to do it." Ambrose points out the benefits of lazy people in military positions in the comments. I enjoyed reading these things because it made me realize that I approach my work in a similar way. But I digress...

In keeping with the spirit of “The Lazy Programmer” I am going to briefly describe a method a friend of mine showed me for maintaining SQL Server databases. I will not outline the details of the approach. Instead, I will provide a brief overview and reserve any details for a (possible) future blog article.

The method my friend showed me involves setting up a main folder for your database with various sub folders to hold the different pieces. For example, you might have a root folder called “Northwind” with sub folders called “SP” (for stored procedures), “Table” (for tables), “PK” for primary keys, “FN” (for user-defined functions), etc.

The contents of all of these folders contain script files to create each item. Sticking with the North wind example, the “Table” folder would contain files called “Customers.sql”, “Orders.sql”, “Employees.sql”, etc. The “PK“ folder would have script files for creating primary keys on the tables in the database. Obviously, databases with lots of objects would have many script files in using this approach.

After all the .sql files are created there are a couple of batch files that you run to extract all of the individual scripts into one, large script file that can be run in Query Analyzer to drop and rebuild the entire database. These batch files can easily be run from other files or processes (say a giant application build script).

It took some getting used to, but I have found working directly with the script files (to create tables, write sproc's, create indexes, etc.) to be much more efficient than doing things in Enterprise Manager. For example, after creating the script to create a table, copying the column names and data types into another script file for an Insert stored procedure is very easy. Also, using the regular expression capabilities of a tool like Visual Studio.Net really speeds up development time.

I would be more than happy to share more about my experiences with this method. What do you find useful in creating/maintaining SQL Server databases?

BenL's WebLog

Maintaining SQL Server Databases