2

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.

Alien Technology
  • 149
  • 4

1 Answers1

2

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

Community
  • 1
Alien Technology
  • 149
  • 4