What's your #1 way to be careful with a live database? [closed]

Question

BEGIN TRANSACTION;

That way you can rollback after a mistake.

Three things I've learned the hard way over the years...

First, if you're doing updates or deletes on live data, first write a SELECT query with the WHERE clause you'll be using. Make sure it works. Make sure it's correct. Then prepend the UPDATE/DELETE statement to the known working WHERE clause.

You never want to have

DELETE FROM Customers

sitting in your query analyzer waiting for you to write the WHERE clause... accidentally hit "execute" and you've just killed your Customer table. Oops.

Also, depending on your platform, find out how to take a quick'n'dirty backup of a table. In SQL Server 2005,

SELECT *
INTO CustomerBackup200810032034
FROM Customer

will copy every row from the entire Customer table into a new table called CustomerBackup200810032034, which you can then delete once you've done your updates and made sure everything's OK. If the worst happens, it's a lot easier to restore missing data from this table than to try and restore last night's backup from disk or tape.

Finally, be wary of cascade deletes getting rid of stuff you didn't intend to delete - check your tables' relationships and key constraints before modifying anything.

Do a backup first: it should be the number 1 law of sysadmining anyways

EDIT: incorporating what others have said, make sure your UPDATES have appropriate WHERE clauses.

Ideally, changing a live database should never happen (beyond INSERTs and basic maintenance). Changing the live DB's structure is especially fraught with potential bad karma.

Make your changes to a copy, and when you're satisfied, then apply the fix to live.