Using Veeam Explorer for MS SQL Server to export split .bak file(s)

[MHarris]

Is there a way that I can create a set of striped (i.e. multiple .bak files) .bak file via Veeam Explorer for MS SQL Server?

Related link: https://helpcenter.veeam.com/docs/backu ... ml?ver=120

You may ask why I would want to do this - here is the explanation:

When initialising SQL Server Peer to Peer Replication between 2 servers you have to supply the .bak file that was used for the restore to the source server (a server that is already in replication that then allows you to add the recovered server back into replication and thus in sync).
The Microsoft method is to backup the source database, copy that .bak file to the destination (recovery) site, then restore. Once that is complete you set up replication and initialise by supplying the .bak file.

That works great, but if your database is large and therefore your .bak file is large (lets say 5Tb) and your sites are far apart and network not particularly fast, it can take days to get the .bak file from source to destination.

Veeam allows us to restore the database to the destination site fairly quickly. Then it can be used to create the .bak file. So you have the opposite scenario where you need to transfer the .bak file in the opposite direction - back to the source site - so that it can be used to initialise replication.

----
One thing I have discovered to speed up this process is that a .bak file that is striped (SQL server creating .bak can stripe to up to 64 files) can be used to initialise replication (it only actually needs the .bak file to supply a LSN (Log Sequence Number), which lets replication know where it got to via comparison with the LSN's in the Distribution database - so that it can replicate commands since the backup datetime. This means that in the Veeam scenario you could restore the database locally, restore the .bak as striped files (split files) then only transfer 1 of those 64 files across the line (meaning only 1 64th of the time it would take to transfer the full backup).

TLDR: is there any way I can make the .bak export create 64 split files rather than the 1 that is created via the GUI??

Here's a reference article explaining the creation of split .bak files (when using SQL Server) which probably explains this usage better than I can manage:

https://www.sqlshack.com/split-sql-data ... sing-ssms/

[PetrM]

Hello and Welcome to Veeam R&D Forums!

Many thanks for the detailed description of your feature request. At the moment, we don't have such an option, but we can consider this idea as a potential improvement in future releases.

Do I understand correctly that only .bak striping can solve the issue? For example, you may try Instant Recovery or our plug-in for Microsoft SQL Server, which can back up and restore a database in multiple channels. However, I'm not sure if it will be useful in your scenario.

Thanks!

[MHarris]

Thanks for your reply.
The issue with using (what I think you are suggesting with your links) is that if you restore (or mount) an instance of SQL server/a Veeam backup then run a .bak backup (which was my previous idea - restore from Veeam, take immediate split backup, use a single file from that split backup to initialise replication), the recovery fork does not match the expected fork - essentially SQL Server sees this as a new instance of the database and thus has no LSN relationship to the previous (in-use) replicated version and does not sync the changes that have occurred between backup time and where (when) the active database is now.
I haven't got to the level of checking the internals of the .bak - but I suspect it resets the LSN so it no longer has the data that SQL needs to know where (when) to sync the replication from. It works fine ongoing, but any replication between those times (timedate of the backup and timedate of now) are lost.

I was hoping you would tell me that I could control the backup specifics (allowing me to adjust the exact specification of the backup) using Powershell or something (not that I know Powershell that well, but it would be worth me learning if I could get our recovery down by a 64th of the time by using the split .bak method!)

It is kind of an SQL problem - why should I have to present an entire .bak file of TB of size - just for it to prune a single LSN value from it!??! Maybe I'll look for a way to hack the .bak so I can create my own or something.

Anyway, thanks for your response; yes please consider a way to alter the exact specifics of the .bak export. I realise my case is somewhat niche, but I also believe there may be other use cases for more flexibility in that area.

Here's a link for more on recovery forks in SQL server and some background on why I cannot use the "Instant Recovery > Backup as split files" method (doing this starts a new recovery fork, the backup must be from the active database, a restore then backup is not sufficient), just in case I did not explain it well:
https://dba.stackexchange.com/a/340840/51645