You can use SSMS (SQL Server Management Studio) in SQL Server 2016 to restore the transaction log backups in SQL Server 2016 to a specific point in time. Although we have not tried it ourselves, we believe this process will also work with SQL Server 2012 and 2014.

You will need:

• A full SQL backup, which is typically a .bak file
• A base .bak full backup, which is the first piece of the restore. For example, if you want to restore to yesterday at 5:00pm and you take backups at 1:00am each night, you will restore to yesterday at 1:00am + 16 hourly transaction log .trn files.
• Periodic transaction log backups, which are typically .trn files

5 Steps to Restore Your SQL Server 2016 Transaction Log Backups to a Specific Point in Time

Step 1: Right-click your database and select the following items from the drop-down menus: Tasks >> Restore >> Database

Step 2: Click the “Timeline” button.

Step 3: Select “Specific date and time” and enter your desired date and time in the boxes below. You can also click in the green color bar or use the slider to set the time. The example shows restoring to the 8:00am backup.

Step 4: Click “OK.” As you can see, the nightly 1:00am backup plus 7 hourly transaction logs will get us the 8:00am data that we want.

Tip: If you want click over to the “Options” tab, you will notice the “Take tail-log backup…” and “Leave source database in the restoring state…” options are selected. This is by default and will help you get back to the current state if needed. It’s just a precaution. You can leave those options selected.

Step 5: Click “OK” to start the restore. You will see the progress indicator in the upper left. First it will count through the full backup and then each of the transaction logs before it finishes.

By Jack Derrico, Systems Engineer