File type still says Primary Data file in the drive

Question

Post reply

File type still says Primary Data file in the drive

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
June 25, 2019 at 4:11 pm

#3657437

Hello,
Not sure why this happens but was trying to alter some of the .mdf files to .ndf which are supposed to be created with .ndf. Someone accidentally created all sec files with the name as .mdf. But after running the below alter command -
alter database dbname modify file (name = 'log_name', filename = 'XXX..............\.ndf') and when I look at the drive, it still says 'SQL Server Database Primary Data file'. Any advice on how to fix the issue.
SQL Edition: SQL Server 2016 Sp1
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Did you also rename the physical file on disk? Alter database...Modify file won't rename the file on disk.

Sue

Steve Jones - SSC Editor SSC Guru Points: 736418 More actions · Answer 2

SQL Server has the file(s) locked, so it can't rename anything. Neither can you. You need to stop the database engine for that datbaase to rename the files. You can stop SQL Server or detach that database.

You should have gotten something like this:

The file "mydatabase" has been modified in the system catalog. The new path will be used the next time the database is started.

If you now restart SQL Server without doing anything, you'll get the database put into recovery pending mode, with a message in the error log like:

Error: 5120, Severity: 16, State: 5.
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\xxx.mdf". Operating system error 2: "2(The system cannot find the file specified.)".

You need to either a) change the ALTER statement to reset the filenames if you can't take an outage, or b) take an outage and rename the files for that database.

Steve Jones - SSC Editor SSC Guru Points: 736418 More actions · Answer 3

This can help: https://www.sqlservercentral.com/articles/how-to-change-the-file-extension-for-a-data-file

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Yeah, tried with all the online help sites (including the one stated by Steve above). The issue is while changing the extension to ,ndf, the type still remains as primary data file.

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736418 More actions · Answer 5

So the extension is changed in Windows? You didn't state that. If you run "dir" on the folder, you see .ndf?

That is a file type extension that Windows Explorer renders. Perhaps this was changed somehow in the registry. AFAIK, that's where it is.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Hi Steve

After a hard checking, I explored and enabled the "file name extensions" feature from windows and that allowed me to change the extension from Mdf->Ndf. Earlier I never had to turn that on. Not sure if this is a new practice for SQL 2016.

But, it fixed.

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736418 More actions · Answer 7

I've never seen that. I wonder if someone changed yours from AD Group Policy? It's certainly strange.