Automate BCM Database Backups


Backing up your business data on a regular basis is critical.  Below are instructions to help you schedule regular automatic SQL backups on Windows Vista and XP (scroll down to see the instructions for Windows XP).


It is recommended that you create your backup folder on a secondary hard drive such as an external USB hard drive.  Just copy and paste the text below into Notepad, then follow the instructions.  If you would like to store your backup on a network drive, follow the additional instructions to copy the SQL backup file to a network location.


REM   Automatic Scheduled Backup (Vista)
REM   ———————————————————————–
REM   Instructions
REM   1.) Create a new folder on a local drive named Backup
REM   2.) Right-click on the folder and select “Properties”
REM   3.) Click the Security tab and then click “Edit…”
REM   4.) 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.) Press CTRL+H to open Notepad’s Replace dialog
REM   9.) Type <BACKUP_PATH> in the “Find what” box
REM   10.) Type the path to the Backup folder you created in step 1 above
REM   11.) Click “Replace All” to update the folder paths
REM   12.) Repeat the above steps to replace <DATABASE_NAME> with your BCM database name, without the .mdf file extension
REM        You can check your database name from the main Outlook window:
REM        “Business Contact Manager | Database Tools | Manage Database”
REM   13.) Click “File | Save As” to save as Backup.cmd in the Backup folder
REM   14.) Click the Windows Start button, then select “Control Panel”
REM   15.) Click “System and Maintenance”, then “Administrative Tools”
REM   16.) Double-click on “Task Scheduler”
REM   17.) Click the “Create Task…” link in the Actions window
REM   18.) Type a descriptive task name (e.g. Backup BCM Database)
REM   19.) Select the “Run whether user is logged on or not” option
REM   20.) Check the “Do not store password” box (unless you want to copy the backup to a network share)
REM   21.) Click the “Triggers” tab and click “New…”
REM   22.) Set your backup schedule and click “OK”
REM   23.) Click the “Actions” tab and click “New…”
REM   24.) Click the “Browse…” button and locate the current file:
REM        <BACKUP_PATH>\Backup.cmd
REM   25.) Click “OK” on the “New Action” and “Create Task” dialogs
REM   26.) Optional: If you would like to store your backup on a network drive, remove REM from the last line below and replace <NETWORK_PATH> with a network location
REM                  You will also need to uncheck the “Do not store password” box in step 20 above
REM                  Also, create an Outlook reminder to update your password for this task each time you change your Windows account password.
REM  
REM   To test your scheduled task in Vista:
REM   ———————————————————————–
REM   1.) click on the “Task Scheduler Library”
REM   2.) Then right-click on your task and select “Run”
REM   3.) Two new files will soon appear in your Backup folder:
REM        <DATABASE_NAME>.bak
REM        <DATABASE_NAME>_BackupResult.txt
REM   4.) Open the <DATABASE_NAME>_BackupResult.txt file to see the results.
REM
REM   ———————————————————————–
REM   Automatic Scheduled Backup (Windows XP)
REM   ———————————————————————–
REM   Instructions
REM   1.) Create a new folder on a local drive named Backup
REM   2.) Right-click on the folder and select “Properties”
REM   3.) Click the Security tab
REM   4.) 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
REM   7.) Click “OK” on the Folder Properties dialog
REM   8.) Press CTRL+H to open Notepad’s Replace dialog
REM   9.) Type <BACKUP_PATH> in the “Find what” box
REM   10.) Type the path to the Backup folder you created in step 1 above
REM   11.) Click “Replace All” to update the folder paths
REM   12.) Repeat the above steps to replace <DATABASE_NAME> with your BCM database name, without the .mdf file extension
REM        You can check your database name from the main Outlook window:
REM        “Business Contact Manager | Database Tools | Manage Database”
REM   13.) Click “File | Save As” to save this file as Backup.cmd in the Backup folder
REM   14.) Click the Windows Start button, then select “Control Panel”
REM   15.) Click “Performance and Maintenance”, then select “Scheduled Tasks”
REM   16.) Double-click on the “Add Scheduled Task” link
REM   17.) Click “Next” in the Wizard
REM   18.) Click the “Browse…” button and locate the current file:
REM        <BACKUP_PATH>\Backup.cmd
REM   19.) Type a descriptive task name (e.g. Backup BCM Database)
REM   20.) Set your backup schedule and click “Next”
REM   21.) Enter your Windows user name and password, then click “Next”
REM   22.) Click “Finish”
REM   23.) The scheduled task will not run if your password changes.  Be sure to add a reminder to your Outlook calendar
REM        to ensure that this scheduled task contains your current password if it changes (e.g. every 90 days)
REM   24.) Optional: If you would like to store your backup on a network drive, remove REM from the last line below and replace <NETWORK_PATH> with a network location  
REM
REM   To test your scheduled task:
REM   ———————————————————————–
REM   1.) Right-click on the new scheduled task and select “Run”
REM   2.) Press F5 to refresh the task’s status
REM   3.) Two new files will appear within a few minutes in your Backup folder:
REM        <DATABASE_NAME>.bak
REM        <DATABASE_NAME>_BackupResult.txt
REM   4.) Open the <DATABASE_NAME>_BackupResult.txt file to see the results.
REM   ———————————————————————–


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -E -S .\MSSMLBIZ -Q”SET NOCOUNT ON; SELECT ‘<DATABASE_NAME> Backup started at – ‘ + CONVERT(varchar, GETDATE());” >”<BACKUP_PATH>\<DATABASE_NAME>_BackupResult.txt”


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -E -S .\MSSMLBIZ -Q”BACKUP DATABASE <DATABASE_NAME> TO  DISK = N'<BACKUP_PATH>\<DATABASE_NAME>.bak’ WITH FORMAT, INIT,  NAME = N’Full Database Backup’, SKIP, STATS = 10″ >>”<BACKUP_PATH>\<DATABASE_NAME>_BackupResult.txt”


