你的＃1的方式要小心现场数据库？ [英] What's your #1 way to be careful with a live database?

问题描述

对于我的客户，我偶尔在他们的活数据库中工作，以解决他们为自己创建的问题，或为了修复我的产品的错误创建的坏数据。很像Unix root访问，它只是危险。我应该提前学习什么课程？

你做的第一件事是什么要小心操作实时数据？

首先，如果你的对活动数据执行更新或删除，首先使用要使用的WHERE子句编写SELECT查询。确保它的工作。确保它是正确的。

你永远不想拥有

  DELETE FROM客户
  

坐在查询分析器中等待您写WHERE子句...意外点击执行，你刚刚杀了你的客户表。糟糕。

此外，根据您的平台，了解如何对表进行快速备份。在SQL Server 2005中，

  SELECT * 
 INTO CustomerBackup200810032034 
 FROM Customer 
  

会将整个Customer表中的每一行都复制到一个名为CustomerBackup200810032034的新表中，然后您可以在完成更新后将其删除并确保一切正常。如果最糟糕的情况发生，从这个表恢复丢失的数据比从磁盘或磁带尝试和恢复昨晚的备份更容易。

最后，要小心级联删除删除的东西，你不打算删除 - 检查你的表的关系和关键约束，然后修改任何东西。

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?

解决方案

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.

这篇关于你的＃1的方式要小心现场数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！