Move entire SQL Server instance

I have tried searching all over and have not been able to find what I need, but this is what I have pieced together.

Background:
I need to move an entire instance of SQL Server 2000 SP3 running on Windows Server 2000 to a new physical server running SQL Server 2000 SP4 running on Windows Server 2003. I guess my biggest question is how do I move system databases (master,msdb,tempdb,model), I don't particularly like the idea of using sp_detach/sp_attach on system databases?

This is what I had in mind.
-Install SP4 on the source server
-Backup source to tape
-Restore from tape to destination server (restore system dbs as re_master,re_msdb)
-Shut SQL services down on destination server.
-Replace the system dbs .mdf and .ldf with the re_master & re_msdb .mdf and .ldf
-Restart SQL services
-Restore User databases from tape.

Ultimately, I'm asking can you change the undlying .mdf & .ldf files for system databases?

Also, are there any issues going from Windows Server 2000 to Windows Server 2003?

Thanks in advance.gee, why would you want to move temp? most people would not need to move model either.

I would...

1. backup your source user databases.
2. restore them to the new server.
3. migrate what you need from master and msdb and you can do some of this with DTS I believe and you can have EM script some other things. It really depends on your needs.|||You can also backup and restore the msdb database, if you have large numbers of jobs and/or DTS packages (shudder) to move. You will have to go in and update the sysjobs table, so the SQL Agent on the new machine does not think it is hosting jobs from a master job server, though. There is no good way to go through jobs and DTS packages for all the hard-coded server names, though.|||I've migrated lots of instances (from non-clustered to clustered, from old h/w to new) and have never attempted to restore a backup of master, model or msdb.

- Most of master gets updated when databases are restored/attached.
- Jobs can be scripted (keep new db names the same as old)
- DTS packages can be backed up using a script (and moved using a variation of the same script)

I'm always leery of restoring master, model or msdb because you might get some ugliness in there as well.

Just my $.02.

Regards,

hmscott|||something else that has worked in the past ...
1. Build out your new server.
2. Stop sql server on both old and new servers.
3. Rename the master mdf and ldf on the new server
4. Copy the master mdf and ldf from the old server to the new server in the location specified for the new server
5. Fire up the new server.
6. Modify sysservers for the new server name (unless you will be using the same server name).
7. Use microsoft methods for moving temp and restoring msdb
8. Detach and reattach the user database files, or backup and restore the user database file, whichever you prefer.|||I'd take hmscott's advice:
- Install the new instance
- restore all user databases
- Script everything else you need from the old instance

Because:
- Master: only contains the logins needed, just script them. If you use the same sid's in the script you don't even have to use sp_change_users_login (when you only have Windows Authentication you don't even have to do that)
- Model: most people don't do anything special with model and leave it standard. If you have customized stuff in there do a compare and script it.
- msdb: script (jobs, DTS, etc)! If you really want to, this is the only one I'd restore (prepare for some problems though).
- tempdb: don't touch it, it's useless to try. Each restart will empty tempdb so there's nothing in there to re-use. If there is, you're doing something wrong.|||Thanks for all the insight, in case you couldn't tell this is my first migration and I'm just trying to make sure I don't miss anything. I'll let you know what I did and how it turned out.|||Am I understanding this correctly, that I don't have to install SP4 on the source server if I take Lexiflex & hmscott's advice?|||that's correct|||Am I understanding this correctly, that I don't have to install SP4 on the source server if I take Lexiflex & hmscott's advice?
Why wouldn't you want to install SP4?

Btw: if the old server has logshipping, replication or linked servers don't forget to script or rebuild them too. They're also in master...|||I probably will install SP4 on the source. I was just wondering because the source server is going to get wiped out and have a clean install of Windows Server 2003 and new installation of SQL anyways.

No logshipping, replication or linked servers to worry about.|||Sorry, I read your post too quickly. You said source-server not target :o

You do not need to install SP4 on the source.