This article is about a situation that many of us could describe the theoretical approach to solving, but then struggle to understand why SQL Server wasn’t following that theoretical approach when you tried it for real.
Earlier this week, I had a client ask about the best way to perform:
The last point might sound un-necessary because you’re restoring a differential backup, but the restore script originally being used meant SQL Server still wanted every transaction log since the full backup restored. This article explains the background to the situation, the successful restore commands, and identifies what was causing every transaction log to need to be restored.
For this article, let’s imagine we have a database of the following configuration:
And, let’s imagine it has the following backup schedule:
Then, let’s assume we want to restore fgTwo to the point in time labelled above in order to recover data from a table it stores. Performing a partial database restore which will be required also requires the primary filegroup to be restored so SQL Server will automatically restore it making the future database look like the following:
You’d expect the path to restore fgTwo to look like the path on the left of the diagram below, but for some reason the client was being forced to perform the restore steps on the right, despite their T-SQL restore commands appearing to follow the syntax in Books Online.
To show how to restore the filegroup using the path on the left, and highlight what was causing the path on the right to be required, I’ll use a series of T-SQL restore commands.
This first command restores the parts of the database we’re interested in from the full backup file, labelled “A” in our timeline, and the two most important parameters shown in green are what turns a complete database restore into a partial filegroup restore.
restore
database partial2
filegroup=’primary’, filegroup=’fgTwo’
from disk = ‘c:\parttest\partial1_FULL_A.bak’
with norecovery, partial, replace,
move ‘primary’ to ‘c:\parttest\priamry.mdf’,
move ‘primary_log’ to ‘c:\parttest\primary_log.ldf’,
move ‘fTwo’ to ‘c:\parttest\fTwo.ndf’
The next command restores from the differential backup, labelled “B” in our timeline, and means we don’t need to restore transaction logs A1 and A2. In the client’s real-world scenario they actually had 120+ transaction logs between the full and differential backups, hence the desire not to have to restore each of them.
It was this command that was the cause of them having to restore every transaction log since the full backup, the crossed out parameters in purple were what was used and causing it to happen. Telling SQL Server again which filegroups to restore was forcing it to need to restore the entire log chain since the full backup.
restore
database partial2
filegroup=’fgTwo’
from disk = ‘c:\parttest\partial1_DIFF_B.bak’
with norecovery,
move ‘primary’ to ‘c:\parttest\priamry.mdf’,
move ‘primary_log’ to ‘c:\parttest\primary_log.ldf’,
move ‘fTwo’ to ‘c:\parttest\fTwo.ndf’
Finally, with that step performed, you can then restore the subsequent transaction logs from after the differential backup with a relevant STOPAT parameter:
restore
log partial2
from disk = ‘c:\parttest\partial1_Log_B1.trn’
with norecovery, stopat=’2012-05-29 15:13:12.750′
restore
log partial2
from disk = ‘c:\parttest\partial1_Log_B2.trn’
with norecovery, stopat=’2012-05-29 15:13:12.750′
restore database partial2 with recovery
At this point, the database is recovered with just the primary and fgTwo filegroups online, you can look in sys.master_files to see the state of all of the database’s data files and which are currently online.
In summary, this article showed how to perform a partial database restore, and how you can easily have to restore more than you were expecting to due to a simple “over-clarification” in a restore command.
I have a complete demo script of a more thorough test available for download from here.
