Migrating Dynamics Databases from one server to another, and upgrading Great Plains.

When you are migrating the Dynamics Databases from your old server to a new one(Congratulations on the new server, BTW!), the general series of steps is as follows(bird's eye view) -

1. Backup and transfer the Dynamics and company databases
2. Backup and transfer the sql logins, sql jobs , database maintenance plans. Proof the jobs.
3. If you are upgrading from SQL 2000 to SQL 2005, you would need to upgrade the compatibility from V8 to V9.

Somewhere along, if you are also upgrading great plains, which is a common case, sometimes you might face this error.

"

The following SQL statement produced an error:
  use msdb   /*IF THE JOB DOESN'T ALREADY EXIST, CREATE IT...*/ if not exists (select name from sysjobs where name = 'Remove Posted PJOURNALs From All Companies')     begin         /*CREATE THE JOB...*/         exec sp_add_job      ....

"

or

"Server XXX does not exist"

or

"Upgrade of Database XXXX failed. Microsoft Dynamics Utilities will now shut down"

You would also face this error when you do test upgrades, as your machine name is different from the name of the machine the original backups were taken on. The jobs etc, seem to be looking for the old name.

I've found the following script handy in these cases, it changes the SQL Server name back to what you want it to be.

The code below renames SQL server.  When you are doing a test upgrade on a different server, or after you migrate to a new server

/*

Purpose: renames SQL server.

Server: all

Database: DBAservice

Originally Developed By : Yul Wasserman 03/08/02

*/

Declare @pNewName varchar(256)

Set @pNewName ='XXXXXXXX' -- Change this to the name of the new server/test server

Declare @OldName varchar(256)

Declare @NewName varchar(256)

set @OldName=''

select @OldName=isnull(srvname,'') from  master.dbo.sysservers where srvid=0

If @pNewName is NULL

Begin

        create table #NName (NName varchar (256))

        insert #NName exec master.dbo.xp_getnetname

        select @NewName=Nname from #Nname

        drop table #Nname

End

ELSE If @pNewName is not NULL

Begin

        select @NewName=ltrim(rtrim(@pNewName))

End

If @OldName<>@NewName

BEGIN

        IF @OldName <>''

        BEGIN

               print 'Attempting to drop server '+@OldName

               Exec master.dbo.sp_dropserver  @OldName

        END

        print 'Attempting to add server '+@NewName

        Exec master.dbo.sp_addserver @NewName,'local'

END

If isnull(@@Servername,'')<>@NewName

Begin

        Print 'Please shut down and restart SQL Server in order to complete renaming.'

End

Else If isnull(@@Servername,'')=@NewName

Begin

        Print 'SQL Server is already named ' +@NewName

End