I've a SQl server 2019 on a Windows Server 2019 standard core, I manage two typer of backup, one with mantainance plans and one with Veeam Backup. The mantainance plans it's ok, the backup with Veeam fails every day with error on SQL leaving SQLServerWriter in Failed state with last error Non-retryable error. I've open a ticket on Veeam support and we have eplored vary log and the problem wasn't related to Veeam because even creating backup with vss command there are error. According to the logs, the backup job fails with the error: [11.09.2023 00:07:12] Error Failed to create snapshot: Backup job failed. [11.09.2023 00:07:12] Error Cannot create a shadow copy of the volumes containing writer's data. [11.09.2023 00:07:12] Error A VSS critical writer has failed. Writer name: [SqlServerWriter]. In the VSS writers, the error could be observed: Writer name: 'SqlServerWriter' Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a} Writer Instance Id: {1fb6de2a-c593-45c2-b592-e90ff6aad393} State: Failed Last error: Non-retryable error The following errors can be observed in the Windows Application events: Log Name: Application Source: SQLWRITER Date: 11.09.2023 0:07:05 Event ID: 24583 Task Category: None Level: Error Keywords: Classic User: N/A Computer: SQL03Core Description: Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 11.0 Error message: BACKUP DATABASE is terminating abnormally. SQLSTATE: 42000, Native Error: 3224 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 11.0 Error message: Cannot create worker thread. Log Name: Application Source: SQLWRITER Date: 11.09.2023 0:07:05 Event ID: 24583 Task Category: None Level: Error Keywords: Classic User: N/A Computer: SQL03Core Description: Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 11.0 Error message: BACKUP DATABASE is terminating abnormally. SQLSTATE: 42000, Native Error: 3202 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 11.0 Error message: Write on "{7F86B757-DC6E-4B76-B35D-F382797EB665}414" failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.) The server has 536 database and 8 core, so the Veeam support suppose that the problem was related to the an insufficent number of worker thread, so according to https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-ver16 , we have change the number of worker thread to 1000 but errors continue to raise, so Veeam support suggest to have help in order to optimize machine and thread according to the load. Can anyone suggest a way to proced? Sorry for my bad english and thanks in advance... Stefano

Our "hardware" dept has started some ASR replication on one of the servers where we run SQL Server. It seems that the method they use for this uses VSS, and I can see a lot of messages in the SQL Log about databases being backed up, and also the I/O frozen/resumed. It seems to do this about every hour. Will these backups taken by VSS break the log chain, or are they copy backups? Is there any way to see if they are copy backups? Is it possible to instruct the VSS to use copy backups? Any feedback appreciated.

I take weekly Full backups (all my databases are in Full recovery model) on Sundays and then schedule the Diffs to run nightly (except on Sundays). Looking through my logs I see this: > Cannot perform a differential backup for database "DBNAME", because a current database backup does not exist I do have the VSS Writer running but have no 3rd party tool that I know of running that should "invalidate" the Sunday full backups. If I manually run a Full backup then run a Diff it works just fine. These machines are VM's in Azure running SQL Server 2017. The strange thing is on my test machine it works with the same setting (VSS Writer enabled). I'd rather not disable the VSS Writer if do not have to. Any thoughts?

### 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?

How to solve Log shipping issues when Shadow copy volume-level backups are in place? I tried setting up log shipping but it failed each time because the backup chain of the virtual log, the block number and the LSn log backups were different after the VSS ran it's snapshot. As a result the log shipping failed. I tried restoring the log to resume log shipping operation but I get this error “The file is too recent to apply to the secondary database”

