How to move the Track-It! 11.xx database to another database server using MSSQL tools

Manage Support IDs

Use checkboxes to 'Remove support IDs/Users' or 'Allow to create Cases'
Support IDs for BMC Customer Support Site Guest User
Profiles Which Include    Back to All Support IDs
 

Knowledge Article

How to move the Track-It! 11.xx database to another database server using MSSQL tools
How to move the Track-It! 11.x database to another database server using MSSQL tools or How to upgrade from SQL Express to Full version of SQL using MSSQL tools.
Track-It!
Track-It!
Track-It! 10 and above
How to move the Track-It! database to another database server using MSSQL tools
NOTE: If you are running the Track-It! database on SQL Express and do not have any database administration tools installed, download and install Microsoft SQL Server 2008 R2 Management Studio Express to your Track-It! server.

1. Prepare for the Backup
 
a. On the Track-It! application server, open the following directory (default):
 
C:\Program Files (x86)\BMC Software\Track-It!\Track-It! Services\ConfigurationData

OR
 
C:\Program Files (x86)\Numara Software\Numara Track-It!\Track-It! Services\ConfigurationData
 
b. Open the TrackIt.Core.DataAccess.xml file with Notepad.
c. Search for the "DatabaseConnections" section as shown below:
 
 <section name="DatabaseConnections">
      <Reports server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Reports>
      <Default server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Default>
      <System server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </System>
      <Search server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Search>
      <Configuration server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Configuration>
      <Barcode server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Barcode>
    </section>
 
d. Check the "Password" value that appears in each of the six lines.
 
Note: If you are running version 11.4 SP1 R1 (11.4.1.558) then the password hash will be different than 63EJNemolRKl4LPtaDsCPQ== . Do Not attempt to change the password.
However, if you are running a lower version than 11.4 SP1 R1 and If the password hash is other than 63EJNemolRKl4LPtaDsCPQ== please take the following steps, otherwise, you may skip these steps.
 
i. Login to Technician Client with a Track-It! administrator account.
ii. Click Tools -> Administration Console.
iii. Under Configuration -> Administration, click on Change Database Password.
iv. On the right side, review the warning message and enable the check box.
 
NOTE: This process will reset the Track-It! specific SQL database users' passwords and will cause Track-It! to close. Please make sure everyone is out of the Track-It! system before proceeding.

NOTE: TI_DB_P@ssw0rd is the default SQL database password for the Track-It! specific SQL logins. After you are completely finished moving Track-It! you may change these if you wish, please reference Track-It! documentation or our knowledge-base at bmc.com/support for more information.
 
v. In the "Enter a new password below" field, enter the following password exactly as it appears (the password is case sensitive and the 0 is a zero):
 
TI_DB_P@ssw0rd

vi. In the "Confirm password" field, enter the same password.
vii. OkClick 
 
During this process, Track-It! may appear to hang for a period of time. After the delay, Track-It! will close by itself.
 
viii. Run Technician Client again and login as the same technician.
ix. If prompted to close the existing login that was in use at the time that the password was changed, click Yes.
x. Close Technician Client.

 

e. Before backing up and restoring the Track-It! database be sure to have everyone close out of any Track-It! applications.
f. On the Track-It! server stop the Track-It! Service Management service.
 
2. Backup the current database
 
a. Open SQL Server Management Studio on the database server or on a machine that has the SQL tools installed.
b. Login as a user account that at a minimum has the db_backupoperator or db_owner fixed database role in the database to be backed up. It is usually easiest to login as a user account that has the sysadmin fixed server role.
c. Expand the Databases folder.
d. Right-click the database that you would like to backup and select Tasks -> Back Up.
e. If a file path already appears in the "Destination" window at the bottom, take note of the path and name of the backup file that will be created and then click OK to begin the backup
 
If a file path does not exist in the window, click the Add button and continue with the steps below
 
i. Click the ellipse button to the right of the "File name" field.
ii. In the "File name" field at the bottom of the "Locate Database Files" window, enter the desired backup file name. This will be the name of the backup file once the backup is complete. Take note of the path where the backup file will be created.
iii. Click Ok and then click Ok to close the "Select Backup Destination" window.
iv. Click Ok to begin the backup.
 
f. Click Ok once the backup has completed successfully.
 
3. Copy the backup file to the new database server

SQL Server's default backup file location should be similar to the following:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

Copy the .bak file that was created by the backup to the new database server's Backup folder.
4. Restore the database onto the new server
 
SQL Server 2012
 
  1. Open SQL Server Management Studio on the new database server.
  2. Login as a user who has the sysadmin fixed server role.
  3. Right-click the Databases folder on the left and select Restore Database.
  4. Under the 'Source' section, select 'Device' first and then click the ellipses button on the right side.
  5. Next, click the Add button.
  6. Navigate to the backup folder where the database backup was copied, select the BAK file, and then click OK.
  7. Click OK again on the Select backup devices window.
  8. Under the Destination section of the Restore Database window, in the Database field enter the name of the database that you would like the restore to create. Make sure this name is not already being used by an existing database.
  9. Click OK to start the restore.
  10. Once complete click Ok.
