Move LDF, MDF or NDF file in SQL Server 2016 using Alter Database

In my previous post I explained how to move a certain database file with ATTACH/DETACH. There is another better option which I would like to share with you.:

Let’s say you need to move the NDF file. The first thing to do is to alter the database. Specify the new path of the NDF file like this:

NAME = Data
FILENAME = 'C:\NewPath\Data.ndf'
In order to move the NDF file you first need to take the database Offline:

use master
ALTER DATABASE [NAVTest] set offline

Now move your file and set the database online again:

use master

So in summary we have the following high level steps:

– Alter database and specify the new path of one or multiple files
– Set database Offline
– Move the physical file
– Set database Online

Now what if the path to one or more files is incorrect? You will then get a warning and the database will be in Suspect Mode.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s