See Also

Backup SQL Database

This information only applies to installations with Amadeus - PMPRO version 10.1 with a SQL Server configuration and all installation with Amadeus - PMPRO version 10.2 and newer.

If you use a version of Amadeus - PMPRO 10.1 or Amadeus - PMPRO 10.2,  critical data is stored in a SQL database for added security. An SQL database can usually not be included in a third party backup system because it is in use by Microsoft SQL server. An SQL backup system can be configured as indicated below. The resulting backup file can then be included in the third party backup system and/or the copy & compress backup.

Create backup folder and batch file

1. Create a new folder SQLBackup in the PMS folder.

2. Download the PMPROSQLbackupTasks.zip file and unzip it into the \Brilliant\Setups folder.

3. Copy the \Brilliant\Setups\PCI\PMPROSQLbackup.bat file into the \Brilliant\PMS folder.

4. Right click the PMPROSQLbackup.bat file and choose edit. Example.

   set variable1=c:\Brilliant\PMS\SQLBackup\ set variable2=%variable1%PMPRO-FULL.BAK set variable3=%variable1%PMPRO-FULL2.BAK if not exist %variable2% GOTO BACKUP if exist %variable2% GOTO STEP1 :STEP1 if exist %variable3% goto STEP2 if not exist %variable3% goto STEP3 :STEP2 del %variable3% GOTO STEP3 :STEP3 copy %variable2% %variable3% del %variable2% GOTO BACKUP :BACKUP sqlcmd -S .\SQLEXPRESS -Q 'EXEC PMPRO.dbo.BackupDatabases @BackupDir='%variable1%', @DatabaseName='PM PRO', @BackupType=0' goto END :END

    

5. Verify the backup location in variable1 indicated in bold below. This should point to the folder you created in step 1. Change if needed.

6. Verify the @DatabaseName in the :BACKUP section. This should be the name of the SQL database. Only change incase the actual name is different then the default name 'PMPRO'.

Create Stored Procedure

A stored procedure in the database takes care of the backup process.

7. Open SQL Server Management Studio

8. Expand \pcname\SQLEXPRESS

9. Expand Databases

10. Select the PMPRO database

11. Open the \Setups\PCI\PMPRO_SQL_backup_Databases_StoredProcedure.sql scrip file. This opens a new Query window.

12. Execute the SQL command

13. Expand Security

14. Expand User

15. Right click PMPROdbuser and select properties

16. Check if the user has the following Database Role Memberships

    • Under Role Members check db_backupoperator, db_datareader and db_datawriter

Create a Scheduled Task (!! Change the path 'C:\Brilliant\PMS\PMPROSqlBackup.bat' to match the folder on the server!!)

Windows task scheduler is used to execute the backup.

From Server 2003

17. Enter the following entry in the RUN command box:

at \\<serverIPaddress> 00:01 /every:m,t,w,th,f,sa,su 'C:\Brilliant\PMS\PMPROSqlBackup.bat'

e.g. at \\10.0.31.12 00:01 /every:m,t,w,th,f,sa,su 'C:\Brilliant\PMS\PMPROSqlBackup.bat'

18. Click OK

19. Reopen the RUN command box

20. Change the line to:

at \\<serverIPaddress> 12:01 /every:m,t,w,th,f,sa,su 'C:\Brilliant\PMS\PMPROSqlBackup.bat'

21. Click OK

22. Open the Scheduled Tasks from the Control Panel

23. Rename the task 'at1' to 'PMPROSQLbackup1' and rename the task 'at2' to 'PMPROSQLbackup2'

From Server 2008

24. Open the Scheduled Tasks from the Control Panel or Administrative Tools

25. Click 'Create Task'

26. Name the task 'PMPROSQLbackup1'

27. Enable 'Run whether user is logged on or not'

28. Enable 'Do not store password. The task will only have access to the local resources'

29. Click the Triggers tab » [New]

    • Begin the task: On a schedule

    • Time 00:01

    • Enable Daily

30. Click the Triggers tab » [New]

    • Begin the task: On a schedule

    • Time 12:01

    • Enable Daily

31. Click the Actions tab » [New]

    • Action: Start a program

    • Browse for 'C:\Brilliant\PMS\PMPROSqlBackup.bat'

32. Click on the settings tab

    • Enable 'If the task fails, restart every'

    • 'Attempt to restart up to': 3

33. Click [OK]  

Test the Task

34. Open the Scheduled Tasks from the Control Panel or Administrative Tools

35. Right click on the task 'PMPROSQLbackup1' and click run then repeat for 'PMPROSQLbackup2'

36. Check if a file called PMPRO-FULL.BAK (where PMPRO will be the database created for PMPRO)

37. When the file is not created please check the 'Problem History' and the steps in the procedure.

PMPRO Preferences

Make sure the PMPRO copy & compress feature does not exclude the BAK file extension.

38. Start PMPRO

39. Go to Setup » Preferences and open the [Compress] tab.

40. Make sure the File extensions to Exclude from the Copy & Compress procedure setting does not contain the BAK file extension.

Problem History

1. Backup file is not created: target location in the PM PROSQLbackup.bat is incorrect

   1. Open the \Brilliant\Setups\PCI\PM PROSQLbackup.bat file.

   2. Make sure the variable1= path in the \Brilliant\Setups\PCI\PM PROSQLbackup.bat batch file is correct and ends with a back slash.
      
      Example: set variable1=c:\Brilliant\PMS\SQLBackup\

2. Backup file is not created: administrator user has no access rights to the PMPRO database

   1. Open SQL Server Management Studio and login as SA (SQL administrator)

   2. Expand pcname\SQLEXPRESS

   3. Expand Security

   4. Expand Logins

   5. Right click 'BUILTIN\Users' and select properties

   6. Select User mappings

   7. Enable the tickbox PMPRO in the map column

   8. Click OK

   9. Close SQL Server Management Studio

    

3. Backup file is not created: PMPROdbuser user has no access rights to the PMPRO database

   1. Open SQL Server Management Studio and login as SA (SQL administrator)

   2. Expand pcname\SQLEXPRESS

   3. Expand Databases

   4. Expand PMPRO

   5. Expand Security

   6. Expand Users

   7. Delete PMPROdbuser

   8. Expand Security at very bottom (above Server Objects)

   9. Expand Logins

   10. Right click ‘PMPROdbuser’ and select properties

   11. Select User mappings

   12. Enable the tickbox PMPRO in the map column

   13. Under Role Members check db_datareader and db_datawriter.

   14. Click OK

   15. Close SQL Server Management Studio