哎呀！在SQL Server中需要撤消？尝试使用快照......

SQL Server 2005及更高版本的一个很棒的功能是快照数据库。它似乎是数据库的完整只读副本。所有数据表，视图，存储过程，用户，权限等。它只是只读。但在引擎盖下，它比这更有效。此功能使我们有机会撤消意外更新和删除，而不会因任何人不方便。它补充了经典备份和恢复操作，但不会替换它们。这是一种不同的技术。稀疏的数据库技术确切地说。创建快照时，SQL Server会创建一个稀疏的数据库，有效地是一个空的shell以便开始。这就是为什么它如此迅速创造。 It appears to be the same allocated size as the associated data file but if you look at the “Size on disk” property you will see a different picture. 128 KB in my tests. When the source database is updated, it performs a copy-on-write operation which copies the original data page to the Snapshot database before the update. This is done only when a data page changes first time. The Snapshot database is maintained internally. Once created, we can undo certain accidental operations. When that DBA drops a table by accident, or deletes thousands of rows, or applies an update without a WHERE clause, the Snapshot can help. All are “Resume generating events” but with a Snapshot database available you can play the hero. Since the data object is unchanged in the Snapshot, it can be quickly copied back via T-SQL without disconnecting the users or affecting other data. A common strategy is to drop and create a Snapshot every day at a particular time to assist with undo requirements. Or you can use a Snapshot to create a read-only copy of a Mirror database to allow reports to run against it. Of course, there is some overhead to maintain the Snapshot and this has to be assessed to be acceptable. Also, a Snapshot will not help you in cases of corruption. If the source database is corrupt then so is the Snapshot so classic Backup and Restore is still needed to save the day in those cases. But now if someone asks you if you can restore an individual table to SQL Server, the answer is… “Yes, of course!”. Cheers Brian