Even if you have a test system to try out some new functionality, at some point it needs to be deployed to live. If you are lucky, this is done by copying the up-to-date test system over the top of live but in reality, most of us have to modify the live database directly, perhaps during a small time-slot and then it is expected to work.
There are a number of problems that are associated with live databases, specifically SQL server in my case, that are worth knowing before you go and break something.
  1. If you can, avoid altering or removing any tables/columns in the first place. If you can avoid this and perhaps leave a few dead things in place, you are less likely to break something.
  2. If you can use some tools like Red Gate to search for references to your objects, it can help you track down what might break. Red Gate also make a tool for moving objects from a test to a live database in a GUI rather than manually.
  3. For any objects that are accessed by something external to the database, mark these with schemas so you know that there might be knock on effects to other systems. e.g. webapp.procUserFetch
  4. Avoid selecting * from anything since this hides whether a specific column is referenced or not.
  5. If you are adding a new column, make sure it is nullable, even if it ultimately won't be. This avoids any existing inserts failing due to the lack of a value for the new column.
  6. Try not to rename columns if you really don't need to. You can always alias them in a select statement.
  7. Adding new tables, procs and views is safe
  8. If you change a table structure, you must use sp_refreshview on the views that reference it otherwise their cached query plan can be out-of-date and you won't know until some funny data is returned in the select.
  9. If you are deleting objects, rename them first or move them to a dead schema so that they can quickly be restored if there are any problems. You can put the rename date in their new name and after a week or so, delete them permanently.
  10. If you can, script the new or changed objects and store them in a repository so you can track and blame changes that are made.
  11. Use the format procNameVerb when creating stored procedures so that like procs are listed together e.g. procUserInsert rather than procInsertUser.
  12. Have a backup of the database if you are making major changes so that you have a last-resort way of falling back to a known-working copy.