Restoring to a Point in Time

SQL Server 2000 permits transaction logs to be restored to a point in time or to a marked transaction in the log. I like to think of point-in-time recovery as time-traveling through the database. Have you ever wished you could take back something you had done? No, this won't help you with that "little episode" in grade 12. But what if you drop a table and need to get it back? You can perform a point-in-time restore to just before you issued the drop-table command and when the restore is finished your table is back. Of course the down side is that all other transactions issued after the point you restored to are lost as well. This is often an acceptable tradeoff , especially if you catch your error quickly. If you want to have your cake and eat it too, see the section on the new Partial Restore option. Restoring to a point in time can be accomplished with the T-SQL STOPAT option or in the Restore Dialog Box by selecting Point in Time Restore.

Two new T-SQL options, STOPATMARK and STOPBEFOREMARK have been added to further enhance point-in-time recoverability. The BEGIN TRANSACTION command now includes a WITH MARK option. This adds a named mark to that transaction. During recovery STOPATMARK and STOPBEFOREMARK , as their names suggest, allow you recover to, or just before, the specified mark. Both options also support an AFTER clause that accepts a date and time value. As many transactions could be given the same mark " name ", the AFTER option signifies the first occurrence of the mark after the specified time. This type of recovery is important in related databases where two or more databases must be rolled back to a point where they are logically consistent.