Enable COPY_ONLY Backup Via DISKSHADOW Command Prompt

### Introduction I'm experiencing weird behaviour on a virtualised (ESXi) Windows Server 2022 running SQL Server 2019 when the VMware conducts a snapshot of the server. It doesn't log the snapshot in the **msdb** database. On all our other SQL Server 2019 servers, when the ESXi host performs a VMware snapshot of the Windows Server with SQL Server 2019, then the **Volume Shadow Copy** service of Windows will be activated and this will trigger the **SQL Server VSS Writer** service. The SQL Server instance will duly note that a **is_copy_only** and **is_snapshot** backup was created for all databases of the instance. On the Windows Server 2022 with SQL Server 2019 instance running there are no entries in the **msdb** backup when VMware performs a snapshot. **Nothing**. This means there are no entries in the **ERRORLOG** that contain I/O is frozen on database . No user action is required. However... and I/O was resumed on database . No user action is required. messages. In order to pinpoint the issue I started looking at the Microsoft articles on the VSSADMIN and the DISKSHADOW command line tools. I supplemented my reading with the Microsoft article on VSS & SQL Writer which has some additional information on how the Volume Shadow Copies are initiated, etc. ### Applying My Acquired Knowledge I issued the following commands to create a valid, consistent Microsoft Volume Shadow Copy Snapshot of the quirky Windows Server 2022:

-dos
C:\Windows\system32>diskshadow
Microsoft DiskShadow version 1.0
Copyright (C) 2013 Microsoft Corporation
On computer:  SERVERNAME13,  22.07.2022 14:48:50


DISKSHADOW> set
Current context: VOLATILE
Current options: 
Verbose mode:    OFF
Metadata file:   

No Diskshadow aliases are currently defined in the environment.

DISKSHADOW> set verbose on

DISKSHADOW> writer verify "SqlServerWriter"

DISKSHADOW> set metadata c:\temp\diskshadow_c_e_f_g_h.cab

DISKSHADOW> begin backup

DISKSHADOW> add volume c: alias SYSTEM

DISKSHADOW> add volume e: alias SQLDATA

DISKSHADOW> add volume f: alias SQLLOGS

DISKSHADOW> add volume g: alias SQLTEMP

DISKSHADOW> add volume h: alias ADHOC

DISKSHADOW> create

