Restoring a BCM database from SQL .mdf and .ldf files (Windows Vista or XP)


If you need to restore a BCM database from the .mdf and .ldf SQL database files, you can use the following script to attach the SQL database.


Be sure to install the latest version of BCM and all service packs before running this script, since BCM will not connect to databases that were created using newer versions of BCM.


Copy the text below into Notepad and follow the instructions.  Let me know if you have any questions about this. 
~ Clinton Ford


@ECHO OFF
REM Restore a BCM database from the SQL .mdf and .ldf files (Windows Vista or XP)
REM ——————————————————————————–
REM Instructions
REM 1.) Install and configure BCM on the destination computer.
REM 2.) Copy your backup .mdf and .ldf files into the BCM database folder.
REM      To do this, click Start | Run, then enter the following text into the text box and click “OK”:
REM           %LOCALAPPDATA%\Microsoft\Business Contact Manager\
REM     NOTE: You might need to rename your .mdf and .ldf files if a pair of files with the same name already exists
REM 3.) Now, modify this script in Notepad by doing the following:
REM 4.) Press CTRL+H in Notepad to open the Replace dialog
REM 5.) Type <BACKED_UP_DATABASE_NAME> in the “Find what” box
REM 6.) In the “Replace with” box, enter the name of the backed up BCM database (without the .mdf file extension)
REM 7.) Save this script to your Desktop as RestoreDB.cmd
REM 8.) Run this script by double-clicking on the RestoreDB.cmd file on your Desktop
REM 9.) From the main Outlook window, select “Business Contact Manager | Database Tools | Create or Select a Database…”
REM 10.) Select your backed up database from the drop-down list and click “Next”


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -b -E -S .\MSSMLBIZ -Q “EXEC sp_attach_db @dbname = ‘<BACKED_UP_DATABASE_NAME>’, @filename1 = ‘%LOCALAPPDATA%\Microsoft\Business Contact Manager\<BACKED_UP_DATABASE_NAME>.mdf’, @filename2 = ‘%LOCALAPPDATA%\Microsoft\Business Contact Manager\<BACKED_UP_DATABASE_NAME>.ldf’;”>>”%LOCALAPPDATA%\Microsoft\Business Contact Manager\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Attach DB Returned:%ERRORLEVEL%>>”%LOCALAPPDATA%\Microsoft\Business Contact Manager\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Attach DB Returned:%ERRORLEVEL%


