relocating SQL 2000

hi,

right now we have a task to relocate SQL 2000 (long time ago, I can't remember how to do it...) from a Windows 2000 server to Windows 2003 server so that we can apply security patch on Windows 2003 for any new virus attack.

anyone still remember how to move system DBs and user DBs from one SQL 2000 to another SQL 2000 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Why do you need to move the databases to apply a patch?
Only thing you need is BACKUPS!

Backup all server and the databases. If everything goes wrong, just restore from the backups you made.
marrowyungSenior Technical architecture (Data)Author Commented:
"Why do you need to move the databases to apply a patch?"

the windows needs to be patched, not the SQL server.

"Only thing you need is BACKUPS!"

master DB can't do by that, right? this is SQL 2000 product. I forget a lot of thing about this already.

how about security login ? not as simple as SQL 2005 and afterward ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
the windows needs to be patched, not the SQL server.
So? What do you think will be the impact in SQL Server?

master DB can't do by that, right?
Can't do what? Backup? Everything can be backed up.

how about security login ?
Backup, backup, backup. Everything can be backed up.
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

marrowyungSenior Technical architecture (Data)Author Commented:
"So? What do you think will be the impact in SQL Server?"

nono.. it is the support team's wish to move from Windows 2000 to Windows 2003. so that they can patch Windows 2003 and their job can be done.

"Can't do what? Backup? Everything can be backed up."
master can't be restore to diff machine. that's why we need to use the 2 x SP from MS to genreate the login and password to diff server, right?

let me  do it on thursday and update you.
Vitor MontalvãoMSSQL Senior EngineerCommented:
it is the support team's wish to move from Windows 2000 to Windows 2003
Can't they perform an in-place upgrade? So they will use the same machine and keep the same server name.

master can't be restore to diff machine
Yes it can but with some precautions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
not sure what you try to achieve -- win 2003 is out of main support... and it is in  the "security" high risk group ...

..answers:

backup -restore; detach-copy- attach, etc

depends what you have

I hope you do not plan to move "master"
 right?
How to move databases between computers that are running SQL Server
https://support.microsoft.com/en-us/help/314546/how-to-move-databases-between-computers-that-are-running-sql-server

more: just in case
marrowyungSenior Technical architecture (Data)Author Commented:
Vitor,

"Yes it can but with some precautions."

what is that ?

not that method by Microsoft anymore but sth else?

Eugene Z,

"backup -restore; detach-copy- attach, etc"

yes yes yes ! this is what we can do.  or try export/import, I don't prefer that.

"https://support.microsoft.com/en-us/help/314546/how-to-move-databases-between-computers-that-are-running-sql-server
"

I am more on worrying about the login and password stored in master on how to move them with the right password, I never try the 2 xSP for that from MS on SQL2000. did you ?
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
these 2 procs to copy logins are good
just follow steps from  https://support.microsoft.com/en-us/help/246133  
if you'd like to feel 100% ready
just create -or-use known sql login -pwd
and copy just this one and test

--BTW: after you copied sql logins and restore DBs - it can be a good idea to run "orphaned users" fix
see one of examples @
https://www.codeproject.com/Articles/594134/How-to-Fix-Orphaned-SQL-Users

and DB maintenances  later as well
Vitor MontalvãoMSSQL Senior EngineerCommented:
what is that ?

 not that method by Microsoft anymore but sth else?
If you're going for a restore of the master database into another server then you'll need to use the following steps to have the server renamed in the SQL Server instance:
sp_dropserver 'oldservername'
sp_addserver 'newservername' , local

Open in new window

Logins will be transferred immediately when restoring the master database since it has all the necessary information about logins.
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

"If you're going for a restore of the master database into another server then you'll need to use the following steps to have the server renamed in the SQL Server instance:"

in case of parallel run, both server name can't be the same, right?

Eugene Z,

"https://support.microsoft.com/en-us/help/246133  "

that one say from SQL 2000 to SQL 2000, need to use DTS, the only method between SQL 2000?

but I think I will do method 2 either.

"Method 2

This method applies to the following scenarios:
You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
You assign logins to roles.
To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:
Run the following script on the source SQL Server."

but still diff than the 2  xSP from MS.
Vitor MontalvãoMSSQL Senior EngineerCommented:
in case of parallel run, both server name can't be the same, right?
Depends. If they are in the same network zone, then they can't.
I remember in those old days that we're building replacement servers with the same name and IP of the old ones but we never connected them to the network until the day we really need to have the new servers up and running. At that moment all that we needed to do was to shutdown old server and remove it from the network and connect the replacement server in the network.
marrowyungSenior Technical architecture (Data)Author Commented:
"Depends. If they are in the same network zone, then they can't."

yes, they can talk to each other.

"At that moment all that we needed to do was to shutdown old server and remove it from the network and connect the replacement server in the network."

yeah this is the only thing we should do.

Eugene Z,

we do plan to move master DB, any more suggestion ?
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
I'd do not move master DB
just copied logins; linked servers to the new server ( basically it is what you'll do when time to upgrade to sql 2005 + will come ), any user objects from master DB if you have-use
marrowyungSenior Technical architecture (Data)Author Commented:
Eugene Z,

"I'd do not move master DB"

then how can the existing login to that upgrade SQL box ?

"just copied logins"

but password can't copy
marrowyungSenior Technical architecture (Data)Author Commented:
hi all,

we see a surprise thing, the target SQL server is running SQL 2000 express edition, can SQL 2000 express upgradable to SQL 2000 standard?
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
should be no issue ( just make sure your ExpEd has latest sql sp4 installed)

still,  see if you can get MSFT supported sql server version instead of sql 2000
for example sql2008R2 server still support sql 2000 databases compatibility
marrowyungSenior Technical architecture (Data)Author Commented:
what we found out is :

for SQL 2000 MSDE to SQL 2000 standard, it is:

1) backup all database,
2) uninstall the MSDE.
3) install with SQL serve 2000 standard.
4) restore all DB.

for SQL 2000 MSDE to SQK 2005 standard.
1) download SQL 2005 express
2) inplace upgrade the SQL2000 MSDE to SQL 2005 express edition.
3) upgrade SQL 2005 express edition to SQL 2005 standard.'

tks.
marrowyungSenior Technical architecture (Data)Author Commented:
tks all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows OS

From novice to tech pro — start learning today.