This produced the following output, which shows us that the databases will be indeed included in the snapshot and be consistent: > Excluding writer "BITS Writer", because all of its components have been excluded. > Excluding writer "Shadow Copy Optimization Writer", because all of its components have been excluded. > Component "\BCD\BCD" from writer "ASR Writer" is excluded from backup, > because it requires volume which is not in the shadow copy set. > All components from writer "SqlServerWriter" are selected. > > * Including writer "Task Scheduler Writer": > + Adding component: \TasksStore > > * Including writer "VSS Metadata Store Writer": > + Adding component: \WriterMetadataStore > > * Including writer "Performance Counters Writer": > + Adding component: \PerformanceCounters > > * Including writer "System Writer": > + Adding component: \System Files > + Adding component: \Win32 Services Files > > * Including writer "SqlServerWriter": > + Adding component: \SERVERNAME13\WSUS\master > + Adding component: \SERVERNAME13\WSUS\model > + Adding component: \SERVERNAME13\WSUS\msdb > + Adding component: \SERVERNAME13\WSUS\TVDTools > + Adding component: \SERVERNAME13\WSUS\SUSDB > > * Including writer "COM+ REGDB Writer": > + Adding component: \COM+ REGDB > > * Including writer "ASR Writer": > + Adding component: \ASR\ASR > + Adding component: \Volumes\Volume{0ae0e5e3-28e7-4d48-a0cc-be0fe93cc972} > + Adding component: \Volumes\Volume{5ff0815f-0548-42b5-9ab4-695abd5ca4fb} > + Adding component: \Volumes\Volume{13593a85-0000-0000-0000-100000000000} > + Adding component: \Volumes\Volume{994f9685-3bd3-4332-9450-2fa867bfd88b} > + Adding component: \Volumes\Volume{08659769-e6dc-4806-9caa-66b472f4ff12} > + Adding component: \Volumes\Volume{4d6aa966-9c7c-4a45-b9fb-74b82d27c9ba} > + Adding component: \Volumes\Volume{604630a9-6906-4d65-b522-efa9bce54b33} > + Adding component: \Volumes\Volume{90a44505-8b5c-46b9-a93c-17d109bb1b48} > + Adding component: \Disks\harddisk6 > + Adding component: \Disks\harddisk3 > + Adding component: \Disks\harddisk0 > + Adding component: \Disks\harddisk4 > + Adding component: \Disks\harddisk1 > + Adding component: \Disks\harddisk5 > + Adding component: \Disks\harddisk2 > > * Including writer "Registry Writer": > + Adding component: \Registry > > * Including writer "WMI Writer": > + Adding component: \WMI > > Alias SYSTEM for shadow ID {d0dfc934-16f2-4545-8bed-f6ea4c39feb0} set as environment variable. > Alias SQLDATA for shadow ID {5c3890d3-fcca-4e97-bbc2-bcc0bf89e94a} set as environment variable. > Alias SQLLOGS for shadow ID {f2372742-d824-4049-8b11-6b7df6d4496c} set as environment variable. > Alias SQLTEMP for shadow ID {bd0cb807-87b3-4996-ae2c-8a37dc0b9ca7} set as environment variable. > Alias ADHOC for shadow ID {3db064ac-c8b1-4370-8d72-b4e5f567c62c} set as environment variable. > Alias VSS_SHADOW_SET for shadow set ID {80875036-e760-415e-a78f-79d61f699d4b} set as environment variable. > Inserted file Manifest.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file BCDocument.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM0.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM1.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM2.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM3.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM4.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM5.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM6.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM7.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM8.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM9.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file WM10.xml into .cab file diskshadow_c_e_f_g_h.cab > Inserted file Dis7D66.tmp into .cab file diskshadow_c_e_f_g_h.cab > > Querying all shadow copies with the shadow copy set ID {80875036-e760-415e-a78f-79d61f699d4b} > > * Shadow copy ID = {d0dfc934-16f2-4545-8bed-f6ea4c39feb0} %SYSTEM% > - Shadow copy set: {80875036-e760-415e-a78f-79d61f699d4b} %VSS_SHADOW_SET% > - Original count of shadow copies = 5 > - Original volume name: \\?\Volume{0ae0e5e3-28e7-4d48-a0cc-be0fe93cc972}\ [C:\] > - Creation time: 22.07.2022 14:50:23 > - Shadow copy device name: \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy1 > - Originating machine: SERVERNAME13.DOMAIN.TLD > - Service machine: SERVERNAME13.DOMAIN.TLD > - Not exposed > - Provider ID: {b5946137-7b9f-4925-af80-51abd60b20d5} > - Attributes: Auto_Release Differential > > * Shadow copy ID = {5c3890d3-fcca-4e97-bbc2-bcc0bf89e94a} %SQLDATA% > - Shadow copy set: {80875036-e760-415e-a78f-79d61f699d4b} %VSS_SHADOW_SET% > - Original count of shadow copies = 5 > - Original volume name: \\?\Volume{604630a9-6906-4d65-b522-efa9bce54b33}\ [E:\] > - Creation time: 22.07.2022 14:50:23 > - Shadow copy device name: \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy2 > - Originating machine: SERVERNAME13.DOMAIN.TLD > - Service machine: SERVERNAME13.DOMAIN.TLD > - Not exposed > - Provider ID: {b5946137-7b9f-4925-af80-51abd60b20d5} > - Attributes: Auto_Release Differential > > * Shadow copy ID = {f2372742-d824-4049-8b11-6b7df6d4496c} %SQLLOGS% > - Shadow copy set: {80875036-e760-415e-a78f-79d61f699d4b} %VSS_SHADOW_SET% > - Original count of shadow copies = 5 > - Original volume name: \\?\Volume{4d6aa966-9c7c-4a45-b9fb-74b82d27c9ba}\ [F:\] > - Creation time: 22.07.2022 14:50:23 > - Shadow copy device name: \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy3 > - Originating machine: SERVERNAME13.DOMAIN.TLD > - Service machine: SERVERNAME13.DOMAIN.TLD > - Not exposed > - Provider ID: {b5946137-7b9f-4925-af80-51abd60b20d5} > - Attributes: Auto_Release Differential > > * Shadow copy ID = {bd0cb807-87b3-4996-ae2c-8a37dc0b9ca7} %SQLTEMP% > - Shadow copy set: {80875036-e760-415e-a78f-79d61f699d4b} %VSS_SHADOW_SET% > - Original count of shadow copies = 5 > - Original volume name: \\?\Volume{08659769-e6dc-4806-9caa-66b472f4ff12}\ [G:\] > - Creation time: 22.07.2022 14:50:23 > - Shadow copy device name: \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy4 > - Originating machine: SERVERNAME13.DOMAIN.TLD > - Service machine: SERVERNAME13.DOMAIN.TLD > - Not exposed > - Provider ID: {b5946137-7b9f-4925-af80-51abd60b20d5} > - Attributes: Auto_Release Differential > > * Shadow copy ID = {3db064ac-c8b1-4370-8d72-b4e5f567c62c} %ADHOC% > - Shadow copy set: {80875036-e760-415e-a78f-79d61f699d4b} %VSS_SHADOW_SET% > - Original count of shadow copies = 5 > - Original volume name: \\?\Volume{994f9685-3bd3-4332-9450-2fa867bfd88b}\ [H:\] > - Creation time: 22.07.2022 14:50:23 > - Shadow copy device name: \\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy5 > - Originating machine: SERVERNAME13.DOMAIN.TLD > - Service machine: SERVERNAME13.DOMAIN.TLD > - Not exposed > - Provider ID: {b5946137-7b9f-4925-af80-51abd60b20d5} > - Attributes: Auto_Release Differential > > Number of shadow copies listed: 5 I then ended the Volume Shadow Copy with:

