Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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 ?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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.
Avatar of marrowyung
marrowyung

ASKER

"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 ?
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.
"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.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?
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
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.
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.
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.
"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 ?
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
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
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?
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
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.
tks all