For my customer I occasionally do work in their live database in order to fix a problem they have created for themselves, or in order to fix bad data that my product's bugs created. Much like Unix root access, it's just dangerous. What lessons should I learn ahead of time?
What is the #1 thing you do to be careful about operating on live data?
Make your changes to a copy, and when you're satisfied, then apply the fix to live.
Always make sure your UPDATEs and DELETEs have the proper WHERE clause.
Make sure you specify a where clause when deleting records.
always test any queries beyond select on development data first to ensure it has the correct impact.
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.
BACK UP YOUR DATA. Learned that one the hard way working with customer databases on a regular basis.
Maybe consider not using any deletes or drops at all. Or maybe reduce the user permissions so that only a special DB user can delete/drop things.
If you're using Oracle or another database that supports it, verify your changes before doing a COMMIT.
BEGIN TRANSACTION;
That way you can rollback after a mistake.
To answer my own question:
When writing an update statement, write it out of order.
UPDATE [table-name]WHERE [conditions]SET [columns-and-values]Choosing the rows you want to update before you say what values you want to change is much safer than doing it in the other order. It makes it impossible for update person set email = '[email protected]' to be sitting in your query window, ready to be run by a misplaced keystroke, ready to mess up every row in the table.
Edit: As others have said, write the WHERE clause for your deletes before you write DELETE.
NEVER do an update unless you are in a BEGIN TRAN t1--not in a dev database, not in production, not anywhere. NEVER run a COMMIT TRAN t1 outside a comment--always type
--COMMIT TRAN t1
and then select the statement in order to run it. (Obviously, this only applies to GUI query clients.) If you do these things, it will become second nature to do them and you won't lose hardly any time.
I actually have a "update" macro that types this. I always paste this in to set up my updates. You can make a similar one for deletes and inserts.
begin tran t1
update
set
where
rollback tran t1
--commit tran t1
Make sure your query has a WHERE parameter specified
I was once mid-way through a complex update, got distracted, and finished the query early, forgetting the "where" clause. Then I got that sinking feeling, watching a half-second query rumble on for 3.. The several hours afterwards spent cleaning up customer data was quite the lesson!
A result of which is now when I work on the live db, I structure my queries like:
UPDATE my_table WHERE condition = true;
then go back and put in the columns etc to update. Takes a bit longer to write, but massively reduces my chance of making the same mistake again!
Often before I do an UPDATE or DELETE, I write the equivalent SELECT.
A couple other things I've found helpful:
if using MySQL, enable Safe updates
If you have a DBA, ask them to do it.
I 've found these 3 things have kept me from doing any serious harm.
Do the exact same update in a Development environment first to make sure it works properly.
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.
Well, that's about all I can think of now. Take the bold passages and you see whats #1 for me. ;-)
Turn off AutoCommit in Database IDE if it supports it. I have it turned off in Oracle SQL Developer all the time.
As an example, I create SQL like this
--Update P Set
--Select ID, Name as OldName,
Name='Jones'
From Person P
Where ID = 1000
I highlight the text from the end up to the Select and run that SQL. Once I verify that it is pulling the record I want to update, I hit shift-up to hightlight the Update statement and run that.
Note that I used an alias. I never update a table name explicity. I always use an alias.
If I do this in conjunction with transactions and rollback/commits, I am really, really safe.
One quick extra I have not seen but that I do often is: backup the table your are updating. I do this by having a database to hold these backups. I can then write:
select *
into MyBackupDb..PeterTableName2008_09_28BeforeABigUpdate
This makes recovery from mistakes much faster down the road (when a full restore is not practical).
Data should always be deployed to live via scripts, which can be rehearsed as many times as it is required to get it right on dev. When there's dependent data for the script to run correctly on dev, stage it appropriately -- you can not get away with this step if you truly want to be careful.
My #1 way to be careful with a live database? Don't touch it. :)
Backups can undo damage that you inflict on the database, but you're still likely to introduce negative side effects during that span of time.
No matter how solid you think the script you're working with is, run it through a test cycle. Even if a "test cycle" means running the script against your own instance of the database, make sure you do it. It's much better to introduce defects on your local box than a production environment.
My rule (as an app developer): Don't touch it! That's what the trained DBAs are for. Heck, I don't even want permission to touch it. :)
If I'm updating a database with a script, I always make sure I put a breakpoint or two at the start of my script, just in case I hit the run/execute by accident.
1 - Always create a backup before opening a connection when you know you will need to update or insert records.
2 - When writing an update statement ALWAYS write the WHERE clause first then cursor back to the beginning of the line and write the field update portion.
3 - the where statement for #2 should be checked with a select statement.
I'll add to recommendations of doing BEGIN TRAN before your UPDATE, just don't forget to actually do the COMMIT; you can do just as much damage if you leave your uncommitted transaction open. Don't get distracted by phones, co-workers, lunch etc when in the middle of updates or you'll find everyone else is locked up until you COMMIT or ROLLBACK.
Go buy Apex SQL Log. If you realize that you really screwed up, or even if it was someone else, you can use the log to reverse the changes.
I always comment out any destructive queries (insert, update, delete, drop, alter) when writing out adhoc queries in Query Analyzer. That way, the only way to run them, is to highlight them, without selecting the commented part, and press F5.
I also think it's a good idea, as already mentioned, to write your where statement first, with a select, and ensure that you are altering the right data.
dev against a backup - make sure the changes/fixes you want to apply come from a script. fat, clumsy fingers have no place when working with live data. If you can, wait for a maintenance window to apply and roll back if you can.
If you can't wait to apply right after a snapshot,backup, Make sure eveyrone understands how much work might be invovled in rolling forward the changes between the last snapshot and the time whne you applied the "fix" should it not work out.
Create a read only user (or get the DBA to do it) and only use that user to look at the DB. Add the appropriate permissions to schema so that you can view the content of stored procedures/views/triggers/etc. but not have the ability to change them.
Different colors per environment: We've setup our PL\SQL developer (IDE for Oracle) so that when you logon to the production DB all the windows are in bright red. Some have gone as far as assigning a different color for dev and test as well.
Use the same process to QA even a simple SQL data fix as you would a code change of any kind. Ours includes being committed into CVS, Having and having executed a documented test plan, having a code review and having a change control process (where various members of management and the senior operations engineer review and sign off a change).
We do this for all normal SQL data fixes, even simple ones- the only exception being when something is required to fix a major issue with production RIGHT NOW (e.g. blocking all customers from logging in) - in which case we ensure that there are as many pairs of eyes on the job as possible (typically 3-4 people around one workstation, all of whom can veto any action).
Besides making a backup of the database before making any destructive changes, another trick I find useful sometimes is if I know the exact number of records I expect to be changed by whatever I'm doing, then add a limit clause:
delete from customers where id = 5 limit 1;
"id" might be a unique index and I know there's only row that's going to match my where clause, but the limit is additional layer of prevention against accidentally nuking the wrong data. I've gotten in the habit of typing this part first, in hopes of further prevention against accidental keystrokes. I start out with "delete limit 1", then go back and add the other stuff.
If your using SQL Server 2005 and above you can create a database snapshot that will allow you to roll back any changes made to the snapshot point in time.
I often have to insert,update or delete data on the live production site (As a data analyst that is probably 40% of my job). Most of the time it is through automated DTS or SSIS packages. However, we are also the people who have to fix problem records or update production when a major client driven change occurs (such as a re-organization of the sales force). Sometimes the issues are due to bugs in the code, but usually they are as a result of strange things the client did to their file or things the users managed to mess up to save us time fixing a problem or because they wanted to circumvent the normal process for just this one quick easy change!(Note to users -Please don't try to fix things manually that are normally done thorugh an automated process, you do not know what else the process may be doing!!!!!) So sometimes we don't have the luxury of testing a script on dev first as what is in need of fixing is not on dev.
My rules: Never insert data directly from a file to a production table. Always bring it into a work table so you can view it first. Have checks in place so that if there is bad data in the file, the process will fail before you get to the final step of inserting into production data. Clean up the data first.
If you must delete a large number of records, it can save you if you select those records first into a work table. Then do the delete. That way if things go wrong it is much easier to recover. If you have audit tables, know how to recover data from them quickly. Again if something goes wrong it is much faster to recover from the audit tables than from the tape backup.
I write a delete statement like this:
begin tran
delete a
--select (list important fields to see here)
from table1 a where field1 = 'x'
--rollback tran
--commit tran
Note several things about this. First by using the alias I can't accidentally delete the whole table by only highlighting one line and running the code. By starting the where clause on the same line as the table I am much less likely to miss highlighting it. If I had joins I would make sure each line ends in a place where the code won't work unless it goes to the next line. Again, this ensures you get an error instead of an oopsie. Always run the select first and note the number of records affected (and look at the data to make sure it looks like the right records!) Then do not commit unless the number of records is correct when you run the actual delete. Yeah, it's prettier to start the where on a separate line, it is safer to end each line of a delete so that it will not run unless the whole query is highlighted.
Updates follow simliar rules.
The danger of running unintentional Deletes (or inserts, or updates) is always on my mind.
I always add "where 1=2" after them until I'm ready to pull the trigger.
if you are using oracle 10/11g... Flashback
http://www.oracle.com/technology/deploy/availability/htdocs/Flashback_Overview.htm
It basically maintains a sliding window of undo logs that can be referenced by time or a named marker. It makes dead simple to undo days worth of changes in a couple minutes. without bringing the database down.
To let the DBAs do the work. Coming from a development background, I don't want/need/should have access to anyone's live database. To me, it is the equivalent of letting a DBA fix coding issue in the DAL, just because it has "database" in the title. :-)
I always like to have someone look over my shoulder whenever I connect to a live database.
Have a recent copy of the production database stored somewhere. This will often preclude your need to query the production db.
If you ever have to do anything to a running db. Document it, and add a fix in as a coded feature available to admins. This way you have one less excuse to point a query tool at your db.
I learned this in an interview and thought it was a great idea.
Begin Transaction
Delete from foo where FooID = 100
IF @@RowCount <> 1 Begin
Rollback Transaction
End
Whenever I open a connection to PROD, or switch to a PROD data context, the first thing I always do is add this comment before and after my active working code block:
-- PROD -- PROD -- PROD -- PROD -- PROD -- PROD --
There have been times when I noticed this while my thumb was on the Alt key and my middle finger was halfway to the 'X' key. Whew!
If you are using Microsoft SQL Server Management Studio 2008 you can specify which color to be used in the info window while executing querys (at the bottom of the Sql Query Editor)
On the Connection Promt choose Options > Use Custom Color and select RED for production.
Never design any databases with cascading deletes. They're evil. If you do have cascading deletes on FKs, you never know how many rows in other referenced tables will be deleted when you delete a row with a delete statement.
That said, you can't assume anything about what other people do. I always do this: 1. Copy database to locally installed db (use dumps). Simply tell management you refuse to work if you cannot have a copy of the full DB on you local computer. 2. Make your script work on your local db, import the dump over and over until the script works perfectly on a cleanly imported dump. Then save the script to a file on disk. 3. Run script on production server. 4. Import script into SCM.
Backups of the data before you start messing with it just like anything else.