Thursday, 25 October 2012

Restore Office Accounting 2009 on Windows 8 and SQL communication error

A Windows 7 PC that had been happily running Office Accounting 2009 refused to boot and couldn't be repaired, although all the files could be accessed ok by slaving the hard drive off another PC.

Just to make matters interesting, the replacement PC is Windows 8. I installed SQL Express 2008 (with loads of warnings about compatibility - just agree and make sure you load SQL 2008 SP3).

When I tried to restore an accounting backup it failed with SQL communication errors. Typically;

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Also tried creating a new accounting database and got the same SQL error about not being able to communicate.


I ran through the standard procedures for enabling remote access to SQL and ensuring the SQL protocols were setup correctly. Thanks to Beth Massi for a really helpful article about installing SQL Management Studio. But still no luck.

I eventually tracked it down to the Office Accounting Data Tools (remember to Run as Admin), look in Settings and there was a blank entry for the SQL instance name. I updated to SQLEXPRESS to match the instance name installed earlier (you can see it in the Control Panel > Programs list or any of the SQL tools) and communication was established.

I was then able to restore a backup ok.

Also able to copy the SBD and SBL files over from the old drive and use the Data Tools > Advanced Tools > Attach Data File to reconnect the latest Office Accounting database so we didn't lose any transactions since the previous backup. That's covered in Microsoft's move accounting database article. Worth noting that the Data Tools now list the SQL instance as MSSMLBIZ

6 comments:

Andy Morrison said...

Hi there

I too can use Office Accounts 2009 in windows 8 (x64) as I "upgraded" from windows 7 BUT and it's a big BUT - you cannot backup the data!

It keeps saying "there was an error accessing the file"?

Any ideas?

redleg said...

Hi Andy, I've not had a problem with backup, its working ok saving to Docs\Small Business Accounting\backups.

Will yours work to a different directory if you override it in File>Utils>Data Utils? What about if you Run as Admin when starting the Accounts program?

Andy Morrison said...

Hello again - apologies for the delay. Tried in different compatibility methods and eventually got it to back up - phew!

Only problem is my HD crashed and am now installing win8 from scratch.

Tried to restore an accounts file and now getting "the media family on device... in incorrectly formed. SQL server cannot process this media family. Restore Headeronly is terminating abnormally.

These things are sent to try us eh!

Grrr....

redleg said...

What a pain! Sure its good practice ;-) Just never when you want it.

Sounds like you got the wrong SQL version on there - older one maybe?

Mike

Andy Morrison said...

Thanks Mike for the info.

Looks like the problem is when I eventually managed to get it working (previous to the hard drive crashing) I had installed sqlexpress 2008 and sqlexpress2012 in the hope that the blooming MS software would sort itself out. Needing to have the accounts software to work was a priority so trying everything at that point. So when it did finally work I then backed up the database files from within Accounting 2009.

So it's now that the HD crashed and I am trying to get Accounting 2009 to restore the database file I get the new issues.

On the new system I have tried to install slqexpress 2008 (sp3) which "seems" to install ok, but when I then try and install sqlexpress 2012 it says that the 2005 version needs to be SP4 or greater even though I had initially installed 2005 Sp4.

For a MS product this is really starting to get on my nerves.

Will keep plodding along :o)

redleg said...

Good Luck! Sounds like you'll need some :-/

Trust 1&1 Internet for your domain name registration, from only £1.99/year!. Check now!