What's your #1 way to be careful with a live database?
DatabaseDatabase Problem Overview
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?
Database Solutions
Solution 1 - Database
BEGIN TRANSACTION;
That way you can rollback after a mistake.
Solution 2 - Database
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.
Solution 3 - Database
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.
Solution 4 - Database
Make your changes to a copy, and when you're satisfied, then apply the fix to live.
Solution 5 - Database
Often before I do an UPDATE or DELETE, I write the equivalent SELECT.
Solution 6 - Database
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
Solution 7 - Database
Always make sure your UPDATEs and DELETEs have the proper WHERE clause.
Solution 8 - Database
To answer my own question:
When writing an update statement, write it out of order.
- Write
UPDATE [table-name]
- Write
WHERE [conditions]
- 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
.
Solution 9 - Database
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.
Solution 10 - Database
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.
Solution 11 - Database
- 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.
Solution 12 - Database
- 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. ;-)
Solution 13 - Database
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.
Solution 14 - Database
If you're using Oracle or another database that supports it, verify your changes before doing a COMMIT.
Solution 15 - Database
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.
Solution 16 - Database
Check twice, commit once!
Solution 17 - Database
Backup or dump the database before starting.
Solution 18 - Database
To add on to what @Wayne said, write your WHERE
before the table name in a DELETE
or UPDATE
statement.
Solution 19 - Database
BACK UP YOUR DATA. Learned that one the hard way working with customer databases on a regular basis.
Solution 20 - Database
Always add a using clause.
Solution 21 - Database
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. :)
Solution 22 - Database
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.
Solution 23 - Database
Make sure you specify a where clause when deleting records.
Solution 24 - Database
always test any queries beyond select on development data first to ensure it has the correct impact.
Solution 25 - Database
- if possible, ask to pair with someone
- always count to 3 before pressing Enter (if alone, as this will infuriate your pair partner!)
Solution 26 - Database
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.
Solution 27 - Database
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.
Solution 28 - Database
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.
Solution 29 - Database
- Always back up before changing.
- Always make mods (eg. ALTER TABLE) via a script.
- Always modify data (eg. DELETE) via a stored procedure.
Solution 30 - Database
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.
Solution 31 - Database
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.
Solution 32 - Database
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
Solution 33 - Database
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:
- 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.
- 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.
- Run script on production server.
- Import script into SCM.
Solution 34 - Database
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!
Solution 35 - Database
Do the exact same update in a Development environment first to make sure it works properly.
Solution 36 - Database
Turn off AutoCommit in Database IDE if it supports it. I have it turned off in Oracle SQL Developer all the time.
Solution 37 - Database
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).
Solution 38 - Database
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.
Solution 39 - Database
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.
Solution 40 - Database
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.
Solution 41 - Database
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).
Solution 42 - Database
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.
Solution 43 - Database
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.
Solution 44 - Database
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.
Solution 45 - Database
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.
Solution 46 - Database
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.
Solution 47 - Database
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. :-)
Solution 48 - Database
If you are using SQL Server 2005+ Management Studio, you can turn Implicit Transactions ON.
Solution 49 - Database
-
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.
Solution 50 - Database
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!
Solution 51 - Database
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.
Solution 52 - Database
Backups of the data before you start messing with it just like anything else.