“You DO have a separate development environment, don't you?”  I've made a comment like that a few times here on the blog, and to an experienced DBA it seems so obvious, but let's talk about it just to be sure (and for those people just starting their careers).

Here's the gist of the statement:  you don't want to be doing development work in your live (sometimes called production) database because sometimes things go wrong, and that would be bad.  So what do you need?  Well, if you can afford it, separate servers for one or more development environments, one or more testing environments and your live environment would be great.  But even if you're strapped for cash (like the now-defunct dot-com I worked at) you should at least have separate dev/test/live databases on your one server.  Or in SQL 2000 you might want to run multiple instances, although I'm not sure the effect on licensing of that approach.

Okay, so you understand you don't want to mess up your live data by doing dev work there, but why a separate test database?  To test your deployment process, and to make sure your system works with “real” data and not just your dev data.  It's not unusual for a developer to manually manipulate the data in his dev environment in order to specifically test some feature.  I have seen this lead to situations where an application worked fine in the dev environment but when released to a live look-alike (QA) it failed miserably.  I have also seen, on numerous occasions, where a SQL script was left out of the deployment package and the whole upgrade failed.  If you went straight from dev to live, your live system is now down while you scramble to figure out what the missing piece is, and sometimes scripts need to be run in a certain order due to dependencies and now you'll have to restore your live system from backup (you DO backup your database, don't you?  Especially right before you apply updates.  And you've checked to make sure it was good?) and start the deployment process all over again.  If this is done in a test environment, the impact is greatly reduced.  Your test environment should be easily recoverable to a known good version (of both schema/code and actual data).