SQL Server Backup Woes? ASR Agent or VSS Issues Explained

Table of Contents

Having trouble backing up your SQL Server 2008 or R2? Specifically, are you seeing ASR Agent or other non-component VSS backups failing? You’re not alone! This issue is a known headache, but thankfully, there are workarounds. Let’s dive in and get your backups running smoothly again.

image just illustration

The Problem: VSS Backup Fails with SQL Server 2008/R2¶

Imagine this: you’re trying to back up your SQL Server 2008 or R2 database using a non-component VSS backup tool (like ASR Agent), and bam! It fails. The error message points to SQLServerWriter, even though the SQL Server error log says the backup was successful. Confusing, right?

You check vssadmin list writers and see SQLServerWriter is in a failed state with a “Non-retryable error.” Unfortunately, this error message isn’t very helpful in pinpointing the exact cause. A deeper dive into the SQLServerWriter trace reveals errors related to retrieving file information during the post-snapshot phase.

image just illustration

Why Does This Happen?¶

The root of this issue lies within the SQLServerWriter component itself in SQL Server 2008 and R2. It’s a bug that interferes with non-component VSS backups.

The Solution: Upgrading Shared Components¶

Unfortunately, there’s no direct fix for SQL Server 2008 or R2. The good news? This was addressed in SQL Server 2012 and later versions. Since SQLServerWriter is a shared component, the trick is to upgrade these shared components without needing a full SQL Server upgrade.

Here’s the recommended approach:

1. Install SQL Server Express: Download and install a free edition of a newer SQL Server version (like SQL Server Express 2014 SP3 or later, depending on your operating system). During the installation process, choose “Upgrade shared features only”. This will update the shared components, including SQLServerWriter, to the newer, bug-fixed version.

2. Keep It Updated: After installing SQL Server Express, make sure to install relevant cumulative updates. This ensures SQLServerWriter remains up-to-date and protected against other potential issues.

Which SQL Express version?

• For Windows Server 2012 or later, or Windows 8 or later: Use SQL Server 2016 or SQL Server 2017 Express Edition.
• For Windows Server 2008 or Windows Server 2008 R2: Use SQL Server 2014 Service Pack 3 (SP3) Express Edition.

image just illustration

What Gets Upgraded?¶

When you upgrade the shared components, you’re not just updating SQLServerWriter. Other shared components like Integration Services, Master Data Services (MDS), SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), and SQL Server Books Online will also be upgraded. This is generally a good thing, as it brings these components up to date as well.

Alternative (Less Recommended) Workaround¶

Another option is to install a “dummy” SQL Express instance of a later version. This will also upgrade the shared components. You can then disable or uninstall the dummy instance. However, directly upgrading the shared components as described above is the cleaner and recommended approach.

Key Takeaways and Next Steps¶

• No Direct Fix for 2008/R2: There’s no patch specifically for this issue in older SQL Server versions.
• Upgrading Shared Components is Key: The solution involves upgrading the shared components using a newer SQL Server Express installation.
• Choose the Right Version: Select the appropriate SQL Express version based on your operating system.
• Stay Updated: Install cumulative updates for your chosen SQL Express version to keep everything running smoothly.

Hopefully, this helps you resolve those pesky VSS backup failures! If you have any questions, thoughts, or further tips to share, please leave a comment below. We’re always eager to learn from each other and help out the SQL Server community. Come back and visit again if you have other SQL Server woes—we’ll do our best to tackle them together!