04-13-2015 07:59 AM
Hi All,
I am currently backing up an SQL server (with a single database split into several filegroups) using a weekly full, daily differentials and daily transaction log backups.
If I needed to, what would be the procedure to restore the database in question to a point in time?
I had at first thought I would need to restore the full, then the differential and then the transaction logs (selecting "To point in time"). However, whilst looking in the Restore GUI I see that when I select the desired transaction log image, the fulls for each filegroup are also selected (but not the differential images).
Many Thanks
Solved! Go to Solution.
04-13-2015 11:00 AM
Same process you've discovered applies to diffs. Select the diff, it would then select the full too. Restore that but make sure to set it so its NOT RECOVERED. You can then apply transaction logs afterwards.
04-13-2015 11:00 AM
Same process you've discovered applies to diffs. Select the diff, it would then select the full too. Restore that but make sure to set it so its NOT RECOVERED. You can then apply transaction logs afterwards.
04-14-2015 03:18 AM
Hi Riaan,
OK thanks, but how would I apply the transaction logs afterwards?
The reason I ask is that when I select the most recent transaction log for restore it also automatically selects a full image for each of the filegroups on that server, surely this process would overwrite the database again from scratch thus not applying the diff, as I can only select one or the other (the diff OR the transaction logs)?
04-14-2015 08:12 AM
from the guide
Restoring a SQL transaction log image without staging a full recovery
This topic describes how to restore a transaction log image without staging a full recovery.
To restore a transaction log without staging a full recovery
1 Browse for the backup images you want to restore.
2 In the Restore Microsoft SQL Server Objects dialog box, expand the database instance.
3 Select the transaction log image that you want to restore.
If a set of images exists that include the transaction log image and that are sufficient for staging a full database recovery, Stage full recovery is enabled. The properties of the transaction log include a Recovery Set tab.
4 Select Restore only the transaction log that you selected.
5 Click Restore.
To view the progress of the restore, select File > View status.