|
How can I move a SQL Server
device from one disk to another, or rename it?
There are two ways with SQL 6.5 :-
- 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.
- Use the sp_movedevice sp that is documented in the Books-Online - just cut and paste
this into an ISQLW session and run it to create the sp (it doesn't exist by default).
After making the change stop SQL Server, physically move the device, then restart it.
Therefore 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_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters\SQLArg0
So it is the value in this key that needs amending.
For SQL 7.0 you can use :-
sp_detach_db ...
<move devices physically>
sp_attach_db ...
For details on the stored-proc parameters see the SQL Books online. |
