Sql-server – how to change the file directory of your log and data files and what are the effects

datafilemigrationsql serversql-server-2012

I have inherited a SQL server 2012 instance where the file directories of the log and data files are on the same folder on the same drive(all are in the C:\ drive). My task that I will be doing is separating the data files from the OS disk. The instance already has few databases on it running.Can I just change the settings of the instance to point it to the new directory, detach the existing databases move the data files and attach it back? Please share what would be the best approach in doing this.

Best Answer

how to change the file directory of your log and data files and what are the effects

Can I just change the settings of the instance to point it to the new directory, detach the existing databases move the data files and attach it back? Please share what would be the best approach in doing this.

Below are two scripts I've used for this purpose quite a few times in the past; just adjust accordingly for your environment.

Basically just plug in the new path locations, the data and log file names, and the logical SQL data and log file names, and then run the first script (see comment above second script below).

First Script

Plug in your variable accordingly for your environment. 

ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE <DBName> SET OFFLINE

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalDataFileName1>
    ,Filename = 'T:\MSSQL\Data\<DBName>.mdf'
    );

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalDataFileName2>
    ,Filename = 'T:\MSSQL\Data\<DBName1>.ndf'
    );

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalLogFileName2>
    ,Filename = 'Y:\MSSQL\Logs\<DBName>.ldf'
    );

Second Script

Before you run the below TSQL, you will need to move the applicable physical data and log files to the new locations as you plugged into the first script.

--Now move the files to the new location where you pointed them to before running the below.

ALTER DATABASE <DBName> SET ONLINE
ALTER DATABASE <DBName> SET MULTI_USER
Related Question