I use the following query: RESTORE FILELISTONLY FROM disk = N'E:\Tmp\metadata2012.metadata' with CONTINUE_AFTER_ERROR --or even like that --RESTORE FILELISTONLY FROM disk = N'E:\Tmp\metadata2012.metadata' GO output: LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint SnapshotUrl -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------------------- --------------------------------------- ------------------------------------ --------------------------------------- --------------------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- dbRestoreToTime C:\dbRestoreToTime2012.mdf D PRIMARY 3145728 35184372080640 1 0 0 207A4430-91A0-4EFF-B9AB-44370D91D8E5 0 0 2424832 512 1 NULL 32000000041100037 F0DC3522-3C58-4AD0-A0BF-40CA3EE96793 0 1 NULL NULL dbRestoreToTime_log C:\dbRestoreToTime_log2012.ldf L NULL 1048576 2199023255552 2 0 0 E83E06CD-2151-40EB-8660-DB3C3D58C334 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL NULL dbRestoreToTime-2_log C:\dbRestoreToTime_log2012-2.ldf L NULL 1048576 2199023255552 3 31000000007400001 0 FE7D0324-6D8D-40BE-927A-6B9916505FD7 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL NULL MyTestFileStore C:\FILESTORE2012 S TestFileStore 0 0 65537 31000000009800001 0 E9467C76-B305-4355-82F9-980DADDA7984 0 0 0 512 2 NULL 32000000041100037 F0DC3522-3C58-4AD0-A0BF-40CA3EE96793 0 1 NULL NULL (4 rows affected) Completion time: 2021-10-25T18:12:18.4750926+02:00 It works as expected, but unfortunately it generates the following error in the windows event viewer: BackupIoRequest::ReportIoError: read failure on backup device 'E:\Tmp\metadata2012.metadata'. Operating system error 87(The parameter is incorrect.). I can restore my database to a specific point in time and it works perfectly (there're several more steps required for that like attaching DB in norecovery state, creating VDI device and applying logs but it's out of scope - DBCC check gives no error on restored DB). The only one thing is the error in the windows event viewer. Do you have any idea why it's happening ? SQL VSS makes metadata2012.metadata and it's part of the following backupcomponents.xml: (It's the metadata which I use for my RESTORE FILELISTONLY query)

During vss based snapshot backup, databases IO is frozen one by one. And subsequently resumed. Questions: 1. When there is a query running, and vss based snapshot backup is executed, will the freeze get delayed (that is - wait for running query to complete)? 2. Assuming the freeze and resume interval is 60 seconds. What happens to user queries fired during this 60 seconds interval. Are they queued? Or rejected?

I have database backups configured via SAN disk tool. In the backupset table I can see the VSS writer service is taking full backups of type snapshot. There are 85 databases on the server. I know about the concept of database snapshot and that initially it is of 0 size. And as the source db is updated, it pushes the changed to the snapshot thus increasing the snapshot size. I want to ask whether: 1. The database snapshot and VSS **database snapshot backup** (via VSS writer service) is the same concept? 2. For both the database snapshot and the vss database snapshot backup- suppose there are 85 items (snapshots/snapshot backups) then this will cause overall poor IO on the sql server since it has to regularly maintain (update as the source db gets changes) snapshot of 85 databases?

Why do VSS snapshots cause Availability Groups to fail over? Back in 2018, Ken Kellman and Erik Darling wrote that by freezing I/O on databases, VSS snapshots can cause Availability Groups to fail over. https://www.brentozar.com/archive/2018/01/perils-vss-snaps/ According to Erik, "the redo threads go buck wild on the other end." OK. I've got buck wild redo threads. What does that mean? I'm seeing one of my Availability Groups fail over on nearly a daily basis. This usually happens right around the time our Avamar server backups run. Is there anything that can, or should, be done to tame these buck wild redo threads of mine, and prevent this daily failover?

"Windows Server Backup cannot guarantee Volume Shadow Copy (VSS) snapshots are retained. In certain situations, after restoring to a new volume, the VSS snapshots may no longer be recognized and appear inaccessible. To guarantee protection of a VSS snapshot, mount the VSS snapshot, and run a backup job on that snapshot." It's written in the doc " https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/jj614621(v=ws.11) what's this pharagraph suggesting can someone please explain it to me? I tried to find online but found very little information. My questions : 1) why we have to protect vss snapshot, does not window server backup feature takes care of it ? If no then how can I do it?

