0

I'm using Microsoft System Center Data Protection Manager 2016 to protect SQL Server 2017 on Windows Server 2016. I believe that whenever DPM does a backup (synchronization or express full) it will temporarily use some space on the SQL Server host before the data are transmitted to the DPM server.

I have found some support for this belief, specifically for transaction log backups, in https://blogs.technet.microsoft.com/dpm/2013/08/21/optimizations-in-protecting-sql-databases-with-high-churn-by-dpm/ which says (in part)

The temporary transaction log files are stored in a folder named “DPM_SQL_POTECT\” + “MachineName”+“SQL Server instance name\” + “Database name” + “_log.ldf\Backup\”. This folder is created in the same location as the log definition file location.

(note that elsewhere in the post it becomes clear that the directory name actually starts DPM_SQL_PROTECT, there's just a typo in the part I quoted).


However, I have not been able to find any similar information about temporary space usage for express full backups (which would be a SQL Server full database backup). Looking at the DPMRA.errlog files on the protected SQL Server suggests that the local VSS shadow copy space is used:

CMultiVolumeUsnIterator:AddIncludeFiles(filepath:e:\Program Files\ Microsoft SQL Server\MSSQL14.SQLNGR1\MSSQL\Data\, filespec:kslds.mdf, snapshotpath:\? \GLOBALROOT\Device \HarddiskVolumeShadowCopy9\Program Files\Microsoft SQL Server\MSSQL14.SQLNGR1\MSSQL\Data)

but I am not sure I am interpreting it correctly. In addition, on that protected server, even though many express full backups have been completed, vssadmin list shadowstorage reports

No items found that satisfy the query.

which seems to contradict the idea that VSS space is being used.


Finally, SQL Server's own logs have entries corresponding to the express full backups which identify the dump device as

device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{990A7582-1356-4B6B-8D70-8F3235748794}1'})

in which the GUID seems to change each time, but I haven't been able to find any explanation of what real storage is behind the virtual device.


Does anybody know how to determine (and perhaps control) the location and amount of space used on the protected server for these backups?

thanks!

Martin

M Herbener
  • 3
  • 3

1 Answers1

0

Per Microsoft Support, with minor edits for typos etc:

In the case of an express full backup, DPM will create a volume shadow copy of the volume hosting the SQL database files. As writes are performed to the SQL log and database files, the copy-on-write will copy the data to the snapshot file located in the system volume information folder. The size of the snapshot will depend on the duration of the backup and the amount of data churn (changes to the files) while the backup is in progress.

The space is used on the volumes hosting the database and log files. For Express Full, you can redirect the location of the VSS snapshots using VSSADMIN.EXE by deleting current shadow storage space and adding it to another volume. For example, to move from E: to S: volume:

vssadmin Delete ShadowStorage /For=E: /On=E:
vssadmin Add ShadowStorage /For=E: /On=S: /MaxSize=UNBOUNDED


Also per MS Support:

If all the SQL databases are in the same protection group, they will backup serially and each backup will create a new shadow copy, one at a time. If the SQL DBs are in separate PGs but scheduled to run at the same time, then multiple shadow copies will be active.


Finally, MS Support confirmed that the description about storage of temporary copies of transaction log file (as described in the question) is still correct for SQL Server 2017, DPM 2016, and Windows Server 2016.

M Herbener
  • 3
  • 3