Rebuilding and upgrading sql server

[shaili]

Hi,

I have to rebuild the sql server ( add more drives, reconfigure raid, and upgrade os to win2000 from NT 4.0 ).
At the same time I have to upgrade sql server from sql 7.0 to sql 2000 ( with 7 main user databases for the business.
What is the best way to do it?

Here is what I think:

1. upgrade the sql server 7.0 to sql server 2000
2. take backups for the system databases and move the backups to other machine or tape
3. detach and move the database files on the other machine for the user databases
4. rebuild the machine with win2000
5. Install sql server 2000 (on different drive then before)

Here I am little confused and concerned about restoring master and msdb.

Are there any known problems restoring master and msdb from the backup files on the same server but on different drive also with different os?

6. Attach the user databases.

Are there any other points that I need to worry about? We do not have test server. This is the main prod server that needs to be worked on directly. There is no backup plan so this upgrade has to be successful. Sounds scary, right? It is scary.

Any advise? any tips - tricks?

Also, Please let me know if you any good link to the related article.

Thanks,
Shaili

 

[nealvenables]

This will sound convoluted (it is!) but I always prefer to keep my databases spinning on a hot-disk.

For maximum safety...

1. Backup your production system onto tape.
2. Get hold of a PC spec machine and build it up to the same software configuration as your production server (SQL 7/NTS 4)
3. TRANSFER all of your databases from your production server to you PC spec "server".
4. Upgrade the PC spec "server" to NTS2000.
5. Upgrade SQL on the PC spec "server" to SQL 2000.
6. Check the integrity of the upgraded SQL dbases.
7. Upgrade your production server to NTS 2000.
8. Upgrade your production server to SQL 2000.
9. Check the integrity of your dbases on your upgraded production server. If they are compromised then...
(10) ...TRANSFER your databases from the PC spec "Server" to your upgraded production server.

This sounds complex and convuluted, but it is safe. If things foul up you have two fall-back positions: (i) the PC "server" and (ii) your initial tape backup.

I've used this approach several times on large, mission critical systems and I sleep well at night(!)