I want to freeze the I/O of a SQL Server instance like the shadow copy does. The result should look like this: 2020-01-19 23:11:40.90 spid73 I/O is frozen on database prod. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. 2020-01-19 23:11:57.89 spid73 I/O was resumed on database prod. No user action is required. But I want to decide when the I/O is resumed. I tried it myself with a backup, but it finished too fast. I want to debug my application during this I/O freeze. How can I achieve this behavio(u)r? **Edit - 2020-01-27 10:53** The purpose is that the SQL Server doesn't accept new connections during this time: Exception: Class name: EMSError Message: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Unit name: OLEDBAccess

Our colocation provider uses VSS to backup our dedicated server. The backups show up in the SQL logs as SQL backups even though they are not and that breaks the Full-Diff chain. The issue is well documented on StackExchange Database Administrators, but they all stop there. Is it possible to do TSQL differential or incremental backups on a system that also uses VSS? If not, is there a better approach for off-site backups assuming that we don't want to lose the onsite full-system VSS backups? SQL Server 2012 SP3 if that matters.

Our company is considering a new backup product that is an all in one sort of thing (file system, AD, SQL, etc). The product relies on VSS snapshots to create the full backups for the SQL databases. This causes an I/O freeze on the databases that usually just lasts a second or two, but can take up to 60 seconds (per Microsoft, I believe). My question is how bad are I/O freezes for databases? Also, is there any way to determine how long the I/O freeze will be? I would think it would depend on a number of factors including: the size of the database, activity on it at the time, CPU usage, disk I/O, etc. I have only been a DBA for less than a year and have not seen this myself, but my coworkers informed me that we have customers who do bulk data loads and in the past even a 1 second I/O freeze on the database will cause their jobs to fail. We have voiced our concerns to management, but it is likely they will purchase the product. Thanks for your help!

This question may read like a duplicate, but is situation-based, and is posted from confusion applying the knowledge from other answers. I've read dozens of articles (among 1 , 2 , 3 , 4 ), but am finding conflicting opinions (based on my understanding, which is now suffering from information overload, or perhaps not including enough information in my other questions). I am creating this question therefore to get a definitive answer based on my situation. Given the following backup scenario, I need to know **whether the third-party backup software would prevent me from performing a full recovery to the point of the latest backup (18:00)?** Time | Action | Device ------|----------------------------------------------|---------------------------- 12:00 | Full backup (non copy_only) | D:\MyBackupDevice 13:00 | Tran log backup (non copy_only) | D:\MyBackupDevice 14:00 | Tran log backup (non copy_only) | D:\MyBackupDevice 15:00 | Tran log backup (non copy_only) | D:\MyBackupDevice 16:00 | Full backup (non copy_only) VSS snapshot | Third-party off-site device 17:00 | Tran log backup (non copy_only) | D:\MyBackupDevice 18:00 | Tran log backup (non copy_only) | D:\MyBackupDevice 19:00 | Disaster strikes | My restore goal is to restore to the point of the 18:00 backup (I know there is a tail-log backup that can be added in as well to get the remaining transactions up until the disaster, but let's keep things simple for now). Based on this answer , I believe that the third-party backup causes a conflict with my own transaction log restoration (as per this answer ), which prevents me from performing my restore to that point. My understanding is that the transaction log backup contains data since the last full non copy_only backup. Is this correct? Does the third-party backup prevent my own restore routine from working because it is a non copy_only backup?

