2

We're replacing our Windows NT server running SQL 2000 with newer hardware running Windows Server 2003 but still SQL 2000. What is the easiest way to do this?

We know how to copy the normal databases etc either by backup and restore or moving and reattaching the data files but we also need to copy logins, DTS packages, SQL Agent jobs etc. We've found instructions on how to do this using DTS packages although it seems kind of complicated to avoid orphaned logins etc.

Given that we just want to copy everything, could we copy the system databases and therefore bring everything over in one bucket so to speak? I haven't really found any info to indicate whether this is possible or if there is too much O/S and other installation specific stuff in the system databases for that to work.

Thanks

tetranz
  • 315
  • 2
  • 6
  • 14

2 Answers2

4

It's not recommended (and not supported) to overwrite system database with those from another server instance, but it is technically possible. I much prefer to migrate the pieces myself. Install your new SQL 2000 instance clean on the new Win2003 server and then take things over to the clean instance, you'll be happier that way.

Databases you have, either backup/restore or detach/reattach.

Logins you can copy over using sp_help_revlogin. Create the proc on your source instance, run it on the source to generate a script that will recreate the logins (maintaining SID's and all that fun stuff), then run the generated script on the new server instance.

DTS packages can be migrated using DTSBackup 2000. Works great.

SQL Agent jobs can be scripted from the original server and recreated on the target server.

If the server hostname changes you'll have to do a few more things on the new server:

  • sp_dropserver 'oldservername'
  • sp_addserver 'newservername','local'
  • Fix ownership of jobs where the owner was a local user on the old server
  • UPDATE msdb.dbo.sysjobs set originating_server='newservername'

If you have linked servers on the old server you can use the script on this page on SQL Server Central (note, you might need to register to view it - which is free and worth it anyway). You'll need to recreated the passwords on the new server, it doesn't carry them over. There's also a tool that used to use called ScriptLinkedServers.exe, but I can't find it right now.

EDIT: Found it - ScriptLinkedServers tool on CodeProject

Good luck!

squillman
  • 37,883
  • 12
  • 92
  • 146
0

If you install SQL to the same path on the new server you should be able to grab the MSSQL folder from the old server (after turning the service off) and copy it on top of the new server's MSSQL folder and it should be an exact copy. You will need to update the sysservers table using the built in procedures in order to get @@servername to display the new hostname.

http://support.microsoft.com/kb/303774

Jason Cumberland
  • 1,579
  • 10
  • 13