Let's say we did a full database backup of the PRIMARY filegroup at 10:20 am. It finished at 10:45 am. We also took log backups every 5 minutes, hence 10:15, 10:20, 10:25, 10:30, 10:35, 10:40, 10:45,... Then we do a full backup for secondary filegroup at 10:50am it finishes at 11:00am.

Now I try to recover a database using full filegroup and log backups. I take the full backup of PRIMARY done at 10:15, restore with norecovery. What should I restore next? The logs until 10:45 with norecovery? then the second full backup after that all the logbackups after 11:50am till the tail log with recovery? Is it the right way or should I do something else?



You should first restore your PRIMARY with norecovery, then your SECONDARY with norecovery, and then all the log backups from 10:15 till tail of the log, the last one with recovery.

| improve this answer | |

That is correct, Restore primary and secondary with no-recovery and then backup till tail log with recovery. I have seen most of the cases where people use restore recovery option primary then, database goes into recovery state. So, it's must to know that, if you have multiple backup and always use restore with no-recovery with 1,2..n backups and with last one, always use restore with recovery.

You should check this reference.

| improve this answer | |
  • Piecemeal restore is the process of restoring a database which originally contains multiple filegroups, by restoring individual filegroups, whilst SQL Server retains a consistent state. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. Piecemeal restore maintains checks to ensure that the database will be consistent in the end. Piecemeal Restore – John aka hot2use Dec 10 '18 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.