Infosys Microsoft Alliance and Solutions blog

« SQL Server 2012: SSIS Deployment Models | Main | Metro / Surface: What's in the name? »

SQL Server 2012: Timeline feature

SQL Server 2012 provides improved point-in-time recovery options with the new Timeline feature available during restore operations. For point-in-time recovery both database and transaction log backups would be required, hence this feature will only be possible in Full or Bulk Logged Recovery modes.

The option for selecting the Timeline feature is available when you select Database Properties >>Tasks >> Restore>>Database. A window as shown below will be presented with the Timeline option as highlighted.

 

time1.pngAs you can see there is one full database backup and two transaction log backups created for the AdventureWorksDW2012 database. On click on the Timeline option, a window with options for restoring the database to the last database backup or for a point-in-time recovery will be presented as shown below.

 

time2.pngThe available backups are represented on a scale with different icons and colors. The Timeline interval is set to Day by default but can be set to Hour, six hours or a full week. As shown here, a full database backup has been taken somewhere after at around 11.50 AM and the transaction log backup has been taken at around 12.10 PM. If the point of failure was somewhere around 12.05 PM, we can set the correct time for the restore using the drag option available below the scale (here it is set to 12 PM). Tail-Log backups will be created appropriately to restore the database to a more recent point. Tail-log backups make sure that all the transactions after the latest transaction log backup have been accounted for during the restore process. Once the selection is done, click OK.
In the Options tab of Restore database window, the restore option can be selected as WITH REPLACE, WITH KEEP_REPLICATION or WITH RESTRICTED_USER as per requirements. Set the Recovery state as "RESTORE WITH RECOVERY". If you want to restore the database while it is still online, select the tail-log backup's option and the WITH NORECOVERY checkbox selected. Once the restore operation is completed, it provides the below success message box

time3.png

The Timeline feature is quite helpful where in it provides a visualization to correctly identify the backups for the restore process. Combined with the option of taking tail-log backups, it should make the process of restoring databases all the more easier. 

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles

Infosys on Twitter