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
Anonymous
September 05, 2007
Sounds goodCan we have something for Vista as well ?RegardsRaul ThoamsAnonymous
September 05, 2007
Yes, I've updated the original post with the Vista instructions. Let me know if you have any questions.~ Clinton FordAnonymous
September 18, 2007
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 RowellAnonymous
October 06, 2007
How do you automatic backup BCM data on Windows Server 2003?SahidAnonymous
October 11, 2007
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????Anonymous
October 30, 2007
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.steveAnonymous
November 08, 2007
At sitem 3 for XP it says go to security tab. I do not have one I have only three tabsGeneralSharingCustomizeAnonymous
November 16, 2007
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.Anonymous
December 04, 2007
Is there a way to edit this script for automated backup of all users on my SBS 2003 on a network drive?Anonymous
December 12, 2007
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.-SateeshAnonymous
December 12, 2007
Hi Agenda_109 and stevehfx,I did the following. Created a Backup folder on desktop.Made the Folder EFS encrypt by going to the Advanced section. Created a backup.cmd file in this folder. Created a Backup schedule 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.Anonymous
December 12, 2007
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=trueI also found another link, if you are running into this issue:http://www.jsifaq.com/SF/Tips/Tip.aspx?id=9381-SateeshAnonymous
December 12, 2007
Hi lindquistc,Please check your SQL Server instances for MSSMLBIZ. Please check if it up and running.-SateeshAnonymous
January 11, 2008
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.Anonymous
January 11, 2008
Hi emilberger,Did you try unchecking simple file sharing in the view tab Advance settings of the Folder options?-SateeshAnonymous
February 08, 2008
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.Anonymous
February 13, 2008
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 ManagerAnonymous
February 14, 2008
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/Anonymous
February 19, 2008
I still need an answer to my backup issueAnonymous
February 25, 2008
HiI 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.Anonymous
February 26, 2008
The comment has been removedAnonymous
February 26, 2008
Hi emilberger,I did these steps on my XP machine, It worked again. Please start afresh and let me know.Created a Backup folder on desktop.Right click the folder, add NETWORK_SERVICE. Give it Write access. 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.Created a Backup schedule as mentioned aboveRan the schedule If it still does not run for you, you need to send the cmd file (with actual paths and filenames) to bcmisvx__AT__microsoft.comThere are no underscores above.-SateeshAnonymous
February 26, 2008
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?-SateeshAnonymous
April 30, 2008
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.Anonymous
April 30, 2008
Nevermind. I found your post about restoring at: http://blogs.msdn.com/bcm/archive/2007/09/06/restoring-an-automatic-sql-backup.aspxAnonymous
May 17, 2008
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.Anonymous
May 19, 2008
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.-FatihAnonymous
June 24, 2008
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!Anonymous
June 24, 2008
Did you try running Outlook explicitly as "Run as Administrator"?Anonymous
July 21, 2008
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 1SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].HResult 0xFFFFFFFF, Level 16, State 1SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].HResult 0xFFFFFFFF, Level 16, State 1SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].Please advise. Thanks again.Anonymous
September 06, 2008
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.Anonymous
September 11, 2008
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.Anonymous
January 09, 2009
The comment has been removedAnonymous
October 22, 2010
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 IsraelAnonymous
November 04, 2010
... I work with Win7 and BCM 2010. How can I automate with a Batchdatei the backup of the data bank BCM? the best greetings, RonaldAnonymous
November 08, 2010
@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!Anonymous
November 12, 2010
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, RonaldAnonymous
January 20, 2011
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.Anonymous
March 18, 2011
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! JesseAnonymous
February 23, 2012
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].