Comments (42)

  1. buckfam6 says:

    i have attempted to follow this process, however when I goto start and run and put in  %LOCALAPPDATA%MicrosoftBusiness Contact Manager

    I receive an error message that says "WINDOWS CANNOT FIND  %LOCALAPPDATA%MicrosoftBusiness Contact Manager.

    What is my next move?

  2. steves2983 says:

    I received the same error so I’ve got the same question, what next?

  3. dsolis says:

    I uninstalled BCM2003 and installed BCM2007. Everything looks ok except for the email history . We dont see the old history and there does not seem to be any history being created. Any  help would  be appreciated.Thanks.  tsscontact@yahoo.com . David Solis

  4. Jerry Blake says:

    To link existing emails to the contacts, you can click BCM->Manage E-mail Auto Linking menu and use Search and Link functionality.

    Make sure the all contact email addresses and folders are checked in the the dialog that comes up when you click the Manage Email Auto Linking dialog.

  5. leffler says:

    We upgraded from BCM 2003 to BCM 2007.  All looked well until we noticed some of the contacts in BCM did not port over.  I have a backup of the old data in the form of SQL 2000 backup, and I have it live on SQL 2005.

    How can I convert this old data into the BCM 2007?  Unfortunately, I don’t have a .bcm backup, only every other backup.

    Are there any good scripts I can use to bring over the missing data?  Any help will be appreciated — larry@dangelocpa.com

    Thank you, Larry Leffler

  6. Jerry Blake says:

    Hi buckfam6 and steves2983,

    %LocalAppData% is an environment variable only in Vista.  There is an enbironment variable %AppData% in XP, but that is not what we want here.  Apologizies for not mentioning this in the script.  

    You can introduce a new environment variable in XP by going to Control Panel –> System –> Advanced –> Environment Variables.

    -Sateesh

  7. Jerry Blake says:

    Hi leffler,

    Did you try the above script?  If I understand correctly, you have mdf and ldf files in SQL Server 2005 which you want to restore in BCM V3.

    -Sateesh

  8. Jerry Blake says:

    Hi leffler,

    There is another post in this same blog:  Restoring an Automatic SQL backup.  Please see if that helps you.

    -Sateesh

  9. Mike Bassell says:

    Our customers don’t want to upgrade to Ofice 2007; they want Windows XP reloaded and all their app functionality restored.

    I’ve modified your script for Win XP and BCM 2003, e.g. "C:Program FilesMicrosoft SQL Server80" instead of "…90", etc., but the program "sqlcmd.exe" is nowhere to be found, nor does the subfolder ".MSSMLBIZ" exist.

    Do you have an MDF restore solution that works for BCM 2003?

    Thanks.

  10. Jerry Blake says:

    Hi Mike,

    If you have BCM V2, the SQL instance name is not MSSMLBIZ.  It has a different name starting with MICROSOFT.  Till BCM V2 SP3, BCM shipped with MSDE.  Only from BCM V2 SP4 that MSDE was replaced with SQL Express.

    Inorder for the script to work with BCM V2, you need to change the V2 instance name, the location of the MSDE sql exe file, the location where BCM V2 stores the mdf and the ldf files.  Try making these changes and let me know if it works.

    -Sateesh

  11. This happens to me at least twice a year with BCM.

    something crashes the machie.  This time a bad memory stick

    Now BCM will not load and it tells me to start the SQL.  It wont start and tells me to go to a MS web site for help.  I go through the people chatter and try the three things that are suggested.  Uncompresisng a file, directory, running a repair utility, and nothing works.

    I cannot get bcm to even start.  Should I have some support package like I bought with ACT $149.00 per year to fix bugs?  This should not be that hard.  And now I have been without a database I need for over a week.  LAst time I had India help it took two weeks to get my $59.00 help to come up with a solution.

  12. CarolineK says:

    Not sure if I am in the right place but having difficulty with BCM and the product and services list.  We have been using Opportunitys to create our quotes which then link to Accounting 2008.  Why if I put new products and services into the list in bcm  they not show in the accounts products and services but does if its done the other way round. And why when you add products or services in the quote itself does not not save to the product and services list.  Input a whole load of info into a quote believing its saving to the list.

    Any advice would be gratefully recieved

    Thanks  

  13. Mike George says:

    Hey guys great post, a water main broke at work when they were installing showers floor above.  In our recovery efforts we managed to save the MDF and LDF files which are crucial.  I installed SQL Server 2000 on my new machine and ran the script provided (I’m sorry I accidently overwrote the original .txt result) and here is the .txt result from the second go:

    HResult 0x2, Level 16, State 1

    Shared Memory Provider: Could not open a connection to SQL Server [2].

    Attach DB Returned:1

    Msg 1801, Level 16, State 3, Server DAVETMSSMLBIZ, Line 1

    Database ‘MSSmallBusiness’ already exists. Choose a different database name.

    Attach DB Returned:1

    Msg 1801, Level 16, State 3, Server DAVETMSSMLBIZ, Line 1

    Database ‘MSSmallBusiness’ already exists. Choose a different database name.

    Attach DB Returned:1

    Msg 1801, Level 16, State 3, Server DAVETMSSMLBIZ, Line 1

    Database ‘MSSmallBusiness’ already exists. Choose a different database name.

    Attach DB Returned:1

    Now when I start Outlook and goto Database Tools I get,

    Starting SQL Server when I select a menu item. Create or select a Database there is no option for my original database MSSmallBusiness, however

    If I go into Manage Database, I get an unable to open MSSmallBusiness Operating System Error 5 if I click on the other databases tab.  I did some research to see if I could see why it isn’t working, one thing I found odd is when I created a new blank Dbase the following use was in the security for the new .mdf file SQLServer2005MSSQLUser$DAVET$MSSMLBIZ (DAVETSQLServer2005MSSQLUser$DAVET$MSSMLBIZ)?, but is not nor can I appear to add that user to my original file.

    I feel that I am very close!!  Need this data bad! any help would be much appreciated.

  14. Jerry Blake says:

    Hi Mike

    So did you copy the .mdf and .ldf file to

    c:Documents and Settings<user name>Local SettingsApplication DataMicrosoftBusiness Contact Manager folder?

    OS Error 5 means it is there, but you have been denied access. Is the login information same on the old machine and the new machine?

    Thanks

    Vinit [MSFT]

  15. Mike George says:

    The login information as per user is the same but the computer names are different?  Is there an issue in trying to run the Dbase in SQL Server 2000?, when I go into the ENterprise Manager I cannot drop down to the attached databases because it requires SQL 2005, but when I try to install SQL 2005 I have a problem with the instance of SQL 2005 Express?

    Thanks

  16. Jerry Blake says:

    So i believe you are using BCM 2003 i.e BCM Version 2. Is this on XP or Vista?

    If you have Enterprise Manager for 2000 it should work with 2000 database. Did you migrate your database from SQL 2000 to SQL 2005?

    Thanks

    Vinit [MSFT]

  17. Mike George says:

    I am using the BCM in XP that came with Office Ultimate 2007.  I didn’t knowingly migrate to 2005?  Essentially the Database ran fine on the old computer until it got damaged, I was able to recover the ldf and mdf files.  

    I reinstalled the same copy of Office Ultimate on the new laptop I recieved from a coworker.  I have a copy of SQL 2005 and 2000.

    Thanks for your help.

  18. Jerry Blake says:

    i see. Did you try renaming the .mdf file (before you do that please make a copy of your .MDF file) and running the command above? From the error it says the file already exists.

    -Vinit [MSFT]

  19. bmex63 says:

    BCM Team,

    I have the same issue that Mike Bassell mentioned in a previous comment.  I have a clients computer with BCM 2003 and this script does not appear to work.  

    Sateesh replied to him but I still don’t understand.  Can someone please clarify what exactly needs to be changed in order for the script to work?

    Thanks,

    – Ben

    —————————

    If you have BCM V2, the SQL instance name is not MSSMLBIZ.  It has a different name starting with MICROSOFT.  Till BCM V2 SP3, BCM shipped with MSDE.  Only from BCM V2 SP4 that MSDE was replaced with SQL Express.

    Inorder for the script to work with BCM V2, you need to change the V2 instance name, the location of the MSDE sql exe file, the location where BCM V2 stores the mdf and the ldf files.  Try making these changes and let me know if it works.

    ————————–

  20. bmex63 says:

    BCM Team,

    Environment:

    XP SP3

    Fresh Install of Office 2003 and BCM

    Installed BCM SP1

    Cannot located the sqlcmd.exe file….

    Thanks,

    – Ben

  21. Jerry Blake says:

    Hi

    If you have BCM 2003, you should probably update it with SP3. Sqlcmd.exe can be found under

    C:Program FilesMicrosoft SQL Server"version of sql"ToolsBinn

    Thanks

    Vinit [MSFT]

  22. idmachines says:

    I have the same issue with mdf and ldf,

    I have them renamed in the localbusiness manager directory (I renamed them idmachines)

    Yet when I go to bcm database tools select it doesn’t see idmachines

    After I ran the script the idmachines restore result.txt only contained file attach db returned 0

    Vista SP1

    2007 Outlook 12 and BCM 3

  23. Gary1029 says:

    I’m very much a novice at data recovery.  Here’s my story:

      I run XP Pro and I upgraded from Office for Small Business 2003 to Office for Small Business 2007. The upgrade went well, including the recognition of my existing BCM files.  But, Outlook 2007 with BCM ran slowly and there was a connectivity issue to my corporate exchange server they haven’t de-bugged yet.  

      So I elected to uninstall Office for Small Business 2007 and reinstalled Office for Small Business 2003.  That went well except it won’t recognize my BCM .mdf or .ldf files.  

      I spent four and a half hours on the phone with Microsoft’s BCM team in southern India yesterday trying to rename the files so they could be recognized in BCM 2003 again.  They gave up on it saying the files were corrupted and are unrecoverable because of the upgrade to 2007, then subsequent reversion to 2003.  I don’t buy this explanation yet.

      Any guidance on recovery is greatly appreciated.  I have a few hundred business contacts with related phoncon records, appointments, etc that are quite valuable to my business.

      Thanks for taking a minute to read my sad story and for any assistance that may come of it.  If necessary I’ll find someone with lots more chops at this than me to carry out any instructions offered.

      Gary1029

  24. Jerry Blake says:

    Hi Gary

    When you would have upgraded from BCM 2003 to 2007 we would have created a back up of your database  under "My Documents/My Business " folder. You can simply restore it in BCM 2003.

    let me know if this helped

    Thanks

    Vinit [MSFT]

  25. Gary1029 says:

    Vinit,

      Thank you for your response.

      A file titled GarysBusinessContactManager.sbb does indeed exist right where you said it would be!  It is dated for the date I performed the upgrade to 2007.  

      How do I restore it so that BCM 2003 will recognize it?  I clicked on Database Tools, Manage Database, and Restore.  It’s looking for file extension .msbcm.  The file is currently named GarysBusinessContactManager.sbb.

      Thanks for your assistance.

    Gary

  26. PhilipW says:

    Hi BCM Team,

    My old laptop was sent off for repair but was lost by the courier.  I am trying to restore my BCM from files I had on the hard drive.

    I am not a technical expert but managed to restore it once with the help of my neighbour – however stupidly I then deleted the extra databases (MSSmallBusiness2) and then found that BCM would no longer work.  I have since restored and reformatted the hard drive a couple of times to try a clean reload but have not succeeded.  

    The text files that have been created after running the RestoreDB.cmd say

    HResult 0x2, Level 16, State 1

    Shared Memory Provider: Could not open a connection to SQL Server [2].

    Attach DB Returned:1

    Attach DB Returned:0

    and

    HResult 0x2, Level 16, State 1

    Shared Memory Provider: Could not open a connection to SQL Server [2].

    Attach DB Returned:1

    plesae can you help me get back up and running

    many thanks

    Philip

  27. Jerry Blake says:

    Hi Philip,

    I searched online for the error message that you are getting and found a useful acticle that could help you.

    Please refer to this and let me know if it helped

    http://littletalk.wordpress.com/2008/03/21/an-error-has-occurred-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-all/

    Thanks

    Vinit [MSFT]

  28. NeoImaginative says:

    Greetings.

    Being rather unfamiliar with BCM as a whole, I apologize for being rather verbose in this post. We are speaking about Outlook 2003 with the Business Contact Manager installed. Every time we enter Outlook we receive numerous: Managed MAPI Service Catastrophic Failure error messages. Could not initialize CLR. The parameter is incorrect. We have been trying to restore the BCM file. Recently, with the help of this article, we have recovered several important finds. In %LOCALAPPDATA%MicrosoftBusiness Contact Manager one WILL find the .ldf and .mdf files that are spoken about in this article. In any event, we are trying to restore the BCM file to allow the file to be opened. Now, interestingly enough, we created a ‘Test’ profile on the machine in question in Outlook 2003. The test profile opens with no error messages save an error about Microsoft Office Outlook SMS add-in. However, the second we try to add anything related to the Business Contact Manager file in, for example, Data File Management under the ‘File’ menu, we receive the Managed MAPI Service Catastrophic Failure message repeatedly. What should we do? Should we reinstall Office Outlook 2003? What risk does this pose to losing the Outlook Data files? Also, a separate Office Outlook 2003 version does exist on another computer. I have read here that installing Office 2007 creates a backup of the BCM files on the machine. I do believe that there is a trial version of Office 2007 installed, but I have yet to open Outlook or take a look at that. At the moment, the only concern is the recovery of the BCM file from the ldf and mdf files. They are titled: Prosearch.ldf and Prosearch.mdf. The mdf file is approximately 100,000 kB and the ldf is approximately 15,000 kB.

    Moreover, a glimpse in the Control Panel in Add/Remove Programs does not reveal much information about BCM. All that is listed for BCM in add/remove programs applet window is the Business Contact Manager Update for Outlook 2003. We are not that familiar with SQL and simply need assistance restoring this BCM file from these two files.

    Any help on this issue is greatly appreciated.

  29. keith says:

    does this method work on windows 7 and bcm 2003 to bcm 2007?    When i do the steps above, I am not able to see bcm (repaired) as one of the legit databases.  Any ideas?  

  30. excelans says:

    Hi guys,

    I use BCM 2007 on office 2010 on my personel computer (windows 7)… I export BCM database…. Then my IT manager import it company server 2003 then server 2005 on XP server… But I cannot add or change any record. Error message is : "must declare the scalar variable @ID"… I can still use it on my computer but not in server… Any suggestion… Please….

  31. Gjalt Lindeboom says:

    Hi Guys,

    I tried the above script, but I doesn't do the trick for me….

    My situation: Using BCM 2007, I am trying to start all over using an old offline database. I've got the folder which has the .ldf and .mdf file and also the two folders Cache and Config (not sure if I need those).

    At first I simply put the files and folders in the userappdatamicrosoftBusiness Contact Manager folder. Then I went to BCM to select that database instead of the current SmallBusiness database, but it wouldn't select that old database in the dropdown menu. I guess I'm missing the right file and BCM doens't recognise this as a 'complete' database?

    Net, I've tried the script to attach the two files, but keep getting the message that the 'name already exists'. I've tried renaming both the .ldf and .mdf file but then it give me the message that the filenames do not correspond to the primairy database file.

    Can anybody help me out on this?

    Would be greatly appreciated!

    Cheers,

    Gjalt

  32. idasafety says:

    I have the same problem as Gjalt.

    I have tried everything in this blog. My files (.mdf & .ldf) are in the correct place and yet they do not appear in the drop down menu when selecting a database.

    Any help?

    Calum

  33. George says:

    thaaanks…working perfectly with my BCM 2007 SP2 (after i changed the Path from the script to my german Windows version)

  34. david_storage says:

    Hello,

    I tried to follow the top posting but when I click the command, I get a notepad message with the following:

    HResult 0x2, Level 16, State 1

    Shared Memory Provider: Could not open a connection to SQL Server [2].

    Attach DB Returned:1

    Any ideas?

  35. I tried the same steps as Gjalt above with not much better outcome.  Not too keen on this program. It doesn't see the file in the drop down.

  36. travis says:

    I get this error?

    Attach DB Returned:3

  37. Somebody can help me please…??

    I can´t  install BCM in my offce 2010….

    because it or one of the requirement components failed to install….

    What can i do ??

  38. caresrg says:

    Will this script work for BCM for Outlook 2010 (32-bit)?

  39. Doug Johnson says:

    Hi,

    How can I extract the data from these database files? I am trying to move to a new CRM and need the history and opportunity information in a better format.

    Thanks,

  40. imacusser1 says:

    Jerry,

    The BCM I am working with was on a drive that failed.  I do not find an ldf file to go with the mdf file other than this "MSSmallBusiness._log.LDF".  So what to do about that?  Also, on the new computer I found the sqlcmd.exe file location in "Program Files (x86)microsoft sql80toolsbinn" rather than the path in your instructions.  Needless to say, running your script has done nothing to help.  Is there some other way to get my client's BCM into his new computer?

  41. Linda says:

    I did everything in the instructions. I definitely see the backed up database in the Outlook appdata folder. I ran the script with the replacement name and it showed it did something. I open Outlook and when I go to create or select database, the .mdf file doesn't show in the drop down box. Only the default one does. Any ideas as to what I might me doing?

    Thanks.