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
- On the Start menu, point to Programs/Microsoft SQL Server 6.5, and then click ISQL/w.
- Enter the sa password, and then click Connect.
- Select from sysdevices in the master database to view the old device file locations.
- Execute sp_configure to allow updates to the system tables, and then reconfigure with override.
- Update the device file locations that have changed.
- 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