NAS Technology Consulting

How I moved my SQL PA9 database

July 12th, 2009

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

1 Comment

  • NAS Technology Consulting, LLC – How To Move a Time Matters and PCLaw Database to a New Server
    May 18, 2015 at 3:24PM

    […] is an updated post to my previous entry regarding moving LNTPA Practice Advantage 9 to a new server.  This time I had to move Time Matters 11 from SBS 2003 server running SQL 2005 […]