SQL Server 6.5 Move Master/​SystemDB Location

Microsoft has an extra sp_​movedevice stored pro­ced­ure to update sys­devices table and then you need use Enter­prise man­ager to update registry info.

Relo­cate the SQL 6.5 Master.dat file to a dir­ect­ory path that does not have spaces. To do this, per­form the fol­low­ing steps:

  1. If SQL Serv­er 7.0 is on the same com­puter as SQL 6.5, ver­sion switch to 6.5.
  2. Match the SQL Serv­er 6.5 device names with their paths using the sp_​helpdevice stored procedure.
  3. Install the stored pro­ced­ure sp_​movedevice found in SQL Serv­er 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
  4. Run sp_​movedevice mas­ter, ‘new path’ as fol­lows:
    exec sp_movedevice MASTER, 'D:\MSSQL\DATA\MASTER.DAT'
    reconfigure
  5. To ensure that the changes made were the ones inten­ded, run the sp_​helpdevice stored pro­ced­ure to con­firm that the new loc­a­tion is correct.
  6. Open the SQL Serv­er 6.5 Enter­prise Man­ager, high­light the serv­er, click Serv­er, click SQL Serv­er, and then clickCon­fig­ure. Modi­fy the “Mas­ter Data­base Path” option on the Serv­er Options tab of this dia­log box to look like this:
    D:\MSSQL\DATA\MASTER.DAT
    WARNING: After com­plet­ing this step, you must make sure your para­met­ers are in the cor­rect order or you may run into anoth­er pos­sible hindrance when using the Upgrade Wiz­ard. To check para­met­er order, click the PARAMETERSbut­ton. In the win­dow titled Exist­ing Para­met­ers make sure the sequence of ver­tic­al order for your serv­er para­met­ers matches the following:
    ‑dD:\MSSQL\DATA\MASTER.DAT
    ‑eD:\MSSQL\LOG\ERRORLOG
    For addi­tion­al inform­a­tion con­cern­ing this prob­lem, see the fol­low­ing art­icle in the Microsoft Know­ledge Base:
    231988 BUG: 7.0 Upgrade Loops When 6.5 Para­met­ers Are Reversed
  7. Stop the SQL Serv­er 6.5 services.
  8. Move the Master.dat file to the new loc­a­tion (for example D:\Mssql\Data).
  9. Start the SQL Serv­er 6.5 services.
  10. Check the SQL Serv­er 6.5 error log to see if all the data­bases have recovered or if there are any marked as sus­pect. Cor­rect any errors. If there are no errors, move to the next step.

More details: http://​sup​port​.microsoft​.com/​K​B​/​2​4​6​181

No Comments

Post a Comment

Your email is never shared. Required fields are marked *

*