Restoring an Automatic SQL Backup


In my previous post, I explained how to schedule tasks to automatically back up your BCM database using SQL scripts.  Hopefully you will never need to restore one of those backups.  But just in case, below is the script to help you do that in Vista or XP.  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 a SQL Database Backup (.bak file)
REM This script will restore a SQL backup (.bak) file over a new, blank BCM database
REM ——————————————————————————–
REM Instructions
REM 1.) Locate the folder where your .bak file resides
REM 2.) Right-click on the folder and select “Properties”
REM 3.) Click the Security tab
REM 4.) In Vista, click the “Edit…” button
REM 5.) Click the “Add…” button
REM 5.) Type NETWORK SERVICE in the text box and click “OK”
REM 6.) Check the Allow “Write” box under Permissions and then “OK”
REM 7.) Click “OK” on the Folder Properties dialog
REM 8.) Start Outlook with BCM
REM 9.) Create a new, blank BCM database by doing the following:
REM 10.) From the main Outlook window, select “Business Contact Manager | Database Tools | Create or Select a Database…”
REM 11.) Select the “Create a new database” option, type a database name, then click “Next”
REM 12.) Write down the name of your blank database, finish the Wizard, close Outlook, and wait for the Outlook process to exit.
REM      Now, modify this script in Notepad by doing the following:
REM 13.) Press CTRL+H in Notepad to open the Replace dialog
REM 14.) Type <BACKED_UP_DATABASE_NAME> in the “Find what” box
REM 15.) In the “Replace with” box, enter the name of the backed up BCM database (without the .bak file extension)
REM      Normally the database name matches the name of the .bak file.
REM      If you are unsure of the database name, use the optional script below from a command prompt.
REM 16.) Click “Replace All” to set the name of the backed up database
REM 17.) Now type <BACKUP_PATH> in the “Find what” box
REM 18.) Type the path to the Backup folder from steps 1 or 2 above
REM 19.) Click “Replace All” to update the folder paths
REM 20.) Now type <BLANK_DATABASE_NAME> in the “Find what” box
REM 21.) In the “Replace with” box, enter the name of the blank BCM database to replace (without the .mdf file extension)
REM 22.) Click “Replace All” to update the destination folder paths
REM 23.) Now type <LOCAL_APP_DATA> in the “Find what” box
REM 24.) In the “Replace with” box, enter one of the following, depending on your operating system:
REM
REM       For Windows Vista:
REM       C:\Users\<USER_NAME>\AppData\Local
REM
REM       For Windows XP:
REM       C:\documents and settings\<USER_NAME>\Local Settings\Application Data
REM
REM 25.) Click “Replace All” to update the folder paths
REM 26.) Now type <USER_NAME> in the “Find what” box
REM 27.) In the “Replace with” box, enter your Windows User Name
REM 28.) Click “Replace All” to update the folder paths
REM 29.) Save this file to your Desktop as RestoreBCM.cmd
REM 30.) Run this script by double-clicking on the RestoreBCM.cmd file on your Desktop
REM
@ECHO WARNING! Restoring this database will overwrite any existing database
@ECHO          named “<BACKED_UP_DATABASE_NAME>”.  Also, this script will
@ECHO          overwrite any data in “<BLANK_DATABASE_NAME>”.  Please be sure that
@ECHO          you have backed up any existing data before continuing.
@ECHO          Close this window or press CTRL+C to abort this operation.


