I re-built SQl server 2000 on the new box. Attached and detached all DBs (master, msdb, users DBs) and moved them to the new paths (E:\mssql). Everything is fine.
The questions are:
1. The 'filename' in the master.sysdatabases for 'master' DB is still pointed to the old location (F:\) even physicaly files are on E:\ drive;
2. The 'phyname' in the master.sysdevices for all devices (DBs), including master, msdb, model are still shows the old location (F:\);
At list I will get a wrong reports of the files location.
it could be other places in the system tables that keep old information.
How and where I can correct the information?
3. All jobs, bakups, etc. that creates output files still pointed to the old locations (let say to the F:\sql2000\). F:\ drive is not exists anymore.
I will recreate all jobs and main plans to specify correct path.
But how can I change the DEFAULT locations for all backups, logs, etc. ?
In the rebuild situation, I generally will not restore or reattach system databases, only the user databases. Because my past experience of having numerous troubles with the master after restoring to a new machine. Scheduled tasks and DTS can be saved and transfer to the new machine.
Originally posted by dbabren
One way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.
By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed
Hope this helps
All user DB's are fine.
The only incorrect information for master.sysdatabases.filename (for master database) and master.sysdevices (for all devices).