INF: How to Move a Device to Another Location

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

You can use the following steps to move a user device. To move the master device (master.dat), see option 2.

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).