Monday, 11 August 2008

Business Contact Manager - Recovery via *.mdf and *.ldf only

We have a relatively new client that has been going through what is turning out to be a month long odyssey of getting their Lenovo X61 tablet working again.

After an initial diagnosis that the hard drive was failing, they made a backup of their data and the Lenovo on-site people showed up and swapped out the drives.

Two hard drives and three motherboards later, we need to get their Business Contact Manager (BCM) data back online.

One slight gotcha though: The only data we have to work with is the original MSSmallBusiness.ldf and MSSmallBusiness.mdf files.

How did we realize that we were facing a gotcha? When we went to import the data via the database management in BCM it did not give us the option to import a *.mdf file.

After some searching about, we came up with the following Business Contact Manager Team Blog post: Restoring a BCM database from SQL .mdf and .ldf files (Windows Vista or XP).

Noting comments in the post about the environment variable used in the script were for Vista, we made sure to point them to the physical files using the full path.

No matter how many ways that script was modified to get it to work, we could not get those files to mount.

This left us in a bit of a pickle. How do we proceed?

Note the command line structure in the script itself. It contains osql commands. And, we SBSers all know where we need the osql commands (previous blog post on taming SQL's memory usage) don't we?

A quick search for sql 2005 sp_attach_db leads us to: MS KB 224071: How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server.

Since our BCM database is already detached, we need only attach it again.

But, before we try that, we remove BCM off the system altogether.

After making sure that BCM was fully removed from the system, we reinstalled it.

Run services.msc to verify that the MSSSQL (MSSMLBIZ) database instance is up. Do not run Outlook yet because the BCM will try and initialize a new database during its setup phase.

The databases would be located in: C:\Documents and Settings\%username%\LocalSettings\Application Data\Microsoft\Business Contact Manager\MSSmallBusiness.mdf.

We copied the recovered database and log files into the above location.

From there, we ran the following at the command line (assume Enter after each line):


  1. hostname (system will reply with the name of your computer)
  2. osql -E -S MyComputer\MSSMLBIZ (assume MyComputer is your hostname)
  3. use master
  4. go
  5. sp_attach_db 'MSSmallBusiness','C:\Documents and Settings\Administrator\Local Settings\Application Data\Microsoft\Business Contact Manager\MSSmallBusiness.mdf','C:\Documents and Settings\Administrator\Local Settings\Application Data\Microsoft\Business Contact Manager\MSSmallBusiness.ldf'
  6. go
  7. quit
Note that osql can be run from within the database folder because the SQL binn folder resides in the System Path.

Once we had a successful database and log file attach to the MSSMLBIZ instance and fired up Outlook, all of the BCM content was there.

We then imported the recovered PST file and our client was good to go.

UPDATE 2008-10-15: If you get an access denied error on the attach attempt then BCM is not installed properly. Run BCM, create a new set of dbs (generally MSSmallBusiness2), and then uninstall BCM, copy the original dbs into the directory, and attach again.

If you need help recovering your databases, please drop us a line: BCM Recovery.

UPDATE 2008-11-03: Added the hostname step to make sure you find the correct system name.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists

*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.

16 comments:

Anonymous said...

I love you for this....no seriouslly... I love you!

Philip E. said...

You are welcome! :D

Philip

Brett G said...

You saved me 2 weeks of hard work!! I love you too! hehe.

I had that sinking feeling when I realised that it wasn't going to be as as simple as recovering the database files.

Thank you so much Phillip.

Philip E. said...

Brett,

Glad you found the information useful.

Philip

A.Stock said...

First, I'm not a database guy, so this is a bit outside my expertise. But i'm a quick study and a compute nerd nonethelsee, so I'm hoping I can figure this out.

I'm on Win XP, and got as far in your instructions as to open a command prompt (Start | Run| "command") and trying to type the osql lines as you have them.

I get the error message saying:

"[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Native Client] Login timeout expired
[SQL Native Client]An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

First, i have no idea what version of SQL Server is running on my system. I have Office 2007 to whick I downloaded/installed BCM from microsoft.com. During that process, MS just auto installed whatever version of SQL they're handing out I suppose. It sounds like (from the error message) that it's trying to access some remote location. i have no idea what that's about.

I thought that maybe i just needed to have the BCM databases (the mdf and ldf files) in the same directory from which I was running the osql commands, but I couldn't navigate the DOS prompt to appropriately get to the right directory. But i did get to a DOS version of my desktop (via the 'command' line above), so I put the BCM databases on my desktop and ran the osql commands...but to no avail. (I got the same error message quoted above).

As you can tell, i'm way out of my league here and any help you can provide here is GREATLY appreciated....seriously, without this data, i'm not sure what i'll do to recover my business data.

thanks soooo much for your help on this!

-Andrew

Philip E. said...

Andrew,

Make sure your own computer name is specified where it says "MyComputer\MSSMLBIZ".

Philip

Jeramy said...

So much love for you right now... brilliant!!!

Anonymous said...

I've tried this for BCM 2003, can't get it to work, the services are named diferently. Can you point me in the right direction please. Thanks
LCW

Philip Elder SBS MVP said...

A,

The above instructions should work. If the default instance installed by BCM 2003 is different, then modify the MyComputer\MSSMLBIZ to MyComputer\YOURBCMInstance name.

The instructions are pretty maleable that way.

Philip

Anonymous said...

Philip, thanks for advice.
I am getting a database already exists error now. There is another service: MICROSOFTBCM which disappears when I uninstall BCM, but service MICROSOFTSMLBIZ remains.

After I re-install BCM, copy the 2 files into the directory I do the procedure as stated:

C:\DOCUME~1\LW\LOCALS~1\APPLIC~1\MICROS~1\BUSINE~1>osql -E -S PRO-NB
\MICROSOFTSMLBIZ
1> use master
2> go
1> sp_attach_db 'MSBusinessContactManager','C:\Documents and Settings\LW\Local Settings\Application Data\Microsoft\Business Contact Manager\MSBusinessContactManager.mdf','C:\Documents and Settings\LW\Local Settings\Application Data\Microsoft\Business Contact Manager\MSBusinessContactManager.ldf'
2> go
Msg 1801, Level 16, State 3, Server PRO-NB\MICROSOFTSMLBIZ, Line 1
Database 'MSBusinessContactManager' already exists.
1> quit

Any help appreciated, thanks.
LenW

John H said...

I tried overseas online tech support, I tried North American online tech support, I tried local on site tech support. Phillip did in one day what they could not do in 4 weeks. A real refreshing experience when you get to work with someone at the top of their game. Accolades, kudos and thanks.

Anonymous said...

I love you so much I want to have your baby. Thanks for such a great set of instructions.

Phlipped said...

Excellent work, helped lead me to a solution. I kept having a problem with an error "database already exists". To correct this I had to install SQL server management studio. Detach and attach the database. When attaching the database, deselect the logfile ldf. BCM pulled up the database without a problem after that.

Anonymous said...

Excellent work!!
Perfect description!

I just can say, I love you, too ;)

Ray said...

Thanks so much for this information. I can't tell you how long I have been struggling with this issue. It has been a nightmare. I can't wait to try your solution.

I will be a hero!!!

Thanks

bgroma said...

Hello, I tried UPDATE 2008-10-15 but I got the message back "the physical file xxx can't be opended" error 5 access denied
Using BCM 2010
Have you any suggestion how to solve this?