Use of third-party VSS backup plus native SQL 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.

• Full backup at 04:00

• 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 datab

I have posted answers on two occasions that relate to your issue.

Will VSS backups break logchain?

• My answer here

How can I backup an SQL Server database using Windows Server Backup?

• My answer here

Checking the 3rd-party backups

Basically you have to check the backup history in the msdb database to see how the database backups were created with the 3rd-party software.

With the following script you can retrieve some of the information relevant for further investigation:

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 

   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.fork_point_lsn,
   msdb.dbo.backupset.last_lsn

FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  

WHERE 1 = 1
ORDER BY   2,3 desc

The important information being is_copy_only and is_snapshot columns.

IS_COPY_ONLY

If the database backup history has the flag is_copy_only set to 1 then subsequent backups do not require these (3rd-party) backups to restore the database to a consistent state. This is because:


A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

Reference: Copy-Only Backups (SQL Server) (Microsoft Docs)

IS_SNAPSHOT

If the database backup history has the flag is_snapshot set to 1 then you know that this backup was performed using a 3rd-party software that triggered the SQL Server Writer (VSS Service for SQL Server) which allowed the 3rd-party software to backup the database almost instantaneously.

From the official documentation on what Snapshot Backups are:


SQL Server snapshot backup is accomplished in cooperation with third-party hardware or software vendors, or both. These vendors use SQL Server features that are designed for this purpose. The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.

Reference: Snapshot Backups (Microsoft Technet)

A backup created using this feature can also be restored almost instantaneously.

Summary

The 3rd-party backups should be marked as is_snapshot = 1 and is_copy_only = 1. These backups will not conflict with additional backup steps/procedures performed using native SQL Server BACKUP DATABASE..., BACKUP DATABASE ... WITH DIFFERENTIAL.... and BACKUP LOG... statements. The 3rd-party database backups are not part of an existing backup set.

Answering your questions

The vendor stated correctly, that during the (quick) snapshot backup, that other backups should not run.


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.

There is a slight chance that native backups could encounter an issue when the 3rd-party software triggers the SQL Server VSS Writer service, which mostly results in an IO Frozen message in the SQL Server ERRORLOG. Having native backups run at this time could possibly result in errors.

Then you have noticed that


SQL Writer does not support... Log backups

Correct. The SQL Writer Service is only triggered for backup snapshots and not required for native SQL Server backups using the normal statements. A backup snapshot of a database is a transactionally consistent state of the database at the point the SQL Writer was triggered.

Nearly Forgot...

Anything that has the is_copy_only flag set does not break the backup chain as per the descrip

StackExchange Database Administrators Q#185834, answer score: 6