June 9, 2008 at 3:52 pm
Question,
Say I have a database with only two file groups, FG1, and FG2. I do a full filegroup back up on monday of FG1, and on Tuesday I do a full filegroup backup of FG2. According to books online, both full backups and the transaction log backups in between make up a "full backup."
When doing a restore, what order do you apply all the files?
June 11, 2008 at 3:11 am
Taken from BOL...
To restore a damaged file or files from file backups and file differential backups
Create a tail-log backup of the active transaction log.
If you cannot do this (because the log has been damaged), you must restore the entire database. For information about backing up a transaction log, see Creating Transaction Log Backups.
Important:
You must always take a tail-log backup before restoring a file backup. This is necessary because the file must be recovered to a state consistent with the rest of the database.
Restore each damaged file from the most recent file backup of that file.
Restore the most recent file differential backup, if any, for each restored file.
Restore transaction log backups in sequence, starting with the backup that covers the oldest of the restored files and ending with the tail-log backup created in step 1.
You must restore the transaction log backups created after the file backups to bring the database to a consistent state. The transaction log backups can be rolled forward quickly, because only the changes that apply to the restored files are applied.
Recover the database.
Note:
File backups can be used to restore the database to an earlier point in time. To do this, you must restore a complete set of file backups, then restore transaction log backups in sequence to reach a target point that is after the end of the most recent restored file backup. For more information about point-in-time recovery, see Restoring a Database to a Point in Time.
so it looks like FG1, FG2 and all t/logs since FG1 backup was taken
Gethyn Elliswww.gethynellis.com
June 23, 2008 at 6:58 am
So then, just confirm, you're saying SQL Server knows what FG each transaction in the transaction log belongs to. Because if it doesn't then the transactions that occured on FG2 between the two full backups would be re-applied after FG2 is restored. Because when I'm doing a restore, I only care about the transactions that occured on FG1 between monday's backup and tuesday's backup. Does that make sense?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply