How can I move a SQL Server device from one disk to another, or rename it?

A. There are two ways with SQL 6.5 and below :-

1. Use device mirroring. Mirror the device to the "new" location and then break the mirror and delete the old device. This method does not need SQL downtime and can be done via the gui or via TSQL.

2. In the Books Online for SQL 6.5 there is an sp documented called sp_movedevice. Cut and paste this SP into a query analyser/ISQLW window and run it to create it. (It doesn't get installed automatically). Then run sp_movedevice with the required parameters to make the system table changes. Next stop SQL Server. Then move the device using the MOVE command, Explorer, File Manager etc. Finally, restart SQL Server. This method requires downtime, but it is faster as using OS level commands is faster than SQL mirroring.

All that sp_movedevice does is update the phyname in the sysdevices table.

Note that the above method works for all USER databases. If you want to move master this way, then note that the phyname parameter in sysdevices is only for documentation - you might as well change it anyway to keep things in line. The actual method SQL uses for locating the master device is by looking in the registry :-

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServerParametersSQLArg0

So it is the value in this key that needs amending.

For SQL 7.0 user databases you can use :-

sp_detach_db ...

sp_attach_db ...

For details on the stored-proc parameters see the SQL Books online.

For SQL 7.0 tempdb use :-

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, FILENAME='')

ALTER DATABASE tempdb
MODIFY FILE (NAME=templog, FILENAME='')

Re-starting SQL Server makes this take effect

===

v1.03 2000.02.04
Applies to SQL Server versions : All
Related FAQ articles : n/a
Related Microsoft Kb articles : Q187824, Q181602
Other related information : n/a