2

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.

  • For grins do a backup first. – paparazzo Jul 14 '16 at 1:10
  • Did you find any usefulness out of my answer or was it missing something more you needed? There should be no effects other than the files residing on the disk partitions where you placed them rather than the old location if that folder was on another disk partition—it'll just work as expected. – Pimp Juice IT Jul 3 '17 at 11:19
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.