Export DataBases to a new server cluster

[ejanakieff]

We have a Windows 2000 two nodes cluster whit SQL Server 2000 SP4.

Now we want to move the SQL Server 2000 SP4 to a new Windows 2003 two nodes cluster.

We have several databases, jobs, databases maintenance plans, packages...

My question is:

How is better: detach the databases from old nodes, and after attach them in the new nodes or do an backup and restore?

When I have installed the SQL server 2005 SP4, I have to restore system databases (master, msdn and model), and after the user database?

Thanks,
Eva Janakieff

 

[ptheriault]

You don't restore the system databases. (master, model, msdb)
If you have to have access to your user databases during the migration then you will have to use the backup and restore method. If you don't then just detach them and attach them on the new server. Neither way is better, you just might find the detach/attach method easier. Make sure you place you database files on the shared drive. To move the jobs you will have to script them out.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

My preference when moving from one machine to another is to use the detach and attach method.

What I actaully usually do in this kind of upgrade, is make sure that the paths to all the database files are the same on both clusters, then stop the SQL server, and copy all the database files (including the system database files) to the new cluster and bring the system online. Then simply tell the database server that the virtual name has changed via the sp_dropserver and sp_addserver commands and you should be all done.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ejanakieff]

Thanks.

In my case, I'll replace the old two nodes cluster by one new, with the same IPs address and the same virtual name for the SQL Server.

So, I have understood that I have do:

1.Detach the databases, only the user databases or the system databases too? If I have to detach the system databases, how I have to deattach the master database?

2. Stop the old SQL Server

3. The database files are in the same location for old and new cluster. So I haven't to move them.

4. Start the new SQL Server
I understand, that if the Startup Parameters are OK, the SQL Server give the database files of the master database, and will create the databases that exists in the old cluster, because in the master there is the database definitions. It's correct?

5. Finally I have to attach the model, msdb, tempdb and the user databases?

It's correct?

Thanks,
Eva Janakieff

 

[mrdenny]

It's much simpler than that.

You don't need to worry about detaching them. Simply stop the SQL Server. This will do a clean close of the database files.

1. Stop the SQL Server.
2. Power down the machines
3. Bring up the new machines
4. Cluster them.
5. Attach the LUNs with the database files on them.
6. Move the files for the system databases to a backup folder.
7. Install SQL Server with the data files in the same folder that the system database files were in.
8. Patch SQL to the same build level as the old machine.
9. Stop the SQL Services.
10. Backup the new system files and remove them from the folder.
11. Restore the old system files from the backup folder.
12. Start SQL Server.

Done. As far as the databases and users nothing will have changed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ejanakieff]

Thanks for your aid.

Finally, we have made an old machine's image, stop the old machines, start the news machines, applied the image and all work fine.

I will keep your suggestions for another time.

Thank's,
Eva Janakieff