使用SQL Server进行时间点恢复

介绍 (Introduction)

How often are you working with multiple environments? For example, if you are a database administrator who is responsible for a production environment as well as another environment, it most likely that you will be working with both environments simultaneously. What is the probability that you will execute a script on production, which actually needs to be executed on the other environment? I would say it is high. To prove this point let me present you an example.

您多久在多个环境中工作？ 例如，如果您是负责生产环境以及另一个环境的数据库管理员，则很可能同时使用这两个环境。 您将在生产环境上执行脚本的可能性是多少，而实际上该脚本需要在其他环境上执行？ 我会说这很高。 为了证明这一点，让我举一个例子。

A novice dba was working in multiple database servers using same SQL Server Management Studio (SSMS) in which both instances are registered. In this case, the dba has registered with their development server and the production server and for both servers. The dba has system administrative privileges. The dba had wanted to truncate customer table in the QA environment but guess what? … this script was executed in production. I don’t think I need to describe to you the consequences.

dba新手正在使用相同SQL Server Management Studio（SSMS）在多个数据库服务器中工作，并且两个实例都在其中注册。 在这种情况下，dba已向其开发服务器和生产服务器以及这两个服务器注册。 dba具有系统管理特权。 dba曾想在QA环境中截断客户表，但您猜怎么着？ …此脚本已在生产环境中执行。 我认为我不需要向您描述后果。

In another incident, a database administrator in a popular hospital needed to modify the column name of the most commonly used table. The previous length of the varchar column was 15 and the requirement was to expand the length the 50. However, instead of making it to 50, it was changed to 5. This action, of course, resulted in lost data.

在另一起事件中，一家流行医院的数据库管理员需要修改最常用表的列名。 varchar列的先前长度为15，并且要求将长度扩展为50。但是，不是将其变为50，而是更改为5。此操作当然会导致数据丢失。

In both cases, the result was an accident and emergency help was required. Typically, you need your scheduled backups to restore your data to the point which you execute the incorrect script. However, you do not take backups every hour as that will negatively impact the performance of the system. If you are taking backups on daily basis and if you have to revert your database to the last backup, then you are talking about more data loss in order to capture latest data loss.

在这两种情况下，结果都是事故，需要紧急帮助。 通常，您需要计划的备份才能将数据还原到执行错误脚本的位置。 但是，您不会每小时进行一次备份，因为这会对系统性能产生负面影响。 如果您要每天进行备份，并且必须将数据库还原到上一次备份，那么您正在谈论更多的数据丢失，以捕获最新的数据丢失。