Thursday, May 11, 2006

SPS 2003 and SQL 2005 - backups

Here's an interesting one. We recently moved our SQL databases for SPS from SQL 200o to a SQL 2005 server. All went well, apart from the SPSBackup.exe scripts. We were getting the following error in the SharePoint logs:

[Microsoft][ODBC SQL Server Driver][SQL Server]To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO).

To solve the problem we uninstalled SQL 2000 client tools from the SPS server and installed the SQL 2005 client tools. That sorted it out for us. The important thing here is that you have to uninstall the SQL 2000 client utilities, otherwise, you'll continue to get the error.

I guess this also means that if you have a portal farm that is using several SQL databases - for different portals, they should all use the same version of SQL.