Error message

MSSQL: error: backup of database `XXX` on `XXXXX` server instance failed unexpectedly: Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken =89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
MSSQL: error: unexpected System.IO.FileNotFoundException exception while attempting backup: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

SQL Backup tab within RvxConfig may also show:

Error enumerating sql databases: Could not load file or assembly 'Microsoft SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

Steps to resolve

This error means that the necessary version of the SQL Server assemblies aren’t available on the client computer.

The two documents on SQL Server compatibility covers this in detail (see attached). If the error indicates you’re looking for version 10 the client version is newer than 3.4.0.49 and you’ll want the SQLServerCompatibility2008 file. If the error indicates we’re looking for version 9 (in the above example, Version=9.0.242.0), the client version is 3.4.0.49 or older, and you’ll want the SQLServerCompatibility file. The version of the SMOs that you need to install is based on the Backup Agent, not the version of SQL you are running. If the client you are running is 3.4.0.49 or later, you’ll need to install the 2008 SMOs, if the client you have is earlier than 3.4.0.49 you’ll need the 2005 SMOs.

Please note that loading the 2005 SMOs (version 9) on a 2008 SQL Server is in no way downgrading. Also, while the 2008 entire pack isn’t compatible with a 2005 SQL server, the SMOs alone are compatible and shouldn’t cause any problems with the SQL Server.

The SQLServerCompatibility2008 file SMOs require the Microsoft(r) System CLR Types for SQL Server(r) 2008 R2 SQLSysClrTypes.msi file be installed as a prerequisite for installing the Microsoft(r) SQL Server(r) 2008 R2 Shared Management Objects.

Note

It turns out SQL 2008 R2 Reporting Services Sharepoint 2010 Add-in will make the 2005_Xmo.msi install fail with an error Higher version already installed. This may affect you if you have Small Business Server, as sharepoint comes bundled with this system.

Uninstalling the reporting services will resolve this issue.