pause
@ECHO Restore started…
REM  If you would like to view the list of files contained in your backup file, use only the first command below:
“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -b -E -S .\MSSMLBIZ -Q “RESTORE FILELISTONLY FROM DISK = ‘<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>.bak'”>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO FileListOnly Returned:%ERRORLEVEL%>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO FileListOnly Returned:%ERRORLEVEL%
IF ERRORLEVEL 1 GOTO END


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -b -E -S .\MSSMLBIZ -Q “sp_detach_db ‘<BLANK_DATABASE_NAME>'”>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Detach DB Returned:%ERRORLEVEL%>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Detach DB Returned:%ERRORLEVEL%
IF ERRORLEVEL 1 SET ERRORLEVEL=0


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -b -E -S .\MSSMLBIZ -Q “RESTORE DATABASE <BACKED_UP_DATABASE_NAME> FROM DISK = ‘<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>.bak’ WITH MOVE ‘<BACKED_UP_DATABASE_NAME>_dat’ TO ‘<LOCAL_APP_DATA>\Microsoft\Business Contact Manager\<BLANK_DATABASE_NAME>.mdf’, MOVE ‘<BACKED_UP_DATABASE_NAME>_log’ TO ‘<LOCAL_APP_DATA>\Microsoft\Business Contact Manager\<BLANK_DATABASE_NAME>.ldf’, REPLACE”>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO RESTORE DATABASE Returned:%ERRORLEVEL%>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO RESTORE DATABASE Returned:%ERRORLEVEL%
IF ERRORLEVEL 1 GOTO END


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


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -b -E -S .\MSSMLBIZ -Q “sp_dboption ‘<BACKED_UP_DATABASE_NAME>’, ‘single user’, ‘TRUE'”>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Single User Mode Returned:%ERRORLEVEL%>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Single User Mode Returned:%ERRORLEVEL%
IF ERRORLEVEL 1 GOTO END


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -b -E -S .\MSSMLBIZ -Q “sp_renamedb ‘<BACKED_UP_DATABASE_NAME>’, ‘<BLANK_DATABASE_NAME>'”>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Rename DB Returned:%ERRORLEVEL%>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Rename DB Returned:%ERRORLEVEL%
IF ERRORLEVEL 1 SET ERRORLEVEL=0


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -b -E -S .\MSSMLBIZ -Q “sp_dboption ‘<BLANK_DATABASE_NAME>’, ‘single user’, ‘FALSE'”>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Multi-User Mode Returned:%ERRORLEVEL%>>”<BACKUP_PATH>\<BACKED_UP_DATABASE_NAME>_RestoreResult.txt”
@ECHO Multi-User Mode Returned:%ERRORLEVEL%
:END


 


