Moving SQL 2000 and all databases to a new server

Question

Moving SQL 2000 and all databases to a new server

Tyson-633393

Old Hand

Points: 333
More actions
February 6, 2008 at 8:35 pm

#186419

Hello,
I have a SQL 2000 server with 55 databases in it that I need to move to a new server.
I have built the new server (W2K SP 4) and have the new server configed with the same
partitions C, and E. My questions is what is the best way to move the databases to the new server?
I have backed up all 55 databases. Should I perform an export of each database to the new server
or is it better to do a restore from my backups? The new server is named AAA. The old server is named BBB. Is it ok to rename the new server after completetion that all the databases are attached to the new server? I know I must place the IP address and name from the old to the new server. I suppose there is another option of detaching the databases off the old and re-attaching to the new server as well?
Thanks,
J~~

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

You can backup all the databases (55 database) then restore in the new server just be reminded of the user log-ins when transferring databases from 1 server to another there are script in the sqlservercentral script section that can help you restore backups to another server within serveral clicks 😉

"-=Still Learning=-"

Lester Policarpio

Tyson-633393 Old Hand Points: 333 More actions · Answer 2

Thanks but I do not want to use scripts. I just want some direction on the best procedure to do this.

I feel more comfortable using the gui to do this.

Tyson-633393 Old Hand Points: 333 More actions · Answer 3

I detached one database and attached it to the new server. . I suppose once I do each detach on the 55 databses and then attach each to the new server, I can name the new server to the old server name and move the ip address over to the new box as well. I believe this is how the original DBA did this before on another sql move. I would still like a comment though on my decision if possible from someone who has done this before.

Thank you

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 4

In my situation when we are migrating production databases to another server what we do is..

1. Change the IP address of the production server so that no transaction will enter the server.

2. Backup all the databases (largest server i migrated is compose of 30 databases)

3. Transfer the backup files to the new server then restore all the backup files (using a script)

4. After the migration to another server, change the comp name of the old production server example (comp name = ProductionServer it will be ProductionServerOLD)

5. Change the IP of the new server to the IP of the production server

6. Change the value of @@servername by using sp_dropserver and sp_addserver

This is our way of transferring databases to another server. Detaching database and attaching it to another server is i think ok to do if your doing a migration for few databases but for 55 databases it will take you time to detach and attach it 1 by 1 against restoring all of the databases to the new server. I would like to emphasize that this is our way of doing this, your "way" of doing things maybe different from ours. I think other comments and suggestions are also welcome here so that we can learn from the experts 🙂

"-=Still Learning=-"

Lester Policarpio

Tyson-633393 Old Hand Points: 333 More actions · Answer 5

Thank you for your reply, well appreciated!! I had one unexpected issue, however. After I attached the databases to the new server and then changed the IP address and the FQDN to reflect the original name and IP, I recieved an error after openeing Enterprise Manager. The local server name in EM still reflected the original name of the server. So what I had was newserver.xxx.net and the name of the database was still server.xxx.net. I think I had to register the new name in SQL. But I got it fixed. At any rate, now I have a deeper issue. I have all 55 databases attached w/t any errors.

But this Datbase is actually part of a billing system we use at work. I have the employees connect to the server through the ODBC connections I set up in XP and a small application is installed on each workstation as well. The can log in with no errors to the billing system, but as they go deeper into the billing system an ODBC error that pops up - ODBC- call failed.The actual error:

823: [ODBC SQL Server Driver]I/O error bad page ID detected during read at offset 0x0000008a648000 in file E:\SQLDATA\cored.mdf

3146: ODBC call failed (DAO.Database)

How do I fix this or track this error?

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 6

Please see this link

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67564

its almost the same as your problem i think there is a corrupt issue with it. Do you have a backup of the databases??

"-=Still Learning=-"

Lester Policarpio

Tyson-633393 Old Hand Points: 333 More actions · Answer 7

Yes, I have a backup, although when I tried to do a restore from the backup, it failed.

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 8

Is that a backup before you migrated the databases to the new server or backups after you successfully attached the databases to the new server?

"-=Still Learning=-"

Lester Policarpio

Tyson-633393 Old Hand Points: 333 More actions · Answer 9

The backup is before I migrated to the new server - yes. I have the .bak file on another

shared server. Do I need to move the .bak file to the new sql server?

Also, when I do th restore, do I choose to restore from database, filegroups/files, or from device?

Tyson-633393 Old Hand Points: 333 More actions · Answer 10

I got the restore going, I will test it when completed..ina few minutes.

Tyson-633393 Old Hand Points: 333 More actions · Answer 11

I got the restore going. i'lltest it when completed. In a few minutes.

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 12

jbarkan (2/7/2008)
But this Datbase is actually part of a billing system we use at work.

Be warned when restoring since after migration users accessed the database. Examine if they insert, update or delete data in the database because when you restore a backup you are overwritting the changes they made earlier......

"-=Still Learning=-"

Lester Policarpio

Tyson-633393 Old Hand Points: 333 More actions · Answer 13

OK! I performed a restore and that error is not appearing now! Just out of curiosity,why did the

error appear in the first place? Is is b/c something happened on the detach/attach procedure?

Tyson-633393 Old Hand Points: 333 More actions · Answer 14

One more Q: The database I had an issue with is about 2.4GB. What are my options to clean it up and make it smaller? I've been reading that shrinking the DB can casue more harm than good. Is this true?