How to update the device file locations in the SQL Server 6.5 master database (ISQL/w)

How to Install SQL Server 2000

How To

How to update the device file locations in the SQL Server 6.5 master database (ISQL/w)

To update the device file locations in the SQL Server 6.5 master database

  1. On the Start menu, point to Programs/Microsoft SQL Server 6.5, and then click ISQL/w.

  2. Enter the sa password, and then click Connect.

  3. Select from sysdevices in the master database to view the old device file locations.

  4. Execute sp_configure to allow updates to the system tables, and then reconfigure with override.

  5. Update the device file locations that have changed.

  6. Execute sp_configure to disallow updates to the system tables, and then reconfigure with override.
Examples
--View the old device file locations
SELECT phyname FROM sysdevices

--Allow updates to the system tables
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
--Update device file locations that have changed
UPDATE sysdevices
SET phyname = "E:\Data\HR\HR1.dat"
WHERE name = "HumanResources1"
GO
UPDATE sysdevices
SET phyname = "E:\Data\HR\HR1Log.dat"
WHERE name = "HumanResources1Log"
GO
--Disallow updates to the system tables
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO