Use of third-party VSS backup plus native SQL backup

Question

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)

Answer 1

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 description given further up in my answer.

Solution

1. Keep your 3rd-party snapshots for disaster scenarios. They are consistent and bring the database back online fast.

2. Have additional FULL, DIFF and LOG backups of your database according to your requirements. Store these backups in a safe place to ensure you can access them when you have to restore a database to either the FULL, DIFF, LOG or Point-in-Time.

---

Additional Information after Edit of Question

You might want to follow up on database backups (full, diff, tlog) and copy_only by reading the following articles:

SQLskills.com

• The Accidental DBA (Day 7 of 30): Backups: Recovery Models and Backup Types
• BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain

Generally speaking: COPY_ONLY backups are advisable when you want to take additional backups outside of your normal backup sequence without breaking anything. Your normal backup sequence should not be bases on COPY_ONLY backups.