SQL SERVER – Move Database Files MDF and LDF to Another Location

When a novice DBA or Developer create a database they use SQL Server Management Studio to create new database. Additionally, the T-SQL script to create a database is very easy as well. You can just write CREATE DATABASE DatabaseName and it will create new database for you. The point to remember here is that it will create the database at the default location specified for SQL Server Instance (this default instance can be changed and we will see that in future blog posts). Now, once the database goes in production it will start to grow.

It is not common to keep the Database on the same location where OS is installed. Usually Database files are on SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.

Let us assume we have two folders loc1 and loc2. We want to move database files from loc1 to loc2.

Well, we are done. There is little warning here for you: If you do ROLLBACK IMMEDIATE you may terminate your active transactions so do not use it randomly. Do it if you are confident that they are not needed or due to any reason there is a connection to the database which you are not able to kill manually after review.

Moving a standard datafile is as easy as it is written here but a better article would have included how to move the master database to a new location. Although it isn’t done often it is well worth knowing simply because there are occasions where this might save you a lot of time, effort and downtime.

This is my situation. I have 68 servers, all local. Now i want these servers to be hosted a single or 2 servers. how do I go about the procedure of transferring the data without making disruptions on the operation on the server.

Hie Dave,
Requesting your help..thanx in advance. One of our pc was crashed and windows was not booting up. I have connected the hdd to another pc and copied the database (.mdf & .ldf). Now how will make the .bak files from the downloaded files?

In SQL server, Detach/attach is on database level, can we do it on filegroup or file level? is there anyway that you can detach a filegroup or a physical file, copy it to different database server and install it to different db server?

i am getting below error
File activation failure. The physical file name “I:\MSSQL11.MSSQLSERVER\MSSQL\DATA\learner_log.ldf” may be incorrect.
Msg 5170, Level 16, State 1, Line 1
Cannot create file ‘I:\MSSQL11.MSSQLSERVER\MSSQL\DATA\Learner_log.ldf’ because it already exists. Change the file path or the file name, and retry the operation.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Learner’. CREATE DATABASE is aborted.

Hi, how to attach the ldf file file from some other location, on thw same time the ldf will on other location. Ex:- mdf will on C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA and ldf will on d-drive .(folder with any name)

Hi there,
I have tried this script in SQL 2012. The database was set to Read-only after query was run successfully. And, I have encountered an error when trying to set the DB to Read-Write. Any idea how to bring the DB online?
Thank you for help in advance!

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.