Automate BCM Database Backups - Business Contact Manager Team Blog - Site Home

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"