KBA-01010: How do I move my SQL Server database files?

Question:

When I set up the SQL Server database for Spitfire, I had the opportunity to specify the location for the primary MDF, the secondary file group NDF, and the SQL transaction log LDF.  How do I change the location of any of these components at a later time?

Answer:

This procedure is best carried out using Enterprise Manager on the SQL Server itself.  All users must be off the system.

1. Open Enterprise Manager.
2. Expand the server.
3. Expand the database collection.
4. Optional — decrease the size of the files prior to the move.
   (To achieve maximum reduction in the LDF file, this step is best performed after a full backup.)
   1. Right-click on the database to be relocated.
   2. Choose All Tasks | Shrink Database.
   3. Click OK.
5. Right-click on the database to be relocated.
6. Choose Properties.
7. Move to the Data Files and Transaction Log tab pages.  Make note of the Location of each file.  Close the dialog.
8. Right-click on the database to be relocated and choose All Tasks | Detach.
9. Use Windows Explorer and move the files from the current locations noted in step 6 to the new locations.  Given that the MDF, NDF and LDF can be quite large, the move can take several minutes per file.
10. Back in Enterprise Manager, right-click on the Databases collection and choose All Tasks | Attach.
    1. Use the browse button to find the MDF and then click Verify.
    2. Update the paths to any additional files.
    3. Enter the database name (from step 5).
    4. Specify SA as the database owner.

Additional Comments:

For best performance and data protection, the LDF should be on a separate set of physical devices.  It should not share I/O heads with the MDF or NDF files.See KBA-01280 for information about relocating your Spitfire database on another SQL Server.

KBA-01010; Last updated: May 11, 2016 at 11:27 am;
Keywords:  move MDF NDF LDF SQL