How do I use Microsoft SQL Server Management Studio Express for ProSystem fx Practice Management SQL Database Backups?

Download and Install

SQL Management Studio Express is a free download from the Microsoft website. Follow these

instructions to download and install SQL Management Studio Express on your Server.

 

1. Go to the Microsoft site at http://www.microsoft.com
2. Select Downloads & Trials on the main Microsoft webpage.
3. Select the Download Center.
4. Search for Management Studio to find this tool in the list of available downloads. The first result will be for Microsoft SQL Server Management Studio Express.
5. Select this tool. Read the Overview and Instructions. Note that because you have installed ProSystem fx Practice Management, you will have already installed SQL Server 2005 and.NET Framework 2.0 on the Server.
6. Download this tool to the Server by clicking the SQLServer2005_SSMSE.msi Download button.
7. Once the tool is downloaded you are ready to install. Select Run. A wizard displays, walking you through the installation process. Accept the license agreement and follow the instructions to install SQL Server Management Studio Express on your Server. Do not install to a workstation.

 

Back up

 

Follow these instructions to make your nightly backups at your firm.

 

1. Select Microsoft SQL Server 2005 from the Start/Programs menu. Then select SQL Server Management Studio Express.
2. The Connect to Database window displays.
3. Server name displays your ProSystem fx Practice Management Server name. Accept this default.
4. Authentication displays the default of Windows Authentication. Accept the default.
5. Click Connect to connect to the database.
6. Click Databases in the navigation bar to expand the list of available SQL databases.
7. Right-click your firm’s ProSystem fx Practice Management SQL database and select Task from the right-click menu. Then select Back Up.
8. Use the Back Up Database window to make your backup instructions. Two windows are available from the navigation pane (General andOptions).
9. Use the General window to select the Backup type and backup file Destination.
10. Verify the Database name on the General window. Notice that the ProSystem fx Practice Management SQL database selected in Step 7 will be selected by default.
11. Use the Description box on the General window to enter a description of the backup.
12. Specify when the Backup set will expire. Notice that the default value of 0 means that the backup will never expire.
13. Select the Options window to set Overwrite and Reliability options. Make sure you select to Overwrite all existing backup sets so that your backups will overwrite and not append. You may also want to select to verify the backup when it is finished to make sure your backup will be available should you need to restore it.
14. Click the Help icon at the top of the window for online Help and more information on backing up and restoring databases. A See Also list ofTasks is available in Help with detailed “How To” instructions on creating a Full Database Backup and restoring a Database Backup.
15. If you want to back up manually, click OK to make the backup. To automate the backup, continue with the instructions for Automatic Backup.
16. A message displays when the backup is successfully completed and a log displays backup task information.

 

Automatic Backup

 

1. Follow Steps 1-15 above to build the backup instructions.
2. Save your backup instructions to a Script so that you can select the script to run the backup in the future by clicking the drop-down Script arrow at the top of the window and selecting Script Action to File.
3. If you have not already done so, create a special SQL Backup folder on the Server. You can use the Create New Folder icon on the Save Aswindow. Browse to the location on your Server and enter the folder name (for example, PMBackup). Make sure that your folder (path) name does not contain any spaces.
4. Enter the name of your backup script (for example, SQLBackup) and click Save. Your script will be saved with a .SQL file extension.
5. Close SQL Management Studio Express.
6. Use Notepad or the editor of your choice to create a batch file using the script file you created.

In the batch file, enter the following command:

 

SQLCMD.EXE –S YourServer\Instance –i path\script.sql

 

Where:

YourServer\Instance is your Server name\Instance (for example, Server\PracticeMgmt)

and

path\script.sql is the path and name of the script created in Step 4 (for example,

c:\PMBackup\SQLBackup.sql)

 

Note:The command is case sensitive and the “–S” must be upper case while the “-i” is

lowercase.

 

1. Save your batch file with a .BAT file extension. You may want to store it in the same SQL backup directory as the Script file created in Step 3 above.
2. On the Server, from the Start menu, open Control Panel and select Scheduled Tasks. This opens the Windows Task Scheduler. Make sure you are scheduling the backup on the Server and not a workstation as the backup will not run properly from a workstation.
3. Select to Add Scheduled Task. Follow the directions in the Wizard to add the backup as a scheduled task on the Server. Click Next to continue.
4. Browse to the .BAT file you created for the Script and select it as the program to run.
5. Enter a name for the task and select how often to perform this task (Daily, Weekly, etc.) and click Next.
6. Select the day and time when you want this task to start and click Next.
7. Enter the name and password of a user on the system. The task will run as if started by the user/password you enter here. You must enter this information for the task. The backup will not run as scheduled if the password is not entered. Then click Next.
8. Click Finish to complete the Wizard.
9. Your backup will be made in the Destination folder you specified in Step 9 (Back Up).
10. Verify the backup and make sure it is running properly. Also make sure you can restore from the backup.