SQL Server Backup – Using Third-Party VSS Backup with Native SQL Backup

backupsql servervss

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        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)

Best Answer

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

Keep your 3rd-party snapshots for disaster scenarios. They are consistent and bring the database back online fast.
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

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.

Related Solutions

Sql-server – SQL Server transaction log backups: test whether tail log follows last known log backup

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with:

Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log backup.
NULL= No log backup exists. The database is offline or the database will not start.

Just to mention as well that Kalen also brings up the point that you will get a NULL value if the database is in SIMPLE recovery mode (autotruncate mode) or if no log backup exist.

But how can I obtain the FirstLSN of the tail log?

Without actually backing up the tail log of a database (don't have an test instance to try this on) you could logically conclude that the value returned in the column mentioned would be the first LSN of the next log backup, in your case the tail.

So executing the following will return the value I believe you are looking for:


SELECT 
   last_log_backup_lsn
FROM 
   sys.database_recovery_status
WHERE 
   databse_id = DB_ID('MyDb')

This DMV is available starting in SQL 2005.

EDIT
Unless you read the BOL link, please note that this DMV will only return values to databases that are online, or opened as BOL references it. If a failure occurs that requires you to take a tail log backup of a database you will not be able to verify this value through the above code unless the database is accessible; which in a failure it probably would not be.

SQL Server Log Shipping – Guide for SQL Server 2012

It just seems so fragile.

Logshipping is tested and proved since sql server 2000 (and even older) days. Its not fragile.

Look at the errors ...

Last Restored File: \server\folder\db_20160105060002.trn,

Logshipping is trying to restore

Destination: '\server\folder\db_20160105080001.trn'

This means you have a gap in the log sequence. There might be adhoc log backups happening which is breaking the log chain.

Refer to my answer - How does Log shipping knows to keep track.

You can even Restrict users to COPY ONLY log backups, so that adhoc log backups wont break the log chain. Also,

@Spörri made a valid point to disable SQL VSS writer service, so that 3rd party backup tool cannot interact with SQL. Its a pain to find that out, since 3rd party softwares are crazy sometimes !

To find out gaps in your log backups, you can use below query

SELECT 
    s.database_name,s.backup_finish_date,y.physical_device_name
FROM 
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE 
    (s.database_name = 'databaseNamePrimaryServer')
ORDER BY 
    s.backup_finish_date DESC;

Another useful query:

-- http://sqlblog.com/blogs/tibor_karaszi/archive/2014/11/03/can-you-restore-from-your-backups-are-you-sure.aspx
-- modified by Kin to include backup start and finish dates
SELECT TOP(100)
database_name
,CASE bs.TYPE
   WHEN 'D' THEN 'Full'
   WHEN 'I' THEN 'Differential'
   WHEN 'L' THEN 'Log'
   WHEN 'F' THEN 'File or filegroup'
   WHEN 'G' THEN 'Differential file '
   WHEN 'P' THEN 'Partial'
   WHEN 'Q' THEN 'Differential partial'
END AS backup_type
,bs.is_copy_only
,bs.is_snapshot
,bs.backup_start_date
,bs.backup_finish_date
,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_time_sec
,mf.physical_device_name
,bs.database_name
FROM msdb.dbo.backupset AS bs
  INNER JOIN msdb.dbo.backupmediafamily AS mf ON bs.media_set_id = mf.media_set_id  
  where database_name = 'master' -- change here for your database 
ORDER BY backup_finish_date DESC;