July 18, 2005 at 1:36 pm
Hi
We plan to put in a shiny brand new updated prod server to replace our old one. The new one is being built from scratch with a fresh installation of SQL Server 2000.
The servername and instance of SQL will be named the same as the old server. Obviously we will be making final backup copies of our user, master, model and msdb databases. The question is which of these will I need to restore once the new server with new SQL Server installs up and running. To my knowldge this should only be the
User databases (obviously),
Msdb (for all SQLagent jobs, DTS packages, backup history etc
Master (for logins).
My instinct says that I should be restoring Master first, then the user databases and then msdb. Is there anything that I may have left out or maybe a better way to accomplish this ?
July 18, 2005 at 2:00 pm
Simon,
If you are planning to implement the SAME file path on the new server including the drive letter you may be able to:
- Install the same service pack /patches on the new machine as on the old one
- Stop SQL Server on the new machine
- Rename Data / Transaction Log directory(ies) to Data_Old
- Copy Data / Transaction Logs directory (ies) to the new machine. You will have to stop old SQL Server for the copy process.
- Restart SQL Server
- Check if you have to copy / reimplement a backup folder and all locations and files that DTS packages refer.
- Do not forget data sources if needed
All pathes to all data directories should be identical on both machines for this plan (without restoring) to work. Othewise you have to use Restore. I would check @@Servername and read an article about renaming SQL Server machine. Some prefer to copy all data and log files except for Master and restore Master from the backup.
I did it on the same machine when SQL Server has to be re-installed.
Do not forget to copy any data that are stored in the file system where only links are stored in the database.
Review your databases if the machine name stored somewhere in the databases.
Yelena
Regards,Yelena Varsha
July 18, 2005 at 2:07 pm
Mmmm, interesting Idea Yelena, I hadn't thought about that
July 18, 2005 at 2:22 pm
Simon,
master will be able to start in the new location only if the startup parameters in the registry point to the correct location. For SQL Server 2000 it should be in:
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters will the new one have the same or different IP ?
About shares and machine name in the database: make absolutely sure machine names /share names are not in the databases as data. I have one app that stores attachments share path as a configuration item in one of the database tables. That means if I restore the database on the new machine and don't change this one record of data in the database than the application attachments will happily go to the old production share.
Yelena
Regards,Yelena Varsha
July 19, 2005 at 10:42 am
Thanks Yelena,
Incidentally am I correct in my assumption about the order with which the db's should be restored (going the restore route)
Master,
User Db's
and then Msdb
Or should user db's always be the last to be restored ?
July 21, 2005 at 7:46 am
Hi Yelena
The registry key is HKLM\Software\Microsoft\Microsoft SQL Server\<instance name>\MSSQLServer\Parameters on my installation. But the server names and instance names and drive mappings should remain the same.
One further question. Our Tempdb is on its own volume (H: drive), does it matter if we don't initially move this from its default SQL installation (data is on an e: drive), move alllof our other dbs Master, model, msdb, user dbs back and restart. Will it complain that it can't find the tempdb.
I guess what I am saying is that before we restart SQL server after copy the Master, model, msdb, user dbs back does there have to be a tempdb on the h: drive where it was originally located prior ro rebuild.
As a matter of interest where are the paths for the various physical files held in SQL server?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply