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?

+23  A: 

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

Bob King
most of the time, copy db is different then live and not all changes behace same as copy and live.
If the copy database is different from the live database, wouldn't that mean it's not actually a copy database? The entire purpose of a test/qa/copy database is to test changes before they are applied to a live/production database.
+13  A: 

Always make sure your UPDATEs and DELETEs have the proper WHERE clause.

Yes I've gotten burned on this before.
Ian Jacobs
Me too. Ever since then I've wished that SQL had been designed so that the where clause came first.
Greg Hewgill
Gotta' love that sinking feeling when running a quick UPDATE and it says "1279209394 Record(s) affected." Uh-oh. ;)
Kevin Fairchild
+1  A: 

Make sure you specify a where clause when deleting records.

Bill the Lizard
+1  A: 

always test any queries beyond select on development data first to ensure it has the correct impact.

Carlton Jenke
+1  A: 

Backup or dump the database before starting.

Lou Franco
+2  A: 

To add on to what @Wayne said, write your WHERE before the table name in a DELETE or UPDATE statement.

+45  A: 

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.

+1  A: 

BACK UP YOUR DATA. Learned that one the hard way working with customer databases on a regular basis.

+3  A: 

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.

+1  A: 

Always add a using clause.

+3  A: 

If you're using Oracle or another database that supports it, verify your changes before doing a COMMIT.

You must be careful cause the records may be locked while your transaction is pending.
Greg Ogle
I usually use SQL developer for oracle and its never committed automatically even after executing. So we have a preview and then commit. Cool feature!!
+84  A: 

That way you can rollback after a mistake.

Paul Tomblin
Yep, pretty much the only way to prevent face-palm madness.
Abyss Knight
Most modern RDBMS'es do this automatically. Also, this won't protect you from DDL mistakes (i.e. CREATE / ALTER / DROP / TRUNCATE).
Graeme Perrow
Though needs to keep in mind that not every action can be rollback with begin/rollback trans.
and in a non-transactional database, this won't work :)
@Graeme, you shouldn't be doing DDL on production databases. You should write a script, run it on your test database, and after your test database passes QA, then you run it on the production server.
Paul Tomblin
@Paul: absolutely. But it could be argued that you should be doing the same with any kind of modifications to your production database, whether DDL or DML, in which case this whole question is meaningless.
Graeme Perrow
Really surprised that this get 40 votes (so far). It's really common that you find a problem one hour later when the client calls asking you where are those records that he really need now.
Eduardo Molteni
Eduardo, it gotten 45 votes so far because -- most of the time -- your cold sweating starts before your finger has finished moving all the way down on the keyboard -- but just too late to stop the finger.
Euro Micelli
@Graeme Perrow: On PostgreSQL, DDL (except for TRUNCATE) is transactional, so you can use a transaction to protect from mistakes on it too.
Don't most tools do this already...
@TM - depends on your definition of "tool". None of the command line tools I use (psql, sqlplus, mysql) do it.
Paul Tomblin
Also useful in that you can run a bunch of selects within the same transaction to verify the results before committing - if they are unexpected, no harm done - just roll back.
Dave Cluderay
+11  A: 

To answer my own question:

When writing an update statement, write it out of order.

  1. Write UPDATE [table-name]
  2. Write WHERE [conditions]
  3. Go back and write 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.

Kevin Conner
+17  A: 

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


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
rollback tran t1
--commit tran t1
Patrick Szalapski
Yeah, this is precisely what I do. Too many people are saying "don't forget the where clause", but what if it's wrong? Never, ever update a live database without this begin/rollback/--commit pattern.
Eric Z Beard
An additional improvement is to first do a "select * from" with the where clause to make sure it's right, then run the update with the same where clause.
Eric Z Beard
Eric is right, though I leave this out of my macro to avoid scope creep. I have another macro that types out "select * from " for general use.
Patrick Szalapski
There's no good reason not to do it this way. When I had to write update scripts at a previous job, I did it this way, along with a SELECT *before* the update, and a SELECT *after*, so I could see the results. After running it several times and seeing that the results were correct, I changed the ROLLBACK to COMMIT.

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!

+18  A: 

Often before I do an UPDATE or DELETE, I write the equivalent SELECT.

Patrick McElhaney
As a quick and simple check I like this method too. Depending on the number of results it may not work but at least it's a start for UPDATES and DELETES.
+5  A: 
  1. Check, recheck, and check again any statment that is doing updates. Even if you think you're just doing a simple, single column update, sooner or later you will not have enough coffee and forget a 'where' clause, nuking a whole table.

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.

+72  A: 

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


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,

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.

Dylan Beattie
don't you mean DELETE FROM Customersjust being technical :-)
Or better yet don't use cascading anything.
le dorfier
+4  A: 
  • Nobody wants backup but everyone cries for recovery
  • Create your DB with foreign key references, because you should:
  • make it as hard as possible for yourself to update/delete data and destroying the structural integrity / something else with that
  • If possible, run on a system where you have to commit the changes before you permanently store them (i.e. deactivate autocommit while repairing the db)
  • Try to identify your problem's classes so that you get an understanding how to fix without trouble
  • Get a routine in playing backups into a database, always have a second database on a test server at hand so you can just work on that
  • Because remember: If something fails totally, you need to be up and running again as fast as any possible

Well, that's about all I can think of now. Take the bold passages and you see whats #1 for me. ;-)

I'd just like to add to the mention of autocommit, because it's an important safety mechanism. If you're connecting straight to the database, you can usually turn off autocommit in the db connection parameters. Otherwise (db front-end product), you may need to look for a an application setting.
Mike Monette

Turn off AutoCommit in Database IDE if it supports it. I have it turned off in Oracle SQL Developer all the time.

Ted Elliott
+10  A: 

As an example, I create SQL like this

--Update P Set
--Select ID, Name as OldName, 
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.

I use a select check also -- I've caught several where clause errors this way. It's a good sanity check, especially when the statements are complex.
This method was honed over a short period after watching my supervisor delete an important table in production in the middle of the day.
I switch the select and update and remove the comments on the select. Then when I'm ready I highlight the area and run. Works for delete's too.

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).

+3  A: 

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.

+1  A: 
  1. if possible, ask to pair with someone
  2. always count to 3 before pressing Enter (if alone, as this will infuriate your pair partner!)
Michael Easter
+11  A: 

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.

Gabriel Isenberg
+2  A: 

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. :)

+3  A: 

Check twice, commit once!

+1  A: 

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.

Brian Vander Plaats

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.

Declan Shanaghy

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.

Darrel Miller
+1  A: 

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.

+1  A: 
  1. Always back up before changing.
  2. Always make mods (eg. ALTER TABLE) via a script.
  3. Always modify data (eg. DELETE) via a stored procedure.

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.

+1  A: 

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.

Richard Nienaber
+1  A: 

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.

Doron Yaacoby

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.


When updating/deleting only one record mysql lets you put "LIMIT 1" at the end so only one record gets damaged even when WHEN clause is wrong.


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.

+1  A: 

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


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. :-)


If you are using SQL Server 2005+ Management Studio, you can turn Implicit Transactions ON.

  1. I always like to have someone look over my shoulder whenever I connect to a live database.

  2. Have a recent copy of the production database stored somewhere. This will often preclude your need to query the production db.

  3. 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.

Nathan Feger

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

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.

Cesar Reyes
+1  A: 

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.

fuzzy lollipop