Restoring an Automatic SQL Backup
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