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:

ALTER DATABASE [NAVTest]
MODIFY FILE
(
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
ALTER DATABASE [NAVTest] set Online

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.

 

 

Advertisements

Author: Ibrahiem Rasoelbaks

I'm a Technical Consultant working at 4PS.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s