I have a SQL database server, and this uses R1Soft backup to take a server backup every 24 hours at 02:00. This is a full file system backup (bare metal plus daily differential, so includes the OS, etc.). I want to increase the backup frequency of some databases, so that in the event of failure, I can restore to a 15 minute time window, e.g. 1. Full backup at 04:00 2. Tran log backup every minutes afterwards What I cannot find *any* clarity on is whether the R1Soft backup (done via VSS Writer ), will cause any problems with my approach (particuarly breaking the log chain). I know very little about VSS, and the more I read, the more confusing it gets. I contacted R1Soft to clarify, and their response was: > We are using the VSS for SQL backups. You can use both solutions as > far as it will not run in the same time. We use VSS writer to flush > the logs into data and after that full backup the databases. This means nothing to me, as I don't know what they mean by *"data"*, and it doesn't clarify the log chain concern. Therefore, can anybody with VSS experience please clarify whether VSS backups "interfere" with native full/transation log backups? From my research I see conflicting messages, because the Microsoft site states: > SQL Writer does not support... Log backups I don't know whether I should be doing what I propose, or whether I should instead - Ask the server hosts to amend the database backup frequency to 15 mins - Prevent the R1Soft database backup, and handle this manually, then let it just shadow copy the backup file Any input, even if just to highlight the questions I should be asking them, would be appreciated. The more I read, the more confused I am getting. ### Update as per answer database_name backup_start_date backup_finish_date expiration_date backup_type backup_size MB logical_device_name physical_device_name backupset_name description is_copy_only is_snapshot checkpoint_lsn database_backup_lsn differential_base_lsn first_lsn fork_point_lsn last_lsn --------------- ----------------------- ----------------------- ----------------- ----------- ------------------ ----------------------- --------------------------------------------------- --------------- ------------- ------------ ----------- --------------------- --------------------- ---------------------- --------------------- ---------------- --------------------- myDb 2017-09-13 02:00:04.000 2017-09-13 02:00:05.000 NULL Full 1525.43896484375 NULL {D827E1B8-FDB7-4AE5-9264-08D4CA29536A}1 NULL NULL 1 1 12207000004436400001 12207000004429300036 NULL 12207000004436400001 NULL 12207000004436700001 myDb 2017-09-13 00:11:00.000 2017-09-13 00:11:01.000 NULL Full 1525.44726562500 NULL {9B33317C-CABA-42DD-9839-9D4599A91205}1 NULL NULL 0 1 12207000004429300036 12207000003990700036 NULL 12207000004429300036 NULL 12207000004431300001 myDb 2017-09-12 02:00:13.000 2017-09-12 02:00:14.000 NULL Full 1525.32031250000 NULL {57B7F13B-9461-48C2-8BB3-3DA651485DC6}1 NULL NULL 1 1 12207000003995300034 12207000003990700036 NULL 12207000003995300034 NULL 12207000003996900001 myDb 2017-09-12 01:11:28.000 2017-09-12 01:11:29.000 NULL Full 1525.32226562500 NULL {924FE276-544D-40F6-93A2-C2375868DB07}1 NULL NULL 0 1 12207000003990700036 12207000003659900164 NULL 12207000003990700036 NULL 12207000003992700001 myDb 2017-09-11 13:33:08.000 2017-09-11 13:33:25.000 NULL Full 1526.40234375000 NULL D:\HostedFiles\Autobackup\bak\20170911_myDb.bak NULL NULL 1 0 12207000003837600221 12207000003659900164 NULL 12207000003837600221 NULL 12207000003846900001 myDb 2017-09-11 00:07:59.000 2017-09-11 00:08:00.000 NULL Full 1525.28271484375 NULL {713D7A36-D4F2-4B2F-A0C1-B079DE03F396}1 NULL NULL 0 1 12207000003659900164 12207000003645600222 NULL 12207000003659900164 NULL 12207000003666600001 myDb 2017-09-10 02:00:17.000 2017-09-10 02:00:17.000 NULL Full 1525.25146484375 NULL {3C42FCFF-BF45-49D6-AD78-57039DB7B4DA}1 NULL NULL 1 1 12207000003657200001 12207000003645600222 NULL 12207000003657200001 NULL 12207000003657500001 myDb 2017-09-10 00:05:34.000 2017-09-10 00:05:36.000 NULL Full 1525.29394531250 NULL {AC33EA68-9B40-4CE6-A2E5-76DE908AD813}1 NULL NULL 0 1 12207000003645600222 12207000003613700036 NULL 12207000003645600222 NULL 12207000003654600001 myDb 2017-09-09 04:06:22.000 2017-09-09 04:06:23.000 NULL Full 1525.26367187500 NULL {6BCA937F-7F1C-4A43-92D8-42366D36FA17}1 NULL NULL 0 1 12207000003613700036 12189000000394000087 NULL 12207000003613700036 NULL 12207000003616500001 myDb 2017-09-09 02:00:04.000 2017-09-09 02:00:21.000 NULL Full 1525.28076171875 NULL {6CD064AC-D2DC-4F84-97A7-88C3D959FEF4}1 NULL NULL 1 1 12207000003607000144 12189000000394000087 NULL 12207000003607000144 NULL 12207000003613500001 myDb 2017-09-08 02:00:04.000 2017-09-08 02:00:07.000 NULL Full 1524.68896484375 NULL {92C322C7-AB8A-4747-A765-4D63A86BDC50}1 NULL NULL 1 1 12189000000855600001 12189000000394000087 NULL 12189000000855600001 NULL 12189000000855900001 myDb 2017-09-08 00:30:00.000 2017-09-08 00:30:17.000 NULL Full 1525.38671875000 NULL D:\HostedFiles\Autobackup\bak\20170908_myDb.bak NULL NULL 1 0 12189000000846800208 12189000000394000087 NULL 12189000000846800208 NULL 12189000000855600001 myDb 2017-09-07 07:42:05.000 2017-09-07 07:42:06.000 NULL Full 1524.51806640625 NULL {77D9CBB9-60E4-4D19-99CF-B92499E33BA7}1 NULL NULL 0 1 12189000000394000087 12188000005293700036 NULL 12189000000394000087 NULL 12189000000397700001 myDb 2017-09-07 02:00:05.000 2017-09-07 02:00:10.000 NULL Full 1524.54052734375 NULL {4D94E390-FBCD-42D5-9191-A7AE9CED4932}1 NULL NULL 1 1 12189000000384900197 12188000005293700036 NULL 12189000000384900197 NULL 12189000000393200001 (14 row(s) affected)

