Freigeben über

Restoring an Automatic SQL Backup

  • Artikel

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

  • Anonymous
    October 22, 2007
    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.

  • Anonymous
    October 22, 2007
    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.Copy both .mdf and .ldf files in the directory they would usually be. In vista, this would be C:Users<username>appdataLocalMicrosoftBusiness Contact Manager.Uninstall BCM.Install BCM again. During installation, BCM checks the above mentioned directory. If it finds valid bcm files, it will attach them to SQL server. 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

  • Anonymous
    October 29, 2007
    The comment has been removed

  • Anonymous
    October 30, 2007
    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 OFFREM 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 databaseREM --------------------------------------------------------------------------------REM InstructionsREM 1.) Locate the folder where your .bak file residesREM 2.) Right-click on the folder and select "Properties"REM 3.) Click the Security tabREM 4.) In Vista, click the "Edit..." buttonREM 5.) Click the "Add..." buttonREM 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 dialogREM 9.) Start Outlook with BCMREM 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 dialogREM 16.) Type MSSmallBusiness in the "Find what" boxREM 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 databaseREM 22.) Now type \Desktopext_driv (g)Backup in the "Find what" boxREM 23.) Type the path to the Backup folder from steps 1 or 2 aboveREM 24.) Click "Replace All" to update the folder pathsREM 19.) Now type Restored_database in the "Find what" boxREM 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 pathsREM 14.) Save this file to your Desktop as RestoreBCM.cmdREM 25.) Run this script by double-clicking on the RestoreBCM.cmd file on your DesktopREM@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%:ENDThe file with the results of the restore is belowLogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresentMSBusinessContactManager4_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         1MSBusinessContactManager4_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:0Detach DB Returned:0Msg 3234, Level 16, State 2, Server NEIL-LAPTOPMSSMLBIZ, Line 1Logical 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 1RESTORE DATABASE is terminating abnormally.RESTORE DATABASE Returned:1Ifyou could help me I would appreciate your advice.  Thanks!

  • Anonymous
    October 30, 2007
    Thanks for the information but it still does not work.  My RestoreBCM.cmd is now as follows:@ECHO OFFREM 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 databaseREM --------------------------------------------------------------------------------REM InstructionsREM 1.) Locate the folder where your .bak file residesREM 2.) Right-click on the folder and select "Properties"REM 3.) Click the Security tabREM 4.) In Vista, click the "Edit..." buttonREM 5.) Click the "Add..." buttonREM 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 dialogREM 9.) Start Outlook with BCMREM 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 dialogREM 16.) Type MSSmallBusiness in the "Find what" boxREM 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 databaseREM 22.) Now type \Desktopext_driv (g)Backup in the "Find what" boxREM 23.) Type the path to the Backup folder from steps 1 or 2 aboveREM 24.) Click "Replace All" to update the folder pathsREM 19.) Now type Restored_database in the "Find what" boxREM 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 pathsREM 14.) Save this file to your Desktop as RestoreBCM.cmdREM 25.) Run this script by double-clicking on the RestoreBCM.cmd file on your DesktopREM@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%:ENDThe 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 IsPresentMSBusinessContactManager4_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         1MSBusinessContactManager4_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:0Msg 15010, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Procedure sp_detach_db, Line 34The database 'Restored_database' does not exist. Supply a valid database name. To see available databases, use sys.databases.Detach DB Returned:1Once again I would appreciate your assistance.PS:  How come my previous post does not appear in this string of posts?

  • Anonymous
    October 31, 2007
    I made the one change that you suggested:IF ERRORLEVEL 1 SET ERRORLEVEL=0below is the latest version of RestoreBCM.cmd@ECHO OFFREM 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 databaseREM --------------------------------------------------------------------------------REM InstructionsREM 1.) Locate the folder where your .bak file residesREM 2.) Right-click on the folder and select "Properties"REM 3.) Click the Security tabREM 4.) In Vista, click the "Edit..." buttonREM 5.) Click the "Add..." buttonREM 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 dialogREM 9.) Start Outlook with BCMREM 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 dialogREM 16.) Type MSSmallBusiness in the "Find what" boxREM 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 databaseREM 22.) Now type \Desktopext_driv (g)Backup in the "Find what" boxREM 23.) Type the path to the Backup folder from steps 1 or 2 aboveREM 24.) Click "Replace All" to update the folder pathsREM 19.) Now type Restored_database in the "Find what" boxREM 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 pathsREM 14.) Save this file to your Desktop as RestoreBCM.cmdREM 25.) Run this script by double-clicking on the RestoreBCM.cmd file on your DesktopREM@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%:ENDThere are still errors and I have listed below the text from the file MSSmallBusiness_RestoreResults.txtLogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresentMSBusinessContactManager4_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         1MSBusinessContactManager4_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:0Msg 15010, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Procedure sp_detach_db, Line 34The database 'Restored_database' does not exist. Supply a valid database name. To see available databases, use sys.databases.Detach DB Returned:1Msg 5105, Level 16, State 2, Server NEIL-LAPTOPMSSMLBIZ, Line 1A 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 1File '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 1A 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 1File '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 1Problems were identified while planning for the RESTORE statement. Previous messages provide details.Msg 3013, Level 16, State 1, Server NEIL-LAPTOPMSSMLBIZ, Line 1RESTORE DATABASE is terminating abnormally.RESTORE DATABASE Returned:0I 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 ManagerOnce again, I would appreciate your help.

  • Anonymous
    November 02, 2007
    I have updated the script with a few improvements.  Please let me know if you encounter any errors.~ Clinton Ford

  • Anonymous
    January 11, 2008
    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.

  • Anonymous
    April 21, 2008
    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 FROMDISK = '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 DATABASEJA_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 ContactManagerfake4.mdf', MOVE 'JA_Company_Main_080407_log' TO 'C:Documents and SettingsJason.AlvaresLocalSettingsApplication 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 DataMicrosoftBusinessContact Managerfake4.mdf', @filename2 = 'C:Documents and SettingsJason.AlvaresLocal SettingsApplicationDataMicrosoftBusiness 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%:ENDRESTORE LOGLogicalName                                                                                                                      PhysicalName                                                                                                                                                                                                                                                         Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresentJA_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         1JA_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:0Msg 15010, Level 16, State 1, Server LONLAP03MSSMLBIZ, Procedure sp_detach_db, Line 34The database 'fake4' does not exist. Supply a valid database name. To see available databases, use sys.databases.Detach DB Returned:1Msg 3101, Level 16, State 1, Server LONLAP03MSSMLBIZ, Line 1Exclusive access could not be obtained because the database is in use.Msg 3013, Level 16, State 1, Server LONLAP03MSSMLBIZ, Line 1RESTORE DATABASE is terminating abnormally.RESTORE DATABASE Returned:0

  • Anonymous
    June 02, 2008
    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

  • Anonymous
    June 05, 2008
    HiHere’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 masterEXEC 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 @) ?

  • Anonymous
    August 21, 2008
    Will this work with OSQL as well? As I have bcm 2003 xp and only have osql.

  • Anonymous
    June 30, 2009
    The comment has been removed

  • Anonymous
    August 28, 2012
    Hello, I followed the instruction, but if the .cmd run, I will get only the information "FilelistOnly Returned: 3" Thanks from Germany

  • Anonymous
    March 31, 2013
    The comment has been removed