Sql-server – Piecemeal restore of entire database – fastest way to roll forward and restore multiple files

restoresql server

I have a database which has two filegroups (both r/w), each with two files. The backup sequence was:

  1. Full Backup
  2. Several transaction log backups (tlog set1)
  3. Full Backup of file1 of filegroup1
  4. Several transaction log backups (tlog set2)
  5. Full Backup of file2 of filegroup1
  6. Several transaction log backups (tlog set3)
  7. Full Backup of file1 of filegroup2
  8. Several transaction log backups (tlog set4)
  9. Full Backup of file2 of filegroup2
  10. Several transaction log backups (tlog set5)
  11. tail log backup

The guidance I found online was that to restore file1, I will need to restore file1 backup, then roll forward tlog sets 1-5 and the tail log.

However, lets say that I need to restore the entire DB in minimal time, can I use the following sequence :

Restore file1 filegroup1
restore tlog set2 --roll forward to file 2 backup
restore file2 filegroup1
restore tlog set3 
restore file1 filegroup2
restore tlog set4
restore file2 filegroup2
restore tlog set5
restore tail

would I have to do

Restore file1 filegroup1
Restore tlog set2 -- roll forward to current time
Restore tlog set3
Restore tlog set4
Restore tlog set5
Restore tail

Restore file2 filegroup1
Restore tlog set2 -- roll forward to current time
Restore tlog set3
Restore tlog set4
Restore tlog set5
Restore tail

...

bringing each file online before moving on the next file (or maybe even just use the full backup then applying all the log backups)? Wasn't able to find a definitive answer.

Additionally, is it necessary for me to restore the initial full backup, since the file backups cover the entire database?

The documentation examples restore just one filegroup, which is restore the full file backup then roll forward through every log bringing the filegroup up to date. The case I'm wondering about is if you are restoring multiple file backups, do you have to roll each file forward to the present, or can you restore the first file backup, roll transaction logs to the second file backup, then roll forward to the present. I want to minimize the number of times I have to apply a log backup.

Best Answer

The first sequence you wrote is correct:

Restore file1 filegroup1
restore tlog set2 --roll forward to file 2 backup
restore file2 filegroup1
restore tlog set3 
restore file1 filegroup2
restore tlog set4
restore file2 filegroup2
restore tlog set5
restore tail

You have no need to bring online the first file, then the second file, etc as all you want is to restore the whole database in minimal time.

I've just tried the first sequence as I thought it was right and it worked.

I tried then to restore from full backup + all the log files to find out if it was faster and it was. Certainly my database was small enough, just a test database, but you can try to restore your database to another database or to another server in both ways to compare the time.

Related Question