SQL Server Backup – Do VSS Backups Break Log Chain?

backupsql servervss

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.

Best Answer

You could verify the backup history with the following script:

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 

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 script will give you an overview of the backup media and files and if you look at the last column is_copy_only you will see if the backup is COPY_ONLY (1) or not (0).

If they are COPY_ONLY then they will not break the backup chain. Otherwise you might face issues as explained in the following article:

COPY_ONLY Backups with SQL Server

Starting with SQL Server 2005, a new backup option has been added that allows you to take full and transaction log backups in between your regularly scheduled backups without affecting the LSNs and therefore the sequence of files that would need to be restored. Since Differential backups backup all data pages since the last full backup, these types of backups do not affect the LSNs and there is no difference when using the COPY_ONLY feature.

You might want to check out my answer here which was posted in response to the question How can I backup an SQL Server database using Windows Server Backup?.

It is related to Windows Server Backup and VSS, but touches on some points you might encounter with the VSS backup strategy that has been implemented at your shop.

Related Question