Client's SQL Server farm DBs are backed up by third-party tool that does **not** use VSS. About half of the instances show backups being made by some tool that **is** using VSS. I can disable SQL Server VSS Writer to stop these errant backups, but how do I track down what is running the VSS backups? **keywords**: Volume Shadow Copy Service, volume-shadow-service [I don't have rep to create tags]

This one's got me a bit perplexed. How might one determine what process/program is making file-level backups via VSS/VDI and the SQL Writer service? I've tried using Profiler to trace backup/restore events, and that just shows me SQL Writer itself creating the backup events. I don't see anything in the Windows event log indicating what's talking to SQL Writer, nor does SQL Server put anything meaningful in its own error log. I've got a situation where *something* is making snapshot backups and breaking the differential chain, but we're not sure what the culprit is. It's undoubtedly some kind of backup agent, or a virtualization assistant that talks to SQL Writer to allow for VM snapshots, but we need some way to pin it down.

Working with an interesting scenario that quite frankly, I have not had the time to test, and would love some expert insight. I have a question concerning transaction logs, and their possible use for recovery (or anything at all for that matter). Imagine that you are using VSS to take application consistent snapshots of volumes holding both .mdf and .ldf files for the database (storage array driven). The questions I have is: Given that only VSS snapshots are available (point in time), I am wondering if there is any use in having a database in full recovery (assuming there was a way of truncating the transaction logs as well). So, the setup is: database is in full recovery -> VSS snapshot of database -> truncate transaction logs. Can the transactions in the log be of any use to me if I were to mount a clone of the database?

We're currently using AppAssure to back up the SQL servers (2012/2014). It uses VSS snapshots. All databases are in simple recovery mode. Do I need to bother backing up the drive that contains the ldf files or is it enough to just back up the mdf files and let the log files recreate during a full restore? [SQL Writer Service](https://msdn.microsoft.com/en-us/library/ms175536.aspx?f=255&MSPPError=-2147217396) explains how VSS can be used to take application consistent snapshot backups of the database files directly. It says it does not support log backups, which is why I asked this question. [Snapshot Backups](https://technet.microsoft.com/en-us/library/ms189548(v=sql.105).aspx) are a feature of SQL server that allow hot backups without doing traditional dumps.