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.
2
-
For grins do a backup first.– paparazzoJul 14, 2016 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.– IT Thug NinjaJul 3, 2017 at 11:19
Add a comment
|
1 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