Comments (15)

  1. Kay Anderson says:

    I do not have an automatic SQL backup but I do have copy of the .mdf and .idf files.  I had to completely re-install my office 2007 programs and of course lost access to my BCM which I rely on daily.  I was on the phone for 2 hours with a Microsoft tech and he could not help me restore the BCM.  He basically had me create a new database and then copy the old database into the folder, rename it, etc.  It did not work.  When I try the wizard to just select the old files, it puts in a file name for me.  It does not know allow me to select the file name.  Any help would be appreciated.  I am not very technical but willing to try and very desparate.

    Thanks.

  2. Jerry Blake says:

    You will need to attach these database files to SQL server. However, this would require running some SQL commands. Please try following alternative and see it works.

    1. Copy both .mdf and .ldf files in the directory they would usually be. In vista, this would be

    C:Users<username>appdataLocalMicrosoftBusiness Contact Manager.

    1. Uninstall BCM.
    2. Install BCM again. During installation, BCM checks the above mentioned directory. If it finds valid bcm files, it will attach them to SQL server.

    3. When you configure BCM, use advanced option and select an existing database.

    If you don’t see database in the list, then this method probably didn’t work. You will have to manually attach .mdf file to SQL server,then reconfigure BCM with existing database option.

    Hope it helps

    -Mukesh

  3. ngoldwein says:

    Ihave followed your post to create my backup of my BCM database and it worked fine.  I now am trying to restore the database but not successfully.  The backed up database is MSSbusiness and it is on a external hard drive connected through a network and the path is \Desktopext_driv (g)Backup.  The blank database name is Restored_database.

    When I run the restore file I get the following:

    Msg 3201, Level 16, State 2, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    Cannot open backup device ‘\Desktopext_driv (g)BackupMSSbusiness.bak’. Operating system error 2(The system cannot find the file specified.).

    Msg 3013, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    RESTORE FILELIST is terminating abnormally.

    FileListOnly Returned:1

    I was hoping you could tell me how to fix the problem.  Thanks in advance.

  4. ngoldwein says:

    I am still having problems with the restoration of the backed up database and would appreciate your assistance.

    I am backing up the database MSSmallBusiness (located in the default file location on my laptop machine).  

    The backed up file name is MSSmallBusiness.bak and is located on my external hard drive.  

    The hard drive is connected by USB to my desktop machine but the database I am backing up is on my laptop machine which is connected to the desktop machine by a wireless network.

    The path for the backed of file is \Desktopext_driv (g)Backup.  The blank database I created in Outlook 2007 is called Restored_database.

    The file RestoreBCM.cmd is below

    @ECHO OFF

    REM Restore a BCM database from a SQL Database Backup (.bak file)

    REM This script will restore a SQL backup (.bak) file over a new, blank BCM database

    REM ——————————————————————————–

    REM Instructions

    REM 1.) Locate the folder where your .bak file resides

    REM 2.) Right-click on the folder and select "Properties"

    REM 3.) Click the Security tab

    REM 4.) In Vista, click the "Edit…" button

    REM 5.) Click the "Add…" button

    REM 5.) Type NETWORK SERVICE in the text box and click "OK"

    REM 6.) Check the Allow "Write" box under Permissions and then "OK"

    REM 7.) Click "OK" on the Folder Properties dialog

    REM 9.) Start Outlook with BCM

    REM 10.) Create a new, blank BCM database by doing the following:

    REM 11.) From the main Outlook window, select "Business Contact Manager | Database Tools | Create or Select a Database…"

    REM 12.) Select the "Create a new database" option, type a database name, then click "Next"

    REM 13.) Write down the name of your blank database, finish the Wizard, close Outlook, and wait for the Outlook process to exit.

    REM      Now, modify this script in Notepad by doing the following:

    REM 15.) Press CTRL+H in Notepad to open the Replace dialog

    REM 16.) Type MSSmallBusiness in the "Find what" box

    REM 17.) In the "Replace with" box, enter the name of the backed up BCM database (without the .bak file extension)

    REM      Normally the database name matches the name of the .bak file.

    REM      If you are unsure of the database name, use the optional script below from a command prompt.

    REM 18.) Click "Replace All" to set the name of the backed up database

    REM 22.) Now type \Desktopext_driv (g)Backup in the "Find what" box

    REM 23.) Type the path to the Backup folder from steps 1 or 2 above

    REM 24.) Click "Replace All" to update the folder paths

    REM 19.) Now type Restored_database in the "Find what" box

    REM 20.) In the "Replace with" box, enter the name of the blank BCM database to replace (without the .mdf file extension)

    REM 21.) Click "Replace All" to update the destination folder paths

    REM 14.) Save this file to your Desktop as RestoreBCM.cmd

    REM 25.) Run this script by double-clicking on the RestoreBCM.cmd file on your Desktop

    REM

    @ECHO WARNING! Restoring this database will overwrite any existing database

    @ECHO          named "MSSmallBusiness".  Also, this script will

    @ECHO          overwrite any data in "Restored_database".  Please be sure that

    @ECHO          you have backed up any existing data before continuing.

    @ECHO          Close this window or press CTRL+C to abort this operation.

    pause

    @ECHO Restore started…

    REM  If you would like to view the list of files contained in your backup file, use only the first command below:

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "RESTORE FILELISTONLY FROM DISK = ‘\Desktopext_driv (g)BackupMSSmallBusiness.bak’">"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO FileListOnly Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO FileListOnly Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_detach_db ‘Restored_database’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Detach DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Detach DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "RESTORE DATABASE MSSmallBusiness FROM DISK = ‘\Desktopext_driv (g)BackupMSSmallBusiness.bak’ WITH MOVE ‘MSSmallBusiness_dat’ TO ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.mdf’, MOVE ‘MSSmallBusiness_log’ TO ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.ldf’, REPLACE">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO RESTORE DATABASE Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO RESTORE DATABASE Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "EXEC sp_attach_db @dbname = ‘Restored_database’, @filename1 = ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.mdf’, @filename2 = ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.ldf’;">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Attach DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Attach DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 SET ERRORLEVEL=0

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_dboption ‘MSSmallBusiness’, ‘single user’, ‘TRUE’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Single User Mode Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Single User Mode Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_renamedb ‘MSSmallBusiness’, ‘Restored_database’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Rename DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Rename DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_dboption ‘Restored_database’, ‘single user’, ‘FALSE’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Multi-User Mode Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Multi-User Mode Returned:%ERRORLEVEL%

    :END

    The file with the results of the restore is below

    LogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent


    MSBusinessContactManager4_dat                                                                                                    C:Documents and SettingsNeilLocal SettingsApplication DataMicrosoftBusiness Contact ManagerMSSmallBusiness.MDF                                                                                                                                                D    PRIMARY                                                                                                                                      55377920       35184372080640                    1                           0                           0 00000000-0000-0000-0000-000000000000                           0                           0             55050240             512           1 NULL                                          194000001144000037 A32CD167-1CCB-4C3D-A8CD-51EC0091BC42          0         1

    MSBusinessContactManager4_log                                                                                                    C:Documents and SettingsNeilLocal SettingsApplication DataMicrosoftBusiness Contact ManagerMSSmallBusiness._log.LDF                                                                                                                                           L    NULL                                                                                                                                         79626240       35184372080640                    2                           0                           0 00000000-0000-0000-0000-000000000000                           0                           0                    0             512           0 NULL                                                           0 00000000-0000-0000-0000-000000000000          0         1

    (2 rows affected)

    FileListOnly Returned:0

    Detach DB Returned:0

    Msg 3234, Level 16, State 2, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    Logical file ‘MSSmallBusiness_dat’ is not part of database ‘MSSmallBusiness’. Use RESTORE FILELISTONLY to list the logical file names.

    Msg 3013, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    RESTORE DATABASE is terminating abnormally.

    RESTORE DATABASE Returned:1

    Ifyou could help me I would appreciate your advice.  Thanks!

  5. ngoldwein says:

    Thanks for the information but it still does not work.  My RestoreBCM.cmd is now as follows:

    @ECHO OFF

    REM Restore a BCM database from a SQL Database Backup (.bak file)

    REM This script will restore a SQL backup (.bak) file over a new, blank BCM database

    REM ——————————————————————————–

    REM Instructions

    REM 1.) Locate the folder where your .bak file resides

    REM 2.) Right-click on the folder and select "Properties"

    REM 3.) Click the Security tab

    REM 4.) In Vista, click the "Edit…" button

    REM 5.) Click the "Add…" button

    REM 5.) Type NETWORK SERVICE in the text box and click "OK"

    REM 6.) Check the Allow "Write" box under Permissions and then "OK"

    REM 7.) Click "OK" on the Folder Properties dialog

    REM 9.) Start Outlook with BCM

    REM 10.) Create a new, blank BCM database by doing the following:

    REM 11.) From the main Outlook window, select "Business Contact Manager | Database Tools | Create or Select a Database…"

    REM 12.) Select the "Create a new database" option, type a database name, then click "Next"

    REM 13.) Write down the name of your blank database, finish the Wizard, close Outlook, and wait for the Outlook process to exit.

    REM      Now, modify this script in Notepad by doing the following:

    REM 15.) Press CTRL+H in Notepad to open the Replace dialog

    REM 16.) Type MSSmallBusiness in the "Find what" box

    REM 17.) In the "Replace with" box, enter the name of the backed up BCM database (without the .bak file extension)

    REM      Normally the database name matches the name of the .bak file.

    REM      If you are unsure of the database name, use the optional script below from a command prompt.

    REM 18.) Click "Replace All" to set the name of the backed up database

    REM 22.) Now type \Desktopext_driv (g)Backup in the "Find what" box

    REM 23.) Type the path to the Backup folder from steps 1 or 2 above

    REM 24.) Click "Replace All" to update the folder paths

    REM 19.) Now type Restored_database in the "Find what" box

    REM 20.) In the "Replace with" box, enter the name of the blank BCM database to replace (without the .mdf file extension)

    REM 21.) Click "Replace All" to update the destination folder paths

    REM 14.) Save this file to your Desktop as RestoreBCM.cmd

    REM 25.) Run this script by double-clicking on the RestoreBCM.cmd file on your Desktop

    REM

    @ECHO WARNING! Restoring this database will overwrite any existing database

    @ECHO          named "MSSmallBusiness".  Also, this script will

    @ECHO          overwrite any data in "Restored_database".  Please be sure that

    @ECHO          you have backed up any existing data before continuing.

    @ECHO          Close this window or press CTRL+C to abort this operation.

    pause

    @ECHO Restore started…

    REM  If you would like to view the list of files contained in your backup file, use only the first command below:

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "RESTORE FILELISTONLY FROM DISK = ‘\Desktopext_driv (g)BackupMSSmallBusiness.bak’">"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO FileListOnly Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO FileListOnly Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_detach_db ‘Restored_database’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Detach DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Detach DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "RESTORE DATABASE MSSmallBusiness FROM DISK = ‘\Desktopext_driv (g)BackupMSSmallBusiness.bak’ WITH MOVE ‘MSBusinessContactManager4_dat’ TO ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.mdf’, MOVE ‘MSBusinessContactManager4_log’ TO ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.ldf’, REPLACE">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO RESTORE DATABASE Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO RESTORE DATABASE Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "EXEC sp_attach_db @dbname = ‘Restored_database’, @filename1 = ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.mdf’, @filename2 = ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.ldf’;">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Attach DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Attach DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 SET ERRORLEVEL=0

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_dboption ‘MSSmallBusiness’, ‘single user’, ‘TRUE’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Single User Mode Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Single User Mode Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_renamedb ‘MSSmallBusiness’, ‘Restored_database’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Rename DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Rename DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_dboption ‘Restored_database’, ‘single user’, ‘FALSE’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Multi-User Mode Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Multi-User Mode Returned:%ERRORLEVEL%

    :END

    The new result file is as follows:

    LogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent


    MSBusinessContactManager4_dat                                                                                                    C:Documents and SettingsNeilLocal SettingsApplication DataMicrosoftBusiness Contact ManagerMSSmallBusiness.MDF                                                                                                                                                D    PRIMARY                                                                                                                                      55377920       35184372080640                    1                           0                           0 00000000-0000-0000-0000-000000000000                           0                           0             55050240             512           1 NULL                                          194000001144000037 A32CD167-1CCB-4C3D-A8CD-51EC0091BC42          0         1

    MSBusinessContactManager4_log                                                                                                    C:Documents and SettingsNeilLocal SettingsApplication DataMicrosoftBusiness Contact ManagerMSSmallBusiness._log.LDF                                                                                                                                           L    NULL                                                                                                                                         79626240       35184372080640                    2                           0                           0 00000000-0000-0000-0000-000000000000                           0                           0                    0             512           0 NULL                                                           0 00000000-0000-0000-0000-000000000000          0         1

    (2 rows affected)

    FileListOnly Returned:0

    Msg 15010, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Procedure sp_detach_db, Line 34

    The database ‘Restored_database’ does not exist. Supply a valid database name. To see available databases, use sys.databases.

    Detach DB Returned:1

    Once again I would appreciate your assistance.

    PS:  How come my previous post does not appear in this string of posts?

  6. ngoldwein says:

    I made the one change that you suggested:

    IF ERRORLEVEL 1 SET ERRORLEVEL=0

    below is the latest version of RestoreBCM.cmd

    @ECHO OFF

    REM Restore a BCM database from a SQL Database Backup (.bak file)

    REM This script will restore a SQL backup (.bak) file over a new, blank BCM database

    REM ——————————————————————————–

    REM Instructions

    REM 1.) Locate the folder where your .bak file resides

    REM 2.) Right-click on the folder and select "Properties"

    REM 3.) Click the Security tab

    REM 4.) In Vista, click the "Edit…" button

    REM 5.) Click the "Add…" button

    REM 5.) Type NETWORK SERVICE in the text box and click "OK"

    REM 6.) Check the Allow "Write" box under Permissions and then "OK"

    REM 7.) Click "OK" on the Folder Properties dialog

    REM 9.) Start Outlook with BCM

    REM 10.) Create a new, blank BCM database by doing the following:

    REM 11.) From the main Outlook window, select "Business Contact Manager | Database Tools | Create or Select a Database…"

    REM 12.) Select the "Create a new database" option, type a database name, then click "Next"

    REM 13.) Write down the name of your blank database, finish the Wizard, close Outlook, and wait for the Outlook process to exit.

    REM      Now, modify this script in Notepad by doing the following:

    REM 15.) Press CTRL+H in Notepad to open the Replace dialog

    REM 16.) Type MSSmallBusiness in the "Find what" box

    REM 17.) In the "Replace with" box, enter the name of the backed up BCM database (without the .bak file extension)

    REM      Normally the database name matches the name of the .bak file.

    REM      If you are unsure of the database name, use the optional script below from a command prompt.

    REM 18.) Click "Replace All" to set the name of the backed up database

    REM 22.) Now type \Desktopext_driv (g)Backup in the "Find what" box

    REM 23.) Type the path to the Backup folder from steps 1 or 2 above

    REM 24.) Click "Replace All" to update the folder paths

    REM 19.) Now type Restored_database in the "Find what" box

    REM 20.) In the "Replace with" box, enter the name of the blank BCM database to replace (without the .mdf file extension)

    REM 21.) Click "Replace All" to update the destination folder paths

    REM 14.) Save this file to your Desktop as RestoreBCM.cmd

    REM 25.) Run this script by double-clicking on the RestoreBCM.cmd file on your Desktop

    REM

    @ECHO WARNING! Restoring this database will overwrite any existing database

    @ECHO          named "MSSmallBusiness".  Also, this script will

    @ECHO          overwrite any data in "Restored_database".  Please be sure that

    @ECHO          you have backed up any existing data before continuing.

    @ECHO          Close this window or press CTRL+C to abort this operation.

    pause

    @ECHO Restore started…

    REM  If you would like to view the list of files contained in your backup file, use only the first command below:

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "RESTORE FILELISTONLY FROM DISK = ‘\Desktopext_driv (g)BackupMSSmallBusiness.bak’">"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO FileListOnly Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO FileListOnly Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_detach_db ‘Restored_database’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Detach DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Detach DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 SET ERRORLEVEL=0

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "RESTORE DATABASE MSSmallBusiness FROM DISK = ‘\Desktopext_driv (g)BackupMSSmallBusiness.bak’ WITH MOVE ‘MSBusinessContactManager4_dat’ TO ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.mdf’, MOVE ‘MSBusinessContactManager4_log’ TO ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.ldf’, REPLACE">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO RESTORE DATABASE Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO RESTORE DATABASE Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "EXEC sp_attach_db @dbname = ‘Restored_database’, @filename1 = ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.mdf’, @filename2 = ‘%LOCALAPPDATA%MicrosoftBusiness Contact ManagerRestored_database.ldf’;">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Attach DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Attach DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 SET ERRORLEVEL=0

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_dboption ‘MSSmallBusiness’, ‘single user’, ‘TRUE’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Single User Mode Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Single User Mode Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_renamedb ‘MSSmallBusiness’, ‘Restored_database’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Rename DB Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Rename DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_dboption ‘Restored_database’, ‘single user’, ‘FALSE’">>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Multi-User Mode Returned:%ERRORLEVEL%>>"\Desktopext_driv (g)BackupMSSmallBusiness_RestoreResult.txt"

    @ECHO Multi-User Mode Returned:%ERRORLEVEL%

    :END

    There are still errors and I have listed below the text from the file MSSmallBusiness_RestoreResults.txt

    LogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent


    MSBusinessContactManager4_dat                                                                                                    C:Documents and SettingsNeilLocal SettingsApplication DataMicrosoftBusiness Contact ManagerMSSmallBusiness.MDF                                                                                                                                                D    PRIMARY                                                                                                                                      55377920       35184372080640                    1                           0                           0 00000000-0000-0000-0000-000000000000                           0                           0             55050240             512           1 NULL                                          194000001144000037 A32CD167-1CCB-4C3D-A8CD-51EC0091BC42          0         1

    MSBusinessContactManager4_log                                                                                                    C:Documents and SettingsNeilLocal SettingsApplication DataMicrosoftBusiness Contact ManagerMSSmallBusiness._log.LDF                                                                                                                                           L    NULL                                                                                                                                         79626240       35184372080640                    2                           0                           0 00000000-0000-0000-0000-000000000000                           0                           0                    0             512           0 NULL                                                           0 00000000-0000-0000-0000-000000000000          0         1

    (2 rows affected)

    FileListOnly Returned:0

    Msg 15010, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Procedure sp_detach_db, Line 34

    The database ‘Restored_database’ does not exist. Supply a valid database name. To see available databases, use sys.databases.

    Detach DB Returned:1

    Msg 5105, Level 16, State 2, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    A file activation error occurred. The physical file name ‘MicrosoftBusiness Contact ManagerRestored_database.mdf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.

    Msg 3156, Level 16, State 3, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    File ‘MSBusinessContactManager4_dat’ cannot be restored to ‘MicrosoftBusiness Contact ManagerRestored_database.mdf’. Use WITH MOVE to identify a valid location for the file.

    Msg 5105, Level 16, State 2, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    A file activation error occurred. The physical file name ‘MicrosoftBusiness Contact ManagerRestored_database.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.

    Msg 3156, Level 16, State 3, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    File ‘MSBusinessContactManager4_log’ cannot be restored to ‘MicrosoftBusiness Contact ManagerRestored_database.ldf’. Use WITH MOVE to identify a valid location for the file.

    Msg 3119, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    Problems were identified while planning for the RESTORE statement. Previous messages provide details.

    Msg 3013, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Line 1

    RESTORE DATABASE is terminating abnormally.

    RESTORE DATABASE Returned:0

    I have checked and my working database (MSSmallBusiness) and the empty database (Restored_database) are both in their default location – C:Documents and SettingsNeilLocal SettingsApplication DataMicrosoftBusiness Contact Manager

    Once again, I would appreciate your help.

  7. Jerry Blake says:

    I have updated the script with a few improvements.  Please let me know if you encounter any errors.

    ~ Clinton Ford

  8. eric@m2architects.com says:

    This is far too difficult.  Should you not supply an add on.  This is the perfect example of why too many are frustrated by this product.  I’m an old programmer but don’t have the time to wade through your data.  Please help with a better solution.

  9. JRA says:

    I have execute an automatic backup to run, but when im trying a restore I’m getting an error can you please advise me on where I’m going wrong and how to rectify this problem thanks in advance. Outlook is closed as well.

    SCRIPT USED

    @ECHO WARNING! Restoring this database will overwrite any existing database

    @ECHO          named "JA_Company_Main_080407".  Also, this script will

    @ECHO          overwrite any data in "fake4".  Please be sure that

    @ECHO          you have backed up any existing data before continuing.

    @ECHO          Close this window or press CTRL+C to abort this operation.

    pause

    @ECHO Restore started…

    REM  If you would like to view the list of files contained in your backup file, use only the first command below:

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "RESTORE FILELISTONLY FROM

    DISK = ‘C:backupJA_Company_Main_080407.bak’">"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO FileListOnly Returned:%ERRORLEVEL%>>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO FileListOnly Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_detach_db

    ‘fake4’">>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Detach DB Returned:%ERRORLEVEL%>>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Detach DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 SET ERRORLEVEL=0

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "RESTORE DATABASE

    JA_Company_Main_080407 FROM DISK = ‘C:backupJA_Company_Main_080407.bak’ WITH MOVE ‘JA_Company_Main_080407_dat’ TO

    ‘C:Documents and SettingsJason.AlvaresLocal SettingsApplication DataMicrosoftBusiness Contact

    Managerfake4.mdf’, MOVE ‘JA_Company_Main_080407_log’ TO ‘C:Documents and SettingsJason.AlvaresLocal

    SettingsApplication DataMicrosoftBusiness Contact Managerfake4.ldf’,

    REPLACE">>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO RESTORE DATABASE Returned:%ERRORLEVEL%>>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO RESTORE DATABASE Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "EXEC sp_attach_db @dbname =

    ‘fake4’, @filename1 = ‘C:Documents and SettingsJason.AlvaresLocal SettingsApplication DataMicrosoftBusiness

    Contact Managerfake4.mdf’, @filename2 = ‘C:Documents and SettingsJason.AlvaresLocal SettingsApplication

    DataMicrosoftBusiness Contact Managerfake4.ldf’;">>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Attach DB Returned:%ERRORLEVEL%>>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Attach DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 SET ERRORLEVEL=0

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_dboption

    ‘JA_Company_Main_080407’, ‘single user’, ‘TRUE’">>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Single User Mode Returned:%ERRORLEVEL%>>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Single User Mode Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 GOTO END

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_renamedb

    ‘JA_Company_Main_080407’, ‘fake4’">>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Rename DB Returned:%ERRORLEVEL%>>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Rename DB Returned:%ERRORLEVEL%

    IF ERRORLEVEL 1 SET ERRORLEVEL=0

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -b -E -S .MSSMLBIZ -Q "sp_dboption ‘fake4’,

    ‘single user’, ‘FALSE’">>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Multi-User Mode Returned:%ERRORLEVEL%>>"C:backupJA_Company_Main_080407_RestoreResult.txt"

    @ECHO Multi-User Mode Returned:%ERRORLEVEL%

    :END

    RESTORE LOG

    LogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent


    JA_Company_Main_080407_dat                                                                                                       C:Documents and SettingsJason.AlvaresLocal SettingsApplication DataMicrosoftBusiness Contact ManagerJA_Company_Main_080407.mdf                                                                                                                                D    PRIMARY                                                                                                                                      26214400       35184372080640                    1                           0                           0 1A89EE1E-F59C-441F-BB27-3D10FE5F7CF6                           0                           0             11468800             512           1 NULL                                           28000000110900037 BED7BA10-AFD5-42E9-B1EF-543CE7F8962E          0         1

    JA_Company_Main_080407_log                                                                                                       C:Documents and SettingsJason.AlvaresLocal SettingsApplication DataMicrosoftBusiness Contact ManagerJA_Company_Main_080407.ldf                                                                                                                                L    NULL                                                                                                                                         10485760        2199023255552                    2                           0                           0 3438AFAD-47CC-4D21-8B6A-2EFE6962FFDF                           0                           0                    0             512           0 NULL                                                           0 00000000-0000-0000-0000-000000000000          0         1

    (2 rows affected)

    FileListOnly Returned:0

    Msg 15010, Level 16, State 1, Server LONLAP03MSSMLBIZ, Procedure sp_detach_db, Line 34

    The database ‘fake4’ does not exist. Supply a valid database name. To see available databases, use sys.databases.

    Detach DB Returned:1

    Msg 3101, Level 16, State 1, Server LONLAP03MSSMLBIZ, Line 1

    Exclusive access could not be obtained because the database is in use.

    Msg 3013, Level 16, State 1, Server LONLAP03MSSMLBIZ, Line 1

    RESTORE DATABASE is terminating abnormally.

    RESTORE DATABASE Returned:0

  10. I seem to lose data as BCM crashes at least quarterly.  I’ve gotten real good at using the SQL Server MAnager Studio to reattach the database however I am now having one problem.

    It tells me it cannot find the .ldf when it is sitting right there.  Can the ldf be rebuilt to coincide with the mdf.  the .mdf is obviously the 1Gb file I need

  11. Jerry Blake says:

    Hi

    Here’s what you can do to reattach the database: rename your .ldf file to something different from the .mdf file name or move it to some other location.  In SQL Server Management Studio, click on ‘New Query’ and run the following command:

    use master

    EXEC sp_attach_db

    @dbname = ‘<YOUR_DATABASE_NAME>’,–example, ‘MSSmallBusiness’

    @filename1 = ‘<LOCATION_OF_YOUR_MDF_FILE>’ –example, ‘C:Users<USER_NAME>appdataLocalMicrosoftBusiness Contact ManagerMSSmallBusiness.mdf’.

    This command should run successfully and your DB will be reattached.  SQL server will create a new .ldf file for your DB.

    Can you also answer these questions for us:

    -What happens when you say BCM crashes?  Do you see any message/s?

    -Do you manually detach the DB after the crash?  If so, does the detach happen with any errors?

    -What is the error message number that you get when reattaching fails?

    -Can you send us the SQL error logs from ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG’?  And BCM logs from ‘C:Users<USER_NAME>appdataLocalMicrosoftBusiness Contact ManagerLogs’ to bcmteam_at_ microsoft.com ( please remove at with @) ?

  12. aromaboy says:

    Will this work with OSQL as well? As I have bcm 2003 xp and only have osql.

  13. craigsuch says:

    I am glad I found this post sicen I lost access to a BCM database when my PC crashed. I was able to recover the mdf and ldf files and create a bak file but I am getting an error running the script:

    (2 rows affected)

    FileListOnly Returned:0

    Msg 229, Level 14, State 5, Server MYPCNAMEMSSMLBIZ, Procedure sp_detach_db, Line 1

    The EXECUTE permission was denied on the object ‘sp_detach_db’, database ‘mssqlsystemresource’, schema ‘sys’.

    Detach DB Returned:1

    Msg 1834, Level 16, State 1, Server MYPCNAMEMSSMLBIZ, Line 1

    The file ‘C:UsersusernameAppDataLocalMicrosoftBusiness Contact ManagerNewBCMdb.mdf’ cannot be overwritten.  It is being used by database ‘NewBCMdb’.

    Msg 3156, Level 16, State 4, Server MYPCNAMEMSSMLBIZ, Line 1

    File ‘MBA_dat’ cannot be restored to ‘C:UsersusernameAppDataLocalMicrosoftBusiness Contact ManagerNewBCMdb.mdf’. Use WITH MOVE to identify a valid location for the file.

    Msg 1834, Level 16, State 1, Server MYPCNAMEMSSMLBIZ, Line 1

    The file ‘C:UsersusernameAppDataLocalMicrosoftBusiness Contact ManagerNewBCMdb.ldf’ cannot be overwritten.  It is being used by database ‘NewBCMdb’.

    Msg 3156, Level 16, State 4, Server MYPCNAMEMSSMLBIZ, Line 1

    File ‘MBA_log’ cannot be restored to ‘C:UsersusernameAppDataLocalMicrosoftBusiness Contact ManagerNewBCMdb.ldf’. Use WITH MOVE to identify a valid location for the file.

    Msg 3119, Level 16, State 1, Server MYPCNAMEMSSMLBIZ, Line 1

    Problems were identified while planning for the RESTORE statement. Previous messages provide details.

    Msg 3013, Level 16, State 1, Server MYPCNAMEMSSMLBIZ, Line 1

    RESTORE DATABASE is terminating abnormally.

    RESTORE DATABASE Returned:0

    Can you help me to fix this?

  14. Liz says:

    Hello,

    I followed the instruction, but if the .cmd run, I will get only the information "FilelistOnly Returned: 3"

    Thanks from Germany

  15. Dear Jerry,

    First of all, thank you very much for your articles.

    I tried the "Automate BCM Database Backups" method you propossed a long time ago, and it works quite fine.

    Now I was trying to restore the database to a shared database I have at a Windows2008 R2, but I really do not know how to do it from the method you explain in this article.

    Is it not possible to restore it overwriting the same database at the server?

    Thanks in advance.

    Rubén

Skip to main content