How I moved my SQL PA9 database

LexisNexis Practice Advantage 9 Enterprise
Moving from SQL 2000 Sp 3 on MS SBS2003 (S1)
Moving to SQL 2005 on MS SBS2003 R2 (S2)
The order of events is the most important part. Begin with a SQL db file backup from S1 and clean SQL 2005 install with instance name PA9 on S2
1. Run tmutile.exe to create intial SQL db tables. This will create the AdminCTL.tps file. My case also changed the tm9user password from weak to strong.

  • Make sure to set the SQL server name correctly in this step.  Needs to include the server name followed by the SQL Instance name. Ex: servernamePA9
  • Change the database name to LNTPA9 and LNTPA9_Tutor
  • Changed tm9user password (forced me, because old pass was weak)
  • exit tmutile.exe

2. Delete the database objects (LNTPA9 but not tm9user) using SQL manager
3. Create new db with same name. Set DBowner to tm9user. Run PA engineer’s script:
Subject: Script
use [TimeMatters9] — *** SUBSTITUTE TimeMatters9 WITH THE NAME OF THE DB
declare @DBname as varchar(500) set @DBname = ‘TimeMatters9’ — *** SUBSTITUTE TimeMatters9 WITH THE NAME OF THE DB
declare @DBowner as varchar (500) set @DBowner = ‘tm9user’ — *** SUBSTITUTE tm9user WITH THE NAME OF THE DB OWNER/USER
/* MAKE NO CHANGES BELOW */
exec (‘sp_defaultdb [‘+@DBowner+’], [‘+@DBname+’]’)
exec (‘sp_change_users_login @action=update_one, @usernamepattern=[‘+@DBowner+’], @loginname=[‘+@DBowner+’]’)
4. Test Connection