3

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 <database name>. No user action is required. However... and I/O was resumed on database <database name>. 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:

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: <none>
Verbose mode:    OFF
Metadata file:   <default>


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:

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?
John K. N.
  • 18,854
  • 14
  • 56
  • 117

0 Answers0