Safely excluded SQL TEMPDB from Replications

[matt_778]

We are using Veeam 8 to replicate VMware VMs to a DR site.

The SQL DBA has suggested we exclude TEMPDB on SQL VMs as this is recreated when SQL server starts and it is responsible for a large change rate.
The suggestion is to house TEMPDB on its own Windows Volume, then either exclude that volume within advanced settings in the replication job, or create that volume (disk) as VMware independent, so Veeam skips over it.

The issue is that the Replicated SQL Server at the DR site needs to have that volume presented, so that on boot it will auto-create TEMPDB.

How do we exclude the data of TEMPDB for replications, whilst retaining the disk structure to the DR VM?

We're thinking of running a full replication so Veeam replicates the new volume one time, then changing it to independent disk so from that point onward Veeam skips it over (but hopefully the disk is then known and just hold old TEMPDB data)

Any thoughts?

[foggy]

We're thinking of running a full replication so Veeam replicates the new volume one time, then changing it to independent disk so from that point onward Veeam skips it over (but hopefully the disk is then known and just hold old TEMPDB data)

That should work, just be careful with failback to original location, not sure how it will behave in case original VM has independent disk.

[AJ83]

Did Veeam ever consider making it a feature to exclude TEMPDB files from MS SQL server backups?
It would save a considerable amount of diskspace in the VBK/VBR files, especially considering there can be a large changerate in the TempDB files.

Using exclusion of the specific disk where TempDB files reside isn`t an option, because SQL server won`t start without access to the path where the TempDB files reside.

SQL server just needs the file location to be there at startup and creates new tempDB files if they are missing, so if you would configure the Tempdb files to be in the root of a specific disk, Veeam B&R would only need to be able to recover a disk as an empty disk.

[HannesK]

Hello,
As an alternative to excluding disks, you could also exclude folders: bitlooker

I don't like that feature for its complexity at scale, but it might be the thing you are looking for.

The concept of Veeam is doing VM image level backup. Always backup everything with the advantage that you cannot forget anything. No need to think about anything. No need to somehow manage hundreds or thousands of exclusions. Complexity is worse than using a little more disk space.

Best regards,
Hannes

[AJ83]

I know you can exclude the files/folder. But as you say it is a hassle to manage.

Ideally you would make the SQL TempDB disk Independant-Persistant in VMware, so it won`t snapshot (introducing risks/ longer commit with potential hickup) Veeam will not backup an Independant-persistant disk, but that introduces the problem in which SQL server wont start because the path is missing for creating TempDB files. So i would like to see a feature in wich you can specify that Veeam will add this as an emtpy disk in a restore scenario.

[PTide]

Hi,

Would you elaborate what exactly is difficult to manage when excluding only one file in a job? Bitlooker has been designed with an intention to provide an easy way to exclude certain things from a backup, and I think that your case fits it perfectly. That is, I'd really appreciate if you could help us improve it.

Thanks!

[AJ83]

It`s not necessarily difficult to set up, but it takes some extra effort. The main thing is that we would like to use independant-Persistant disks for the TempDB files for obvious reasons.

[skrause]

We do this by doing a file level exclusion in the image processing step.

You just have to have the entire folder path to where the TempDB lives and then use a wildcard.

example: X:\MSSQL\Data\*.*

Disclaimer: we do not use Veeam's SQL processing (we do copy only) so I don't know how that affects it but the backups work and I was able to test when I first created the exclusion by doing an instant VM recovery to a different location with no networking and SQL started up fine.

It would be nice if Veeam had a way to "exclude TempDB" like it excludes the pagefile though.

[foggy]

I've merged your thread into an older discussion containing a somewhat creative workaround. Please review.

[AJ83]

Thanks foggy! The workaround matt is describing works wonders for a replication scenario, but i reckon it won`t do any good for a backup that needs to be able to run in a surebackup job.

[foggy]

Anyway, I'm also supportive of the BitLooker approach, looks safer to me.