Initialize replication from multiple split backup files

Question

We are creating a new Node within our existing P2P replication setup. To be able to create the backup and copy the files I am Backing up to 9 files - each will be about 72Gb:

e.g.

BACKUP DATABASE [Database_Name] TO
DISK = 'C:\MSSQL_Backup\Database_Name_file1.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file2.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file3.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file4.bak'

etc. (up to 9)

When I restore at the New Server I will then add the new Node to the P2P Topology.

All good so far?

The question is: When I Initialize the subscription at the new Node - how can I specify the Backup that was used - given that it is made up of 9 files?

I have searched the Net and though I have found the question asked a couple of times - it seems that no one can answer.

I would test this, but I am really pushed for time.

Any help appreciated.

Answer 1

As no one got back to me (fair enough - maybe you've got jobs too!) I have tested this.

We didn't use this method in the end for the production task because the powers that be decided it was ok/best to use 7-Zip to split the single .bak file.

However, the answer is - you only need to specify the a file of the set and it works fine (I did this on my test setup):

Restore as normal > when specifying the Backup to use as the initialization point, give it the first file name.

I assume this is because the LSN is listed in this file (whether it contains the right bit of the Log data or not is irrelevant - as it's only looking for a point to reference through the Log Reader agent).

In fact to double check this, I have just gone back to my test setup and commenced replication (P2P in this case as in SQL 2008R2 it allows you to specify the Backup file through the SSMS GUI Wizard - call me lazy!) by using the 3rd file in my 4 file backup set, which worked fine. I believe this proves that the LSN is in the header of each file in the Backup set and also why using a Log backup or Differential backup also works.

Just to make sure that anyone in the future that needs the same answer to the multi-file backup initialization question can find this - I need to include the initial question as I would have asked it so they find this when they search:

Q: Can I use a split Backup set to Initialize replication?

A: Yes you can, simply specify any of the bak files used in the Restore to initialize.