“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -E -S .\MSSMLBIZ -Q”SET NOCOUNT ON; SELECT ‘Backup completed at – ‘ + CONVERT(varchar, GETDATE());” >>”<BACKUP_PATH>\<DATABASE_NAME>_BackupResult.txt”


REM copy /Z “<BACKUP_PATH>\<DATABASE_NAME>.bak” “<NETWORK_PATH>\<DATABASE_NAME>.bak” >>”<BACKUP_PATH>\<DATABASE_NAME>_BackupResult.txt”

Comments (40)

  1. v_2rath@hotmail.com says:

    Sounds good

    Can we have something for Vista as well ?

    Regards

    Raul Thoams

  2. Jerry Blake says:

    Yes, I’ve updated the original post with the Vista instructions.  Let me know if you have any questions.

    ~ Clinton Ford

  3. dlrowell@hotmail.com says:

    Great tool, major convenience factor; but something’s wrong in my implementation at least; backup.cmd runs fine, but task fails. Any thoughts on that?

    TIA–David Rowell

  4. Sahid says:

    How do you automatic backup BCM data on Windows Server 2003?

    Sahid

  5. Agenda_109 says:

    This works well until you want to EFS encrypt the ‘Backup’ folder.  The .BAK file gives ‘Access Denied’ because the owner of the file is ‘NETWORK SERVICE’ instead of the user who originally encrypted the folder.

    Does anyone have any idea how to get around this????

  6. stevehfx says:

    Since upgrading to vista, i am unable to back up my bcm database (BCM 2007).  I get an error that error says

    "Cannot open backup device C:users….. ….tempen-US#3.0.5625.0.  Operating system error 5 (Access is denied).

    BACKUP DATABASE is terminating abnormally.

    I have tried running Outlook as an administrator and it makes no difference.

    steve

  7. eric@m2architects.com says:

    At sitem 3 for XP it says go to security tab.  I do not have one I have only three tabs

    General

    Sharing

    Customize  

  8. lindquistc says:

    Our BCM database is installed on Server 2003 on a full instance of sql 2005. How can we alter this script to backup the database. If I use the script above it says it cannot find the instance.

  9. Spearhead says:

    Is there a way to edit this script for automated backup of all users on my SBS 2003 on a network drive?

  10. Jerry Blake says:

    Hi emilberger,

    Your hard drive must be formatted NTFS for this tab to show up.  If that is not what it is,

    go to Tools –> Folder Options –> View and uncheck Use Simple File Sharing.

    -Sateesh

  11. Jerry Blake says:

    Hi Agenda_109 and stevehfx,

    I did the following.  

    1. Created a Backup folder on desktop.
    2. Made the Folder EFS encrypt by going to the Advanced section.

    3. Created a backup.cmd file in this folder.

    4. Created a Backup schedule

    5. Ran the schedule

    Result:

    Since I forgot to give NETWORK_SERVICE Write access to this folder, I get the Access denied error, you were mentioning above.  If I give this access, everything is working correctly.

  12. Jerry Blake says:

    Hi Sahid,

    If your question is about Task Scheduling in Windows Server 2003, please refer this link:

    http://technet2.microsoft.com/windowsserver/en/library/f60ea8d6-d2af-4dd0-b050-c09951826f5d1033.mspx?mfr=true

    I also found another link, if you are running into this issue:

    http://www.jsifaq.com/SF/Tips/Tip.aspx?id=9381

    -Sateesh

  13. Jerry Blake says:

    Hi lindquistc,

    Please check your SQL Server instances for MSSMLBIZ.  Please check if it up and running.

    -Sateesh

  14. eric@m2architects.com says:

    I created the "Backup" folder on Drive E:

    Open properties and there is no security tab just sharing tab from this point I am lost..  TOO difficlult for somehting that should have been there in the first place.

  15. Jerry Blake says:

    Hi emilberger,

    Did you try unchecking simple file sharing in the view tab Advance settings of the Folder options?

    -Sateesh

  16. mja@affiliatedconsulting.com says:

    How would you add a date in the file name?

    N’FMSBCM-02-09-2008.bak’ for example.

    Not real literate with scripts… thanks for the help.

  17. I did evertyhgin I can tell for XM and I get only the M2Backup_BackupResult.txt file created and it is empty.

    No error message and no Error Status in the Task Manager

  18. Scoop0901 says:

    I have a database that I backed up on my desktop (running Vista Ultimate).  I saved all the files to my keyfob and wanted to carry them along with me so I could work on things on my laptop.  Sounds like a great idea, right?  Well, it didn’t work.

    When I put the files in the appropriate directories (after having put the original files that were in the directories in new sub-directories), then launcing Outlook with BCM, I get an error saying that the BCM database could not be launched, as it was "foreign" or something like that.

    So, just how would I move the files — even from a WinXP-to-WinXP or a WinXP-WinVista or a WinVista->WinXP computer?

    TIA,

    Dave J. (Scoop0901)

    http://blog.scoop0901.net/

  19. I still need an answer to my backup issue

  20. danieltwarren says:

    Hi

    I am having a few problems with setting this script to run on out systems. The major problems I can see are firstly tat the file ’90’ is actually named ’80’ which I have edited in the script. The next problem is that there is no sqlcmd.exe in the relavent folder. I feel this is a major problem as to why it is not working.

  21. Jerry Blake says:

    Hi ascmja,

    Cant you look at the properties of the file for the date at which it formed?

    You can get append the below script to the above script.

    for /f "tokens=1,2" %%u in (‘date /t’) do set d=%%v

    for /f "tokens=1" %%u in (‘time /t’) do set t=%%u

    if "%t:~1,1%"==":" set t=0%t%

    set timestr=%d:~6,4%%d:~3,2%%d:~0,2%%t:~0,2%%t:~3,2%

    ren "<BACKUP_PATH><DATABASE_NAME>.bak" <DATABASE_NAME>-%timestr%.bak

    -Sateesh

  22. Jerry Blake says:

    Hi emilberger,

    I did these steps on my XP machine,  It worked again.  Please start afresh and let me know.

    1. Created a Backup folder on desktop.
    2. Right click the folder, add NETWORK_SERVICE.  Give it Write access.

    3. Created a backup.cmd file with the above script in this folder.

    Please note that you need to change the BACKUP_PATH and DATABASE_NAME to your own settings.

    1. Created a Backup schedule as mentioned above
  23. Ran the schedule

  24. If it still does not run for you, you need to send the cmd file (with actual paths and filenames) to bcmisvx__AT__microsoft.com

    There are no underscores above.

    -Sateesh

  • Jerry Blake says:

    Hi Dave,

    Did you try restoring the backed up file in the other system?  When you are referring to files and folders, can you please mention them in detail?

    -Sateesh

  • rkumpitsch says:

    Hi. Great utility, however since this is saving in SQL backup format which is different from SBB format from the Backup option BCM Manage Database window, what is the easiest way to restore a BCM database from the BAK backup file?  Thanks.

  • jweschman says:

    I was able to get automated/scheduled backups working using the instructions above… thanks for providing them!

    However, the resulting *.BAK backup file that I get using the script is just over 50 megabytes in size!  By comparison, the *.SBB backup files that I get if I use the BCM/Database Tools/Manage Database/Backup menu item are just 9MB in size.

    Are the BAK files supposed to be 5x as large as the (presumably comparable in "content") SBB files??  Is there something I can do, or some way to modify the script above so that they backup files aren’t so enormous?

    Much thanks.

  • Jerry Blake says:

    The reason of file size differences between ".sbb" files and ".bak files" is that ".sbb" file is the compressed version of ".bak" file. You may want to compress your files with some compressor tool that you prefer.

    thx.

    -Fatih

  • JDuc says:

    I’ve followed the directions exactly as they are listed.

    I’ve even double checked the folder permissions, and even gave FULL permissions to ‘NETWORK SERVICE’.

    I’m still getting the no permissions error.

    What do I need to check to get this to work?

    And is there a way to tell BCM to take this file that’s been created and compress it with the tool that BCM uses normally?

    Thanks!

  • Jerry Blake says:

    Did you try running Outlook explicitly as "Run as Administrator"?

  • captbackfire says:

    I am having trouble with the automated backup at one of my customers and I’m sure that it’s my own fault.  Would y’all please look over my scheduled task command file and the results I’m getting and point out my error?  Thanks for the help…

    BACKUP.CMD:

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -E -S .MSSmallBusiness -Q"SET NOCOUNT ON; SELECT ‘MSSmallBusiness Backup started at – ‘ + CONVERT(varchar, GETDATE());" >"e:backupMSSmallBusiness_BackupResult.txt"

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -E -S .MSSmallBusiness -Q"BACKUP DATABASE MSSmallBusiness TO  DISK = N’e:backupMSSmallBusiness.bak’ WITH FORMAT, INIT,  NAME = N’Full Database Backup’, SKIP, STATS = 10" >>"e:backupMSSmallBusiness_BackupResult.txt"

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -E -S .MSSmallBusiness -Q"SET NOCOUNT ON; SELECT ‘Backup completed at – ‘ + CONVERT(varchar, GETDATE());" >>"e:backupMSSmallBusiness_BackupResult.txt"

    MSSMALLBUSINESS_BACKUPRESULT.TXT:

    HResult 0xFFFFFFFF, Level 16, State 1

    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    HResult 0xFFFFFFFF, Level 16, State 1

    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    HResult 0xFFFFFFFF, Level 16, State 1

    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    Please advise.  Thanks again.

  • Phil F says:

    I would like to rename the backup file in the DatabaseName.bak_yyyy-mm-dd-time format.  I would also like to compress the file using the .SBB or .zip compressor.  How can I accomplish these goals with your script.

  • bsharp says:

    I’m trying to follow the instructions for doing this with XP. I get to step #8, and when I hit "Ctl-H" it opens up history, not Notepad’s Replace dialog.

  • aaronshim says:

    This is the script that I use. It created the backup, rename with an appended date and then moves to a network drive (NAS box).

    ———————-

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -E -S .MSSMLBIZ -Q"SET NOCOUNT ON; SELECT ‘MSSmallBusiness Backup started at – ‘ + CONVERT(varchar, GETDATE());" >"F:BCM_BackupMSSmallBusiness_BackupResult.txt"

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -E -S .MSSMLBIZ -Q"BACKUP DATABASE MSSmallBusiness TO  DISK = N’F:BCM_BackupMSSmallBusiness.bak’ WITH FORMAT, INIT,  NAME = N’Full Database Backup’, SKIP, STATS = 10" >>"F:BCM_BackupMSSmallBusiness_BackupResult.txt"

    "c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe" -E -S .MSSMLBIZ -Q"SET NOCOUNT ON; SELECT ‘Backup completed at – ‘ + CONVERT(varchar, GETDATE());" >>"F:BCM_BackupMSSmallBusiness_BackupResult.txt"

    REM copy /Z "F:BCM_BackupMSSmallBusiness.bak" "<NETWORK_PATH>MSSmallBusiness.bak" >>"F:BCM_BackupMSSmallBusiness_BackupResult.txt"

    for /f "tokens=1-3 delims=/- " %%a in (‘date /t’) do set XDate=%%a-%%b-%%c

    for /f "tokens=1-2 delims=: " %%a in (‘time /t’) do set XTime=%%a.%%b

    ren "F:BCM_BackupMSSmallBusiness.bak" MSSmallBusiness-%XDate%.bak

    move "F:BCM_BAckupMSSmallBusiness*.bak" Z:BCM_Backup

    ———————-

    Change to suit your drive, folder and database name.

    Aaron

  • Nahum says:

    Hi there…

    I need to backup my manager DB, and i don't know nnothing about BCM.

    He's using Windows 7, so i started to follow the instructions of backing up procedure, but after step 8 I GOT STUCK.

    i press CTRL+H and nothing happends…

    Please advise…

    Btw,

    I read that there is a new version BMC 2010, does it make backing up and restoring BCM easier then the 2007 version.

    Hope to hear from you soon.

    Nahum

    Israel

  • Hello friends, says:

    … I work with Win7 and BCM 2010. How can I automate with a Batchdatei the backup of the data bank BCM?

    the best greetings,

    Ronald

  • soydeedo says:

    @Nahum

    You need to copy all of this text into Notepad first. Then CTRL + H will work and the rest of the instructions will make sense.

    As for using BCM 2010 and it being easier…not really. It's pretty much the same process and in fact, since it uses SQL Server 2008 Express, it looks like part of the script above needs to be modified – at least I think that's the reason why I had to change mine.

    c:program filesmicrosoft sql server90toolsbinnsqlcmd.exe doesn't exist on my computer, so none of the SQL was being executed. I had to replace that path with c:program filesmicrosoft sql server100toolsbinnsqlcmd.exe

    Other than that, though, it was the exact same process.

    @aaronshim

    Thanks for the easy copy/paste tip!

  • Export says:

    Many thanks for the tip. If have a backup from my database from the computer in the office. , I cannot restore the database on the computer at home. There is always a mistake!

    If I export the database, however, I can import the data also on my PC at home. My first question: What is the difference between backup and exporting?

    My second question: Can one also circumscribe this Script and then automate the export of the data?

    Greetings, Ronald

  • fluidphil says:

    just keep in mind that:

    MSSMLBIZ = the SQL Database Server Instance

    &

    MSSmallBusiness = SQL Database Name

    If you named your Server Instance or Database name anything else, you will need to modify the above script.

    THIS SCRIPT WORKS! I have used this script to backup BCM 2007, upgraded to BCM 2010, modified the script to reflect the new SQL Server 2008 Express location (see soydeedo's tip above) and it works for BCM 2010 as well.

  • Jesse says:

    Hello, I followed the instructions for Vista, but am running Windows 7. Do I have to do anything different? I tried replacing the /90 with /100, but all I am getting is a blank _BackupResult file in the folder…

    Please help!!!

    Thanks!

    Jesse

  • Sarven says:

    HResult 0x57, Level 16, State 1

    SQL Network Interfaces: Connection string is not valid [87].

    HResult 0x57, Level 16, State 1

    SQL Network Interfaces: Connection string is not valid [87].

    HResult 0x57, Level 16, State 1

    SQL Network Interfaces: Connection string is not valid [87].