2

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.

5

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.

1

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?

You have to either test it yourself or ask the vendor, as for question that can snapshot backups which under the hood use VSS to connect to database and take backup break the backup chain the answer to this is yes it may and I am saying this because I have faced this issue. Also note that the characteristics of breaking that backup chain lies with snapshot backup only. TP tools which take normal backup also use VSS but they do not break log chain at least I have not faced such issue but you need to be careful with snapshot backups and I guess your tool is doing the snapshot backup because it is freezing the I/O to get consistent view of the disk and then taking snpahot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.