SQL 2005 and 2008
 
  1. Open SQL Server Management Studio on the new database server.
  2. Login as a user who has the sysadmin fixed server role.
  3. Right-click the Databases folder on the left and select Restore Database.
  4. Select 'From device' under the 'Source for restore' section and then click the ellipses button on the right side.
  5. Next, click the Add button.
  6. Navigate to the backup folder where the database backup was copied, select the BAK file, and then click OK.
  7. Click OK again on the Specify Backup window.
  8. In the 'Select the backup sets to resotore' window, click the Restore column to put a check mark in the box next to the name.
  9. In the 'To database' field at the top, enter the name of the database that you would like the restore to create. Make sure this name is not already being used by an existing database.
  10. Click OK to start the restore.
  11. Once complete click Ok.
 
5. Correct the SID of the Track-It! database users.

IMPORTANT NOTE!: When you restore a database from one SQL server to a different server, the following steps MUST be taken to ensure that the restored database and its user(s) are valid on the new SQL Server. Even though the user accounts will appear in the restored database, their SIDs, which had been generated on the original SQL Server, will not be valid for the new SQL Server. Failure to follow these steps WILL result in the inability to login to Track-It!.
 
  1. Download the attached file TIA02966.sql which contains a query that will correct the SIDs from the Attachments section below.
  2. On the new SQL Server, run SQL Server Management Studio and login with SA or an SA equivalent account.
  3. Click the New Query button.
  4. Open the .sql file with Notepad and copy the text from the script into the New Query window.
  5. In the database dropdown list shown in the toolbar, select the restored database.
    User-added image
  6. Click the Execute button above the query window to run the script or press F5.
 
6. Add Necessary Custom Error Messages to SYSMESSAGES 
 
To avoid errors that may occur due to missing custom error messages on the new database server, please take the following steps:
 
a. Download the attached file TIA03830.zip which contains a SQL update script that will help resolve the issue from the Attachments section below.
b. Extract the AddCustomMessages.sql file to the database server's desktop.
c. On the database server, run SQL Server Management Studio and login with SA or an SA equivalent account.
d. Click the New Query button in the upper left (under the File menu).
e. Open the AddCustomMessages.sql file with Notepad and copy the text from the script into the New Query window.
f. In the database dropdown list shown in the toolbar, select the restored database.
g. Click the Execute button above the query window to run the script.


7. Point Track-It! to the restored database

 a. On the Track-It! application server, open the following directory (default):

C:\Program Files (x86)\BMC Software\Track-It!\Track-It! Server

OR
 
C:\Program Files (x86)\Numara Software\Numara Track-It!\Track-It! Server
 
b. Open the trackit.cfg file with Notepad.
c. Locate the [ODBC] section and change the information in the bold lines below to point to the new database:

[ODBC]
DBTYPE=SQL Server
SERVER=DBServer\InstanceName (if a named instance is being used)
DATABASE=RestoredDBName
MAXRECORDS=2000
MAXRECORDS_LOOKUP_TABLE=2000
ASYNC_FETCH=0
TRACKIT_USER=TRACKIT80 (could be TRACKIT80_1, TRACKIT80_2, etc.)
TRACKIT_PASSWORD=63EJNemolRKl4LPtaDsCPQ==
DSN_NAME=Trackit

 
d. Save the changes to the file and close it.
e. Next, open this directory:

C:\Program Files (x86)\BMC Software\Track-It!\Track-It! Services\ConfigurationData
OR
 
C:\Program Files (x86)\Numara Software\Numara Track-It!\Track-It! Services\ConfigurationData

f. Open TrackIt.Core.DataAccess.xml file with Notepad.
g. Search for the "DatabaseConnections" section as shown below. 
 
 
 <section name="DatabaseConnections">
      <Reports server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Reports>
      <Default server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Default>
      <System server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </System>
      <Search server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Search>
      <Configuration server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Configuration>
      <Barcode server="VPCSQL2012" database="TI21_Trackit_Data" user="
      </Barcode>
    </section>

h. Update the server name and database name in each of the six lines shown above so that they point to the new database server and the database that had been restored.
i.  Double check to make sure the Track-It! SQL Users shown in those lines match with the users that are listed in the restored database.
j. Start the Track-It! Service Management service.

Track-It! should now launch successfully and connect to the restored database.

This can be verified after logging into Track-It! by clicking Help -> Session Info.
Track-It!
Track-It!
Track-It! 10 and above
Track-It!
Track-It!
Track-It! 10 and above
Attachment(s):
TIA03830.zip
TIA02966.sql