Moving to SQL 2000
Turns out i posted on my good moves and never posted my list i use to do this. :) Thought i would post it now. Hope it helps.
Moving Sql Server Steps From 7.0 -? 2000
When moving to new computers
The top 10 can be done before anything is done to the server. That’s the prep work that should be done before moving.
1. Script Sql Logins – this can be done through the Logincreate.sql file
2. Script NT logins – This is accomplished through the same file.
3. Script All Jobs from MSDB
4. Script All Operators
5. Script All Alerts (if needed)
6. Script/Record Link Servers
7. Script/Record Server group logins. I.e. System admin, security admin.
8. Save DTS packages to an External server. Save it to another server. Or if possible the server you are moving the databases to.
DTSBackup works great for this!
9. Save any Stored Procedures in master you may need since master will not get moved over.
10. Run the detach script that will detach the databases and create the new attach script for you. – detachscript.sql
11. Copy the mdf and ldf files to there new path on the new server.
12. Shutdown the old SQL Server. If needed. Only if renaming your new server to the same name.
13. Rename and add to the domain the new computer so it has the same name as the old computer.
14. Install SQL on the new machine if needed. It may already be there in my case I had to install it since I was renaming the computer.
15. Install Service packs and Set any Server properties back to where they were previously such as Memory used.
16. Run the Attach script to put the databases back on the server. This will automatically upgrade the databases.
17. Re-create any of the Server system groups
18. Run Login Scripts
19. Run the orphan user procedure to make sure there are no orphan users. –sporphanuser
20. Update stats on all databases that were attached.
21. Run scripts for Jobs, Operators and Alerts.
22. Re-Create any Link Servers
23. Add to Active Directory if needed.
24. Validate
25. Go Live
Good luck in your moves.