Microsoft has an extra sp_movedevice stored procedure to update sysdevices table and then you need use Enterprise manager to update registry info.
Relocate the SQL 6.5 Master.dat file to a directory path that does not have spaces. To do this, perform the following steps:
- If SQL Server 7.0 is on the same computer as SQL 6.5, version switch to 6.5.
- Match the SQL Server 6.5 device names with their paths using the sp_helpdevice stored procedure.
- Install the stored procedure sp_movedevice found in SQL Server 6.5 Books Online.
Use master Go Exec sp_configure "allow",1 RECONFIGURE WITH OVERRIDE go CREATE PROCEDURE sp_movedevice @@devname varchar(30), @@newpath varchar(255) AS BEGIN EXEC sp_configure "allow",1 RECONFIGURE WITH OVERRIDE BEGIN TRAN UPDATE sysdevices set phyname = @@newpath WHERE name = @@devname IF @@rowcount <> 1 BEGIN PRINT "**********************ERROR**********************" PRINT "Moved failed - more or less than one row affected" PRINT "**********************ERROR**********************" ROLLBACK TRAN END ELSE BEGIN PRINT "Device moved successfully" PRINT "Change will take effect next time you start SQL Server" COMMIT TRAN END EXEC sp_configure "allow",0 RECONFIGURE WITH OVERRIDE END Go EXEC sp_configure "allow",0 RECONFIGURE WITH OVERRIDE
- Run sp_movedevice master, ‘new path’ as follows:
exec sp_movedevice MASTER, 'D:\MSSQL\DATA\MASTER.DAT' reconfigure
- To ensure that the changes made were the ones intended, run the sp_helpdevice stored procedure to confirm that the new location is correct.
- Open the SQL Server 6.5 Enterprise Manager, highlight the server, click Server, click SQL Server, and then clickConfigure. Modify the “Master Database Path” option on the Server Options tab of this dialog box to look like this:
D:\MSSQL\DATA\MASTER.DAT
WARNING: After completing this step, you must make sure your parameters are in the correct order or you may run into another possible hindrance when using the Upgrade Wizard. To check parameter order, click the PARAMETERSbutton. In the window titled Existing Parameters make sure the sequence of vertical order for your server parameters matches the following:
‑dD:\MSSQL\DATA\MASTER.DAT
‑eD:\MSSQL\LOG\ERRORLOG
For additional information concerning this problem, see the following article in the Microsoft Knowledge Base:
231988 BUG: 7.0 Upgrade Loops When 6.5 Parameters Are Reversed - Stop the SQL Server 6.5 services.
- Move the Master.dat file to the new location (for example D:\Mssql\Data).
- Start the SQL Server 6.5 services.
- Check the SQL Server 6.5 error log to see if all the databases have recovered or if there are any marked as suspect. Correct any errors. If there are no errors, move to the next step.
More details: http://support.microsoft.com/KB/246181