How to move Data files from drive to E drive in MS SQL Server 2008 R2

Category: sql server tools


madymsdn on Sat, 20 Jul 2013 04:10:00

Hi Experts,,

We have MS SQL Server 2008 R2 on the Dev and Prod Servers(not yet went to live, testing is going on). We have installed SQL Server in the C Drive along with Data files also. Now issue is, C drive got fulled and dont have space to create the New datbase. By mistake, we have installed in the default location in C drive.

But Client need Data files in the E drive not in the Default location in C drive.

So, Could you please suggest the best ways to move the Data files from C drive to E drive with out lost any data.

(we have configured Hyperion with SQL Server Databases and also we have lot of data regarding applications.)

Quick response would be appreciated.




Olaf Helper on Sat, 20 Jul 2013 04:20:19

Hello mady,

If you can have a small downtime of your database, then you can use the Database Detach and Attach (SQL Server) method to move your database files to a different location.

Shanky_621 on Sat, 20 Jul 2013 06:46:38

Using attach dettach method sometimes changes owner of database while reattaching so I prefer doing it through sql command

sp_helpdb db_name

--this will give u logical mdf.ndf,ldf names use that in query in place of logicalfile name

--ndf is only applicable if u have secondary data file

GO USE master; GO ALTER DATABASE DB_name MODIFY FILE ( NAME = logicaldatafilename, FILENAME = N'D:\newlocation\TestMove.mdf' ); GO USE master; GO ALTER DATABASE db_name MODIFY FILE ( NAME = logicalndfname, FILENAME = N'D:\Newlocation\TestMove2.ndf' ); GO USE master; GO ALTER DATABASE db_name MODIFY FILE ( NAME = logicallogfilename, FILENAME = N'D:\Newlocation\TestMove3.ndf' ); GO

After running this query stop sql server services and move data and log files to respective location 

Restart the sql server service .

run SP_helpdb db_name to look for new file location

madymsdn on Sat, 20 Jul 2013 16:39:01

I will try and let you know what happened.

Thanks for the Quick reply.



Sofiya Li on Mon, 22 Jul 2013 09:28:14

Hi madymsdn,

In addition, we can also move your database files from one location to another physical location by using SSMS.

There is one article about moving data files .mdf and .ldf, you can review the following link.

Sofiya Li