-dos
DISKSHADOW> end backup

DISKSHADOW> exit

### Confirming Snapshot Backup Looking at the information in the **msdb** database, I was able to confirm that the databases had indeed been backed up: > +---------------+-------------------------+------+-------------+------------------+--------------+-------------+ > | database_name | backup_start_date | type | backup_type | device_type_desc | is_copy_only | is_snapshot | > +---------------+-------------------------+------+-------------+------------------+--------------+-------------+ > | model | 2022-07-22 14:50:21.000 | D | Full | Virtual Device | 0 | 1 | > | msdb | 2022-07-22 14:50:21.000 | D | Full | Virtual Device | 0 | 1 | > | SUSDB | 2022-07-22 14:50:21.000 | D | Full | Virtual Device | 0 | 1 | > | master | 2022-07-22 14:50:21.000 | D | Full | Virtual Device | 0 | 1 | > +---------------+-------------------------+------+-------------+------------------+--------------+-------------+ ### Problem However, the database backups created with the DISKSHADOW CLU are not marked as **is_copy_only**. This is bad because a snapshot backup shouldn't break the backup chain. The VMware snaphsot is capable of creating backups with **is_copy_only** and **is_snapshot**. Reading the SQL Server Backup Applications - Volume Shadow Copy Service (VSS) and SQL Writer article I saw that there is the possibility of setting the backups to **COPY_ONLY**: (**emphasis** mine) > During the backup discovery phase, the SQL writer will indicate its capability to do a copy-only backup by setting the supported backup schema option VSS_BS_COPY using the IVssCreateWriterMetadata::SetBackupSchema call. **The requestor (_in my case the diskshadow CLU_)** can set the backup type as a copy-only backup by setting the **VSS_BACKUP_TYPE** option as **VSS_BT_COPY** with the call IVssBackupComponents::SetBackupState. ## Questions 1. Can I set the option VSS_BACKUP_TYPE to VSS_BT_COPY in the DISKSHADOW command-line utility? 2. Is there any other way to configure the **SQL Server VSS Writer** to create **COPY_ONLY** backups when manually creating a Volume Shadow Copy as I am doing?