1

Our colocation provider uses VSS to backup our dedicated server. The backups show up in the SQL logs as SQL backups even though they are not and that breaks the Full-Diff chain. The issue is well documented on StackExchange Database Administrators, but they all stop there.

Is it possible to do TSQL differential or incremental backups on a system that also uses VSS?

If not, is there a better approach for off-site backups assuming that we don't want to lose the onsite full-system VSS backups?

SQL Server 2012 SP3 if that matters.

  • Why can't you do regular SQL backups or why can't they do it for you? – Antoine Hernandez Nov 9 '16 at 18:39
  • VSS backup as far as I know not breaks backups chains by resetting LSN its the snapshot backup. VSS backup also means backup by third party tools although snapshot backup also relies on VSS for backup – Shanky Nov 10 '16 at 5:51
  • @Antonine - We are doing regular SQL backups. A daily full then every couple of hours a differential. VSS backups can break the differential chain. – Alien Technology Dec 4 '16 at 15:09
  • @Shanky - I will find the URL and post here, but apparently VSS backups put the DB in a state as if they were going to do a backup so they can get a clean copy. This state resets the LSN. – Alien Technology Dec 4 '16 at 15:11
  • I can't find the write-up that I was looking for, but these pages describe the issue from different viewpoints: support.microsoft.com/en-us/kb/937683 support.microsoft.com/en-us/kb/951288 nixx.ca/wiki/doku.php?id=vmware:quiesce_sql – Alien Technology Dec 4 '16 at 16:02
1

It seems that there are two primary solutions to allow differential SQL backups on a server that does VSS backups.

1) On the full server backup, use COPY_ONLY if that is available.

2) Disable the "SQL Server VSS Writer" service.

Some full system backups using VSS record a backup to get SQL Server to pause long enough for a backup. This pause is recorded as if it were a backup but the file is never created.

In our case, disabling the SQL Server VSS Writer service seems to be the best approach. Since the database is not quiesced during the system backup, we assume that the database will not be in a valid state after a full system restore and we will have to restore our regular SQL Server backups at that time.

More information can be found at:

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”? How can I fix "Cannot perform a differential backup ... a current database backup doesn't exist"?

“Cannot perform a differential backup for database ”myDb“, because a current database backup does not exist.” https://serverfault.com/questions/147459/

Quiesce operations and SQL backups https://nixx.ca/wiki/doku.php?id=vmware:quiesce_sql

Cannot perform a differential backup because current backup does not exist https://sqlbackupandftp.com/blog/cannot-perform-a-differential-backup-database-current-database-backup-exist/

SQL differential backups failing after windows server backup https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35080750-ad5d-4841-b2ba-e17ee5a799d5

SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume https://support.microsoft.com/en-us/kb/951288

Error message when you try to restore a database by using SQL Server Management Studio in SQL Server 2005 after you use the Backup tool: "Restore failed for Server '' (Microsoft.SqlServer.Smo)" https://support.microsoft.com/en-us/kb/937683

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.