As a SQL Server administrator, you may encounter situations where you need to move the physical database files from one drive to another on the same machine. This could be due to various reasons such as performance optimization, disk space management, or hardware upgrades. In this article, we will discuss a step-by-step approach to accomplish this task.

Step 1: Identify the Current File Locations

Before moving the database files, it is important to know their current locations. You can use the following query to retrieve this information:

SELECT name, physical_name,
    CASE
        WHEN type = 0 THEN 'DATA'
        WHEN type = 1 THEN 'LOG'
    END AS FileType
FROM sys.master_files;

This query will provide you with a list of all database files along with their current physical paths.

Step 2: Generate ALTER DATABASE Statements

Next, we need to create ALTER DATABASE statements to modify the file locations. The following query will generate the necessary statements for all databases except the master database:

SELECT 'ALTER DATABASE ' + d.name + ' MODIFY FILE (NAME = ' + f.name + ', FILENAME = ''' +
    'c:\NewFolder\' +
    RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name))-1) + ''')'
FROM sys.databases d
INNER JOIN sys.master_files f ON d.database_id = f.database_id
WHERE type = 0 AND d.database_id <> 1;

Make sure to replace ‘c:\NewFolder\’ with the desired destination path for the files.

Step 3: Execute the ALTER DATABASE Statements

After generating the ALTER DATABASE statements, execute them to change the file locations. This can be done by running the generated statements in SQL Server Management Studio or using a script.

Step 4: Shutdown and Move the Physical Files

Once the ALTER DATABASE statements have been executed, you can shut down the SQL Server services and move the physical files to their new location. Ensure that all files, including the master database, are moved to the appropriate destination.

Step 5: Update Startup Parameters

After moving the physical files, you need to update the SQL Server startup parameters to reflect the new file locations. Follow these steps:

1. Open the SQL Server Configuration Manager.
2. Right-click on your SQL Server instance and select “Properties”.
3. Click on the “Advanced” tab and navigate to the “Startup Parameters” section.
4. Change the location of the master database (.mdf) file by modifying the “-d” parameter.
5. Save the changes.

It is important to note that you should ensure the service account has proper access to the new file location; otherwise, SQL Server may fail to start.

Once you have updated the startup parameters, you can start the SQL Server services, and the databases should be accessible with the new file locations.

It is recommended to test this process in a non-production environment before performing it on a production server. Additionally, the time required for the file move will depend on factors such as disk speed and database size.

By following these steps, you can successfully move SQL Server database files to a different drive, ensuring optimal performance and efficient disk space management.