What is your # 1 way to be careful with a live database?

Question

For my client, I sometimes work in my real database to fix the problem that they created for themselves, or to fix the bad data that created my product errors. Like accessing a Unix root system, this is simply dangerous. What lessons should I learn in advance?

What do you do to be careful when working with live data?

Answer 1

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

First, if you are making updates or deleting data in real time, first write a SELECT query with a WHERE clause that you will use. Make sure it works. Make sure this is correct. Then add the UPDATE / DELETE statement to the well-known WHERE clause.

You will never want to have

DELETE FROM Customers 

sits in your query analyzer, waiting for you to write a WHERE clause ... accidentally click execute, and you just killed the Customer table. Unfortunately.

Also, depending on your platform, find out how to quickly back up a table. In SQL Server 2005

 SELECT * INTO CustomerBackup200810032034 FROM Customer 

will copy each row from the entire Customer table to the new CustomerBackup200810032034 table, which you can delete as soon as you have made your updates and make sure that everything is in order. If the worst happens, it is much easier to recover the missing data from this table than to try to restore the backup of last night from a disk or tape.

Finally, be careful when deleting cascades, getting rid of unnecessary things for you - check the relationships and limitations of keywords before changing.

Answer 2

 BEGIN TRANSACTION; 

Thus, you can roll back after an error.

Answer 3

Make a backup first: it must be law with number 1 sysadmining anyway

EDIT : By including what others have said, make sure your UPDATES have matching WHERE clauses.

Ideally, a change to the real database should never happen (other than INSERT and basic maintenance). Changing the living structure of a database is especially fraught with potential bad karma.

Answer 4

Make your changes to the copy, and when you are satisfied, apply the correction to life.

Answer 5

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

Answer 6

NEVER upgrade unless you are in BEGIN TRAN t1 - not in the dev database, not in production, and not anywhere else. NEVER run COMMIT TRAN t1 outside the comment - always enter

 --COMMIT TRAN t1 

and then select the operator to run it. (Obviously, this applies only to GUI request clients.) If you do this, it will become second nature to them, and you are unlikely to lose time.

In fact, I have an “update” macro that picks it up. I always insert this to customize my updates. You can create a similar file to delete and paste.

 begin tran t1 update set where rollback tran t1 --commit tran t1 

Answer 7

Always make sure your UPDATE and DELETE have the correct WHERE clause.

Answer 8

To answer my own question:

When writing update instructions, write it out of order.

• Write UPDATE [table-name]
• Write WHERE [conditions]
• Go back and write SET [columns-and-values]

Choosing the lines you want to update before saying which values ​​you want to change is much safer than doing it in a different order. This makes it impossible to place update person set email = '[e-mail]' in the request window, ready to be launched by inappropriate keystroke, ready to ruin every row in the table.

Edit: as others have said, write a WHERE for your deletions before writing DELETE .

Answer 9

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 select the text from the end to selecting and running SQL. As soon as I check that it pulls out the record I want to update, I press shift-up so that the Update statement lights up and runs it.

Please note that I used an alias. I never update the explanation of the table name. I always use an alias.

If I do this in conjunction with transactions and rollback / commits, I am really, really safe.

Answer 10

My # 1 way to be careful with a live database? Do not touch him.:)

Backups can undo the damage you do to the database, but you can still introduce negative side effects during this period of time.

No matter how confident you are with the script, run it through a test loop. Even if “test loop” means running a script against your own database instance, make sure you do it. It is much better to introduce defects into a local box than into a production environment.

Answer 11

• Check, double-check, and re-check the status that performs the updates. Even if you think you are just doing a simple update with one column, sooner or later you won’t have enough coffee and forget the “where” clause, exposing the whole table.

A few other things I found useful:

• if you use MySQL, enable Safe Updates

• If you have a database administrator, ask them to do this.

I found that these 3 things did not give me serious damage.

Answer 12

• Nobody wants to make a backup, but everyone shouts about restoring
• Create your database using links to foreign keys, because you need:
• do your best to update / delete data and destroy structural integrity / something else with that
• If possible, start the system in which you must commit the changes before you permanently store them (that is, disable autorun when restoring db).
• Try to identify your problem classes so that you understand how to fix without problems.
• Get the routine when playing backups to the database, always have a second database on the test server at hand so you can just work on it
• Because remember: If something fails completely, you need to start again and again as quickly as possible

Well, that’s about all I can think of now. Take bold passages and you see that No. 1 is for me .; -)

Answer 13

It may be considered that you are not using any deletions or drops at all. Or, possibly, reduce user rights so that only a special database user can delete / delete things.

Answer 14

If you are using Oracle or another database that supports it, check your changes before running COMMIT.

Answer 15

Data must always be deployed to work through scripts that can be rehearsed as many times as needed to get them directly to dev. When the dependent data for the script will work correctly on dev, execute it accordingly - you cannot avoid this step if you really want to be careful.

Answer 16

Check twice, lock once!

Answer 17

Back up the database before starting.

Answer 18

To add to what @ Wayne , write WHERE before the table name in the DELETE or UPDATE .

Answer 19

BACK YOUR DATA. I learned that it is difficult to work with customer databases on a regular basis.

Answer 20

Always add a use clause.

Answer 21

My rule (as an application developer): do not touch it! This is what database administrators are trained. Damn it, I don’t even want permission to touch it. :)

Answer 22

Different colors for the environment: we created our PL \ SQL developer (IDE for Oracle) so that when you enter the production database all the windows are bright red. Some have come up with a different color for dev and test.

Answer 23

Make sure you specify the where clause when deleting entries.

Answer 24

always check any queries outside of the design data selection to make sure they have the right impact.

Answer 25

• if possible, ask someone to contact
• it always counts to 3 before pressing Enter (if only this, as this will lead to disconnection with your partner!)

Answer 26

If I update the database using a script, I always make sure to put a breakpoint or two at the beginning of my script, just in case I accidentally hit run / execute.

Answer 27

I will add to the recommendations to do BEGIN TRAN before your UPDATE, just remember to actually execute COMMIT; you can do as much damage if you leave an uncommitted transaction open. Do not get distracted by phones, employees, lunch, etc., When in the middle of updates, or you find that everyone else is blocked until you COMMIT or ROLLBACK.

Answer 28

I always comment on any destructive queries (insert, update, delete, delete, modify) when writing adhoc queries in Query Analyzer. Thus, the only way to start them is to select them without selecting the commented part and press F5.

I also think that it is a good idea, as already mentioned, to write the instruction first, with a choice, and make sure that you are changing the correct data.

Answer 29

• Always keep a backup before changing.
• Always create a mod (e.g. ALTER TABLE) with a script.
• Always modify data (e.g. DELETE) using a stored procedure.

Answer 30

Create a read-only user (or get a DBA for this) and use it only to view the database. Add the appropriate permissions for the schema so that you can view the contents of stored procedures / views / triggers / etc. but do not have the ability to change them.