April 23, 2008 at 2:41 pm
I am a System Admin who been tasked to migrate old sql2000 server to a new sql2000 server.
Does anyone have some simple steps I can follow, I did some reading and discovered that database backup and recovery is the best way, also recoving the master, msdb, and the model then the rest of the databases is a good solution. Please help
Thank,
Mark
April 24, 2008 at 10:57 am
Hi Mark,
I guess it is personal preference on how you want to do it...in the past I have done the following when we migrated servers to new hardware
The new server is set up identical to the old server in regards to drives and folder structure...
For example
C:\ (doesn't really matter)
E:\ where .mdf's go
F:\ where .ldf's go
And you re-create the folder structure that they are on the old server (E:\MSSQL2000\MSSQL\Data\...)
Once the target server is setup with the folder structure...
1. Install SQL using the directories you defined...
2. Install the exact service packs and or hot fixes that the old server is at
3. Once you have the new server online and matching the specs of the old server you "should" be able to do the following steps
1. Shut down both sql services on both servers
2. move the system databases OUT of their respective directories on the TARGET server and place them in a different folder for safe keeping
3. move the system databases OUT of the SOURCE server to the TARGET servers "system databases folder"
4. move the user databases to the respective folders.
If you do it correctly then you "should" be able to just start the SQL services up on the new server and it should recognize the databases like nothing is different.
Then you need to change some values in the system tables...
1. the server name (Article HERE = http://www.sqlservercentral.com/articles/Administering/65/)
2. look at the select * from msdb.dbo.sysjobs table and you will have to update the originating_server column to the new server name.
I am pulling most of this from memory but it is what we did when we move a SQL server to a new location on new hardware with a new server name and it worked slick.
Lee
April 25, 2008 at 3:12 pm
And if you can rename the new machine the same as the old (after taking the old one off the wire), including the IP address, you shouldn't have any issues, not even connectivity from your clients. We just recently did this and had no issues. If you cannot, take Lee's advice on changing "originating_server" on msdb..sysjobs to the new server name.
-- You can't be late until you show up.
April 25, 2008 at 5:36 pm
good advice above. Search sp_help_revlogin on this site and script out logins. They should come over with a master restore, but if they don't......
Script out everything. Every object, job, alert, operator, etc. Keep it handy just in case.
Backup and restore. I'd do master first. Be sure that msdb, tempdb, model, etc. are in the same locations (path-wise) before you do that. If that doesn't work, don't waste time on the user databases. Get those working first. Once they are, the user ones are a piece of cake.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply