Automate BCM Database Backups

http://blogs.msdn.com/b/bcm/archive/2007/09/05/automate-bcm-database-backup.aspx

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 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 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.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 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 .bak
REM _BackupResult.txt
REM 4.) Open the _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 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 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.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 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 .bak
REM _BackupResult.txt
REM 4.) Open the _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 ‘ Backup started at – ‘ + CONVERT(varchar, GETDATE());” >”\_BackupResult.txt”

“c:\program files\microsoft sql server\90\tools\binn\sqlcmd.exe” -E -S .\MSSMLBIZ -Q”BACKUP DATABASE TO DISK = N’\.bak’ WITH FORMAT, INIT, NAME = N’Full Database Backup’, SKIP, STATS = 10″ >>”\_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());” >>”\_BackupResult.txt”

REM copy /Z “\.bak” “\.bak” >>”\_BackupResult.txt”