INF: How to Move a Device to Another Location

• Microsoft SQL Server versions 6.0, 6.5, 7.0

SUMMARY

This article describes how to move a device to another location in SQL Server versions 6.0 and 6.5. To do this in SQL Server version 7.0, search for "Attaching and Detaching Databases" in Books Online.

MORE INFORMATION

To move a device to another location, perform the steps in either one of the following options. CAUTION: Before attempting this operation, ensure that you have a current backup of the user database and master database.

Option 1

1. Use SQL Enterprise Manager to mirror the source database device onto the target directory.



2. Use SQL Enterprise Manager's unmirroring to Switch to Mirror Device - Replace Original Device.



3. Use Explorer to delete (if desired) the source device, after verifying the mirror and unmirror succeeded.

Option 2

1. Install the sp_movedevice stored procedure from the SQL Server Books Online (for more information, see the "Moving a Device" topic in the SQL Server Books Online).



2. Run sp_helpdevice to determine the device name and current path.



3. Run the following command:

   sp_movedevice <device_name>, '<new_path>'

   NOTE: If you are moving the master database, proceed to Step 4. Otherwise, skip to Step 8.



4. In SQL Enterprise Manager, click the Server menu. Click SQL Server, then click Configure, followed by Parameters.



5. Change the Master Database File option to reflect the new location.



6. In SQL Enterprise Manager, click the Server menu. Click SQL Server, then Distributed Transaction Coordinator, followed by Configure.



7. Change the log drive designation and then click Reset Log.



8. Stop SQL Server and physically move the device to the new location.



9. Start SQL Server and verify that the database is working properly (for example, that the database is not marked Suspect).

Additional query words: SEM db dev DTC

Keywords : SSrvAdmin
Version : winnt:6.0,6.5,7.0
Platform : winnt
